Tricky Query - need help

[snufse]

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.

[skhanal]

From what you have posted, it does not look complex. You can join VGIPRDDTA.F0911 with another table on glmcu column.

[snufse]

You are right, it was not that difficult as I thought. Here is the code:

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'')'

Thank you.