Albert: Thanks for your reply. I'm probably going to end up using something similar to the technique you suggested. I was trying to approach this by doing something along the lines of an Excel lookup-type function. Doing it your way will be more straightforward and efficient from a database standpoint.
Thanks -- Mike -----Original Message----- From: Albert Berry [mailto:[EMAIL PROTECTED] Sent: Monday, June 27, 2005 12:27 PM To: [email protected] Subject: [RBG7-L] - Re: Dynamic column selection Mike, to continue with my thoughts, after the spurious send, at the start of the routine could you create a temp table and load it? Then the pricing data is normalized, and can be easily picked up. CREATE TEMP TABLE `TmpMatrix` ( + UNIT_NO INTEGER, + GRADE_TYPE TEXT (3), + COST CURRENCY) LOAD TmpMatrix 1,"Chro",5000 2,"Chro",5000 END --- Ramsour Mike <[EMAIL PROTECTED]> wrote: > Good morning: > > Is there a way to have a SELECT statement dynamically determine which column > to use in one table based on a value in another table without using > something like IFEQ? > > I have a matrix with the rows identified as GRADE_TYPE and 3 other columns > that represent the point in a process where material is rejected at. The > intersection of GRADE_TYPE and process provides a costing rate to apply to a > rejection amount. The columns are named GRADE_TYPE, HOT_AREA, BRT_ANNL and > TEMPER. The HOT_AREA, BRT_ANNL and TEMPER columns are broad categories that > specific operating units are associated with. So units 1, 2 and 3 are > HOT_AREA units, 4, 5 and 6 are BRT_ANNL units and 7, 8 and 9 are TEMPER > units. > > The table looks something like this: > > Table: COST_MATRIX > -- > GRADE_TYPE HOT_AREA BRT_ANNL TEMPER > Chro $5000 $7500 $8000 > Mart $4000 $6000 $7500 > > I have a separate detail table with unit numbers and grade types. > > The data looks something like this: > > Table: REJECT_DETAIL > -- > GRADE_TYPE UNIT_NO WEIGHT > Chro 1 5,000# > Chro 5 4,750# > Mart 3 7,500# > Mart 7 8,000# > Crni 4 8,250# > Crni 9 2,500# > > I would like the SELECT statement to be able to dynamically figure out which > column to return from the COST_MATRIX table based on the UNIT_NO and a given > GRADE_TYPE from the REJECT_DETAIL table without doing a bunch of IFEQs. So > if the GRADE_TYPE is Chro and the UNIT_NO is 1 the select statement would > pull in the costing rate of $5000 but if the GRADE_TYPE is Chro and the > UNIT_NO is 5 the costing rate would be $7500. I realize I could do this > with a set of UNION statements but I was looking for something more dynamic. > > Any ideas? > > Thanks > > Mike Ramsour > c/o AK Steel > Phone: 740-829-4340 > > Albert Berry Management Consultant RR2 - 1252 Ponderosa Drive Sparwood BC, V0B 2G2 Canada (250) 425-5806 (250) 425-7259 (708) 575-3952 (fax) [EMAIL PROTECTED]
