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.


On 1/22/2020 9:44 AM, Charles Wilt wrote:
It's called a "row-value-expression", documented as predicate...

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


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

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

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.