Tricky Query - Need help RSS

• • Reply
  

  snufse

  Member

  38 Points

  143 Posts

  Tricky Query - Need help

  Aug 04, 2010 12:37 PM|LINK

  I have following query (against db2/400 data base using linked server).
  
  

  SET @SQLSTRING = 
  'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
  ''SELECT 
          glmcu as business_unit,
          SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom1,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo1,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity1 
          from    VGIPRDDTA.F0911
  where   glmcu = ''''     1000131'''' and
          (glsub = ''''00200   '''' or glsub = ''''00315   '''') and 
          gllt = ''''AA'''' and
          gldct = ''''JE'''' and 
          globj = '''' '''' and
          glum = ''''CY''''
  group by glmcu
  order by glmcu'')'

  
  
  The query works fine but I need to modify my selection for fields 'glmcu' (BusinessUnit) and 'glsub' (CostCenter). Instead of hard coding the selection I will need to retrieve the selection (and grouping) from another table.
  
  Table layout:
  
  

  
  BusinessUnit  Group  CostCenter
  1000131          1     00200
  1000131          1     00315
  1000131          1     00320
  
  1000164          1     00200
  1000164          1     00400
  
  1000131          2     04000
  1000131          2     04400

  
  
  So with the above table example the query should render 3 records.
  
  Anyone that may help??? Thank you.
  

• • Reply
  

  snufse

  Member

  38 Points

  143 Posts

  Re: Tricky Query - Need help

  Aug 04, 2010 07:37 PM|LINK

  SET @SQLSTRING =
  'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL,
  ''select   @@mcu as job_number,
             @@gid as group_id,
             @@dl01 as job_name,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom1,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo1,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_1,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom2,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo2,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_2,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom3,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo3,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_3,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom4,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo4,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_4,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom5,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo5,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_5,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom6,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo6,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_6,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom7,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo7,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_7,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom8,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo8,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_8,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom9,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo9,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_9,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom10,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo10,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_10,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom11,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo11,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_11,
             SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom12,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo12,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_12
  from       VGIPRDDTA.F555101 as f1
  inner join VGIPRDDTA.F0911 as f2 on f2.glmcu = f1.@@mcu and f2.glsub = f1.@@sub
  where   gllt  = ''''AA'''' and
          gldct = ''''JE'''' and
          globj = '''' ''''
  group by @@mcu, @@gid, @@dl01
  order by @@mcu, @@gid'')'

  
   

  • Marked as answer by Hong-Gang Chen - MSFT on Aug 10, 2010 12:32 PM