Re: "Condition table" query
- From: sriramna <sriramna@xxxxxxxxxxx>
- Date: Fri, 16 May 2008 00:36:34 -0700 (PDT)
The ability to join tables on a _set_ of values would be great if it
works as described (V5R3).
I was thinking along the lines of a union of results from multiple
queries as also suggested. However this would require every
permutation of wildcard fields to be a separate query...
Will revert if successful!
--
Sriram
On May 16, 10:07 am, Rudy Canoza <pi...@xxxxxxxxxxxxxxxxxxxxx> wrote:
sriramna wrote:
I have a reference table 'LOOKUP' with 4 fields and sample values as
follows:
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:
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...
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 = [some calctype value]- Hide quoted text -
- Show quoted text -
.
- References:
- "Condition table" query
- From: sriramna
- "Condition table" query
- Prev by Date: Re: "Condition table" query
- Next by Date: Re: Disabling Expansion Unit on 170
- Previous by thread: Re: "Condition table" query
- Next by thread: DSPSFWRSC command
- Index(es):
Relevant Pages
|
