Re: question SQL WHERE ... IN ... ResultSet from SubSelect -- MIDRANGE-L

Exactly right - just one thing to remember - be sure to use parentheses around the names between WHERE and IN - without them, it does violate syntax.

HTH
Vern

On 1/22/2020 9:44 AM, Charles Wilt wrote:

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