Select field based on text/variable

[ChristopherBKing]

I have a database containing qty sold for items. The fields are defined as Demand@1, Demand@2, Demand@3 ect.. To determine which field holds the correct data you must first look at the Hist_Month field. I know this is a strange setup but I am pulling from an old unix based system. Basically I need to take the value from the Hist_Month field and append Demand@ in front of it to select the appropiate field. I can get it to pull correctly using a passthrough query aginst the progress database using the following.

SELECT
item_0.ITEM_CODE,
if(item_0.HIST_MONTH > 16)
then item_history_0.DEMAND[integer(item_0.HIST_MONTH - 16)]
else item_history_0.DEMAND[integer(item_0.HIST_MONTH + 8)]
FROM P21.item item_0, P21.item_history item_history_0
WHERE item_history_0.ITEM_REC = item_0.FRECNO AND item_history_0.LOC_ID = 1

This works but if I try to pull more than one field it will time out. So I just did a select.* and put all the data into an access table. I figured something like the following would work but just gives me demand@8 instead of giving me the value from the field demand@8.

SELECT ItemHistoryRev.ITEM_CODE, ItemHistoryRev.HIST_MONTH, IIf([hist_month]>16,("demand@"&[hist_month]-16),(demand@"&[hist_month]+8)) AS Expr1
FROM ItemHistoryRev;

the iif portion is working correctly it just is not combining the to determine the field name.

 

[PHV]

Do a search in this forum for normalization union query

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244