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.