"Condition table" query

sriramna · #1 (**permalink**) 05-15-2008, 04:42 PM

I have a reference table 'LOOKUP' with 4 fields and sample values as follows:

HTML Code:

Plant  Group  WorkCentre  WageType   CalcType
1234   1002   W001           9Y10           A
1234   *      W002           9Y11           C
1234   1005   W001           *              D
1234   1002   W003           *              C
1234   1007   *              *              C
1234   1002   *              9Y32           C

etc.

There is another table with transaction values:

HTML Code:

Plant  Group  WorkCentre  WageType   Date
1234   1002    W003      9Y13        15/05/08
1234   1005    W001      9Y13        15/05/08

etc.

I need a query which will return all records in the transaction table which find a match for a given CalcType in the Lookup table, with * considered as a wildcard in the lookup table (i.e., any value is OK in the transaction table as long as the other specific values match in the lookup table).

The problem is that more than one field value could be a '*' in the lookup table, as shown above.

Is it at all possible via SQL to retrieve the transactional records which find any match for a given CalcType in the lookup table? Or am I constrained to use procedural logic, examining each transaction against every record in the lookup table until I find a match or run to the end?

Would appreciate any ideas...

#2 (**permalink**) 05-16-2008, 06:32 PM

You might consider breaking up this and using the UNION to compile your results.

#3 (**permalink**) 05-17-2008, 06:06 AM

With four fields, each of which could have a "wildcard" specification, this would result in over 16 union queries...

#4 (**permalink**) 05-20-2008, 05:02 PM

Perhaps create a (normalized) view that eliminates the wildcards and explicitly names every combo in the "Condition table" -> then join to the view. It sounds like the wildcards are there to make data entry easier, but they're making the table difficult to use.

sriramna · #5 (**permalink**) 05-25-2008, 08:00 AM

Shouldn't something along the following lines work?


SELECT t.* 

FROM trans_table t JOIN lookup_table l 

 ON l.plant IN ('*', t.plant) 

 AND l.GROUP IN ('*', t.GROUP) 

 AND l.workcentre IN ('*', t.workcentre) 

 AND l.wagetype IN ('*', t.wagetype) 

 WHERE t.calctype = [SomeCalcType_Value]

#6 (**permalink**) 05-28-2008, 01:02 PM

SELECT t.* , l.calctype 

FROM trans_table t JOIN lookup_table l 

ON l.plant IN ('*', t.plant) 

AND l.GROUP IN ('*', t.GROUP) 

AND l.workcentre IN ('*', t.workcentre) 

AND l.wagetype IN ('*', t.wagetype)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

User Name		Remember Me?
Password

"Condition table" query

ANSWER(S):