Re: SQL Question

Nice! First non-NULL is returned. For some reason I wondered about the order of the parameters, but I think it probably doesn't matter, since we're talking about the JOIN criteria, right? They should be equal - other columns might not be the same in both tables. OK, that's what I was wondering about!

*Regards*

*Vern Hamberg*

IBM Champion 2025 <cid:part1.8vVIll4c.hnTT0mO4@centurylink.net> CAAC (COMMON Americas Advisory Council) IBM Influencer 2023

On 3/5/2025 12:10 PM, Daniel Gross wrote:

If you only need the decision between two values being NOT NULL - then COALESCE or IFNULL are also good, because it's more compact, and doesn't add the complexity of a CASE expression.

COALESCE(leftTable.col, rightTable.col)

HTH
Daniel




Von meinem iPhone gesendet

Am 05.03.2025 um 18:21 schrieb Vern Hamberg via MIDRANGE-L<midrange-l@xxxxxxxxxxxxxxxxxx>:

:) Good tip, hidden treasure, methinks. The CASE takes care of the matter, as well, though it looks more complex.
--

*Regards*

*Vern Hamberg*

IBM Champion 2025<cid:part1.dsXXx0Ak.fwoiAwt4@centurylink.net> CAAC (COMMON Americas Advisory Council) IBM Influencer 2023

On 3/5/2025 9:42 AM, Charles Wilt wrote:
On Wed, Mar 5, 2025 at 7:43 AM Vern Hamberg via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi Greg

No matter what kind of OUTER JOIN, LEFT RIGHT or DULL, if you say SELECT
* you'll get the doubling up of the same columns.

No, no you won't IF you use USING (MYKEY) instead of ON A.MYKEY = B.MYKEY...
https://www.ibm.com/docs/en/i/7.4?topic=clause-joined-table#rbafzjoinedt__title__3

The result table of the join contains the columns from the USING clause
first, then the columns from the first table of the join that were not in
the USING clause, followed by the remaining columns from the second table
of the join that were not in the USING clause. Any column specified in the
USING clause cannot be qualified in the query.


It's a sometimes useful tidbit to know...

Not always useful on the IBM i given the legacy standards of most shops to
have unique column names.
Also last I checked, it only works when dealing with a single join

Charles

--
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
athttps://archive.midrange.com/midrange-l.

Please contactsupport@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.