Re: SQL Question

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

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.