Select field based on variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
"Christopher King" wrote
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.
Click to expand...
Hi Christopher,

Several ugly ideas for an ugly data structure:

1) UNION ALL

Select Item_Code, Hist_Month, Demand@9 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 1
UNION ALL
Select Item_Code, Hist_Month, Demand@10 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 2
UNION ALL
Select Item_Code, Hist_Month, Demand@11 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 3
UNION ALL
Select Item_Code, Hist_Month, Demand@12 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 4
..
<snip>
..
UNION ALL
Select Item_Code, Hist_Month, Demand@1 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 17
UNION ALL
Select Item_Code, Hist_Month, Demand@2 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 18
UNION ALL
Select Item_Code, Hist_Month, Demand@3 As Qty
FROM ItemHistoryRev
WHERE Hist_Month = 19
..
<snip>
..

I think you see the pattern (of course, maybe I don't understand).

2) use Choose(index, choice-1[, choice-2, ... [, choice-n]])
{from Access Help}
Choose returns a value from the list of choices based on the value of index.
If index is 1, Choose returns the first choice in the list;
if index is 2, it returns the second choice, and so on.

Select Item_Code, Hist_Month,
Choose(Hist_Month,
Demand@9,
Demand@10,
..
<snip>
..
Demand@1,
Demand@2,
Demand@3,
..
<snip>
..
) As Qty
FROM ItemHistoryRev;

3) or use UNION ALL to create a thin table

Select Item_Code, Hist_Month, 1 As Demand, Demand@1 As Qty
FROM ItemHistoryRev
UNION ALL
Select Item_Code, Hist_Month, 2 , Demand@2
FROM ItemHistoryRev
UNION ALL
Select Item_Code, Hist_Month, 3, Demand@3
FROM ItemHistoryRev
UNION ALL
Select Item_Code, Hist_Month, 4, Demand@4
FROM ItemHistoryRev
..
<snip>
..
INTO ThinTable;

so...(untested):

Select
Item_Code,
Hist_Month,
Qty
FROM ThinTable
WHERE
IIF(Hist_Month >16, Demand = Hist_Month - 16,
Demand = Hist_Month +8);

Again, I may have completely missed the point though.

Gary Walter
 
Back
Top