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.
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:
So with the above table example the query should render 3 records.
Anyone that may help??? Thank you.
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'')'