-
Tricky Query - need help
I have following query (against db2/400 data base using linked server).
Code:
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:
Code:
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.
-
From what you have posted, it does not look complex. You can join VGIPRDDTA.F0911 with another table on glmcu column.
-
You are right, it was not that difficult as I thought. Here is the code:
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.
