Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select field based on text/variable

Status
Not open for further replies.

ChristopherBKing

IS-IT--Management
Feb 22, 2005
1
US
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.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top