"Condition table" query - IBM AS400

This is a discussion on "Condition table" query - IBM AS400 ; 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 ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: "Condition table" query

  1. "Condition table" query

    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

  2. Re: "Condition table" query

    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




  3. Re: "Condition table" query

    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]

  4. Re: "Condition table" query

    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 -



+ Reply to Thread