Warning: Code not tested

Something like this maybe:

select t.* from tran t
join lookup l on t.plant = l.plant and t.group = l.group and t.wc = l.wc and
t.wt = l.wt
union
select t.* from tran t
join lookup l on t.plant = l.plant and t.group = l.group and t.wc = l.wc
where l.wt = '*'
union
select t.* from tran t
join lookup l on t.plant = l.plant and t.group = l.group
and t.wt = l.wt
where l.wc = '*'
union
select t.* from tran t
join lookup l on t.plant = l.plant and
t.wc = l.wc and t.wt = l.wt
where l.group = '*'
etc, etc, etc. But it get's really messy

Something like this might work, but may not perform well:

with myLK as (
select plant ||
case when Group <> '*" then Group else '****' end ||
case when WC <> '*" then WC else '****' end ||
case when WT <> '*' then WT else '****' end as LKUP
from Lookup
)
select t.* from tran t
where t.plant || t.Group || t.WC || t.WT
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || '**** || t.WC || t.WT
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || t.Group || '****' || t.WT
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || t.Group || t.WC ||'****'
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || '****' || '****' || t.WT
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || t.Group || '****' || '****'
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || '****' || t.WC ||'****'
in (select LKUP from myLK)
union
select t.* from tran t
where t.plant || '****' || '****' ||'****'
in (select LKUP from myLK)

Sam


"sriramna" wrote in message
news:7923a642-9846-4178-b0d1-45a6fe96285a@j33g2000pri.googlegroups.com...
>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...
>
> --
> Sriram

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]

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 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 -