Re: question SQL WHERE ... IN ... ResultSet from SubSelect

[Charles Wilt <charles.wilt@xxxxxxxxx>]

It's called a "row-value-expression", documented as predicate...
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzch2pred.htm


Think of
WHERE (deer, deen, dededc)  = ( 'A','B','C')

As short hand for
WHERE deer = 'A'
            and deen = 'B'
            and dededc = 'C'

So in the case of
WHERE (deer, deen, dededc)
 IN ( SELECT deer, deen, dededc
        FROM wrslib / pde
        WHERE jojob = 'YR_CLOSE' AND
              jonbr = 475468 AND
              joentt = 'UP'
     );

Each row of the subselect will be compare to the row  (deer, deen, dededc)

If they match, then the WHERE is true.

Charles





On Wed, Jan 22, 2020 at 8:17 AM Voris, John <john.voris@xxxxxxxxxxxxx>
wrote:

> Question about a SQL statement.
> We are pulling some data from a journal.  My boss wants to use the
> following statement which uses a expression of WHERE ... IN
>
> Whenever I use the "IN THE LIST OF" - I have always ensured that I treated
> the loaded array as values, and built my Selection as
>
> . . . WHERE <Field> IN ( value1, value2, value3 )
>
> and sometimes I built it as
> . . .  WHERE <Value1> IN ( Field1, Field2, Field3 )
>
>
> His statement in a SubSelect is      WHERE ( Value1, Value2, Value3 )
> which I thought violated the syntax.
> But it seems that he is getting the proper results!
> His intent is really testing       WHERE  ( Key1, Key2, Key3)
>
> 1) I did not know that you could load the left-side of IN with MULTIPLE
> values
> 3) I would probably build a subSelect using WHERE EXISTS.
> 2) Or what I would do using IN is compare after CONCAT for a ( KEY1 + KEY2
> + KEY3 ) for my comparisons.
>
> Here is his statement.
> Notice how the lower SubSelect has WHERE ( field1, field2 field3 ) IN (
> Result-Set )
>
>
> UPDATE hrdbfad104 / pypde AS M
>     SET (deq0de, depded, deq0dc, depdcd, deq0ba, depbas, depcee, depcer,
>         depcba, deffyr, dedfyr, dedfbs, depslt, depxvr) =
>         (SELECT j.deq0de, j.depded, j.deq0dc, j.depdcd, j.deq0ba, j.depbas,
>             j.depcee, j.depcer, j.depcba, j.deffyr, j.dedfyr, j.dedfbs,
>             j.depslt, j.depxvr
>              FROM wrslib / pde AS J
>              WHERE jojob = 'YR_CLOSE' AND
>                    jonbr = 475468 AND
>                    joentt = 'UP' AND
>                    j.deer = m.deer AND
>                    j.deen = m.deen AND
>                    j.dededc = m.dededc)
>
>     WHERE (deer, deen, dededc) IN (
>
>     SELECT deer, deen, dededc
>         FROM wrslib / pde
>         WHERE jojob = 'YR_CLOSE' AND
>               jonbr = 475468 AND
>               joentt = 'UP');
>
>
> So my question is "    WHERE (deer, deen, dededc) IN (  "
> is the tright-sdie of IN   comparing these three fields deer, deen, dededc
> to a LIST OF VALUES
> or can it be compared to a RESULT SET ?
>
> John Voris
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com