Month: February 2022

Posted on

Variation on JOIN and NULLs #ibmi #db2fori #SQL

Follow me for a little SQL “Amuse-gueule” and spot the difference.

Before you jump to your favorite SQL editor (ACS? DBeaver? Other?) and un-comment/run the sections one after an other, read first and ponder how similar these are and only differ on some subtile but fundamental aspects.

Use the comment section to list the differences you found in the resulting sets, share what came as a surprise, what you learned, and rave on the select * from p … q … pattern.

with p(rel1id, a1, a2, a3, x) as (
values
(1, ‘yes’, 4, 56, ‘blue’),
(2, ‘yes’, 4, 56, ‘red’),
(3, ‘no’, 4, 56, ‘white’),
(4, ‘no’, 4, NULL, ‘green’)

),
q(rel2id, a1, a2, a3, y) as (
values
(5, ‘yes’, 4, 56, ‘cats’),
(6, ‘no’, 4, NULL, ‘dog’)
)
/* Natural join baby */
— /* 1 */ select * from p join q using (a1, a2, a3)
/* almost natural */
— /* 2 */ select * from p join q on (p.a1, p.a2, p.a3) = (q.a1, q.a2, q.a3)
/* I wish this could work … RFE maybe…*/
— /* proto 3.1 */ select * from p join q on exists (values (p.a1, p.a2, p.a3) INTERSECT values (q.a1, q.a2, q.a3))
— /* proto 3.2 */ select * from p join q on (p.a1, p.a2, p.a3) is not distinct from (q.a1, q.a2, q.a3)
/* But here are the workarounds… we’re saved! */
— /* 3.1 */ select * from p , q where exists (values (p.a1, p.a2, p.a3) INTERSECT values (q.a1, q.a2, q.a3))
— /* 3.2 */ select * from p join q on p.a1 is not distinct from q.a1 and p.a2 is not distinct from q.a2 and p.a3 is not distinct from q.a3
;

Was this post of any interest? Then go visit @ChrJorgensen and wish him a nice IBM Champion year! This post is a follow-up on his reply.

Also this one:
A = B and C = D and E = ‘constant’
can be replaced with
( A, C, E ) = ( B, D, ‘constant’ )#IBMi

— Christian Jørgensen (@ChrJorgensen) February 15, 2022 https://platform.twitter.com/widgets.js

Well, this was my 1st… just been recognized as
IBM Champion!!!

Thank you very much @IBMChampions – feeling extremely honored! #IBMi

— Christian Jørgensen (@ChrJorgensen) February 14, 2022 https://platform.twitter.com/widgets.js

Posted on

How to build up the contents of email output with Interform NG2

This video demonstrates how you can build up the contents of email output by using variable information that is being received from the NG2 process. Interform NG has a wealth of variables available to us, it uses the xpath function extensively throughout , so whenever you get information from the spool file or from the xml input file, or whatever input comes into NG2, any of this information can be pushed into a variable then that variable can be transported into the email, or any other output. For more information on these products or for an in depth demo please get in touch with Louise, Business development manager at Logicmate Ltd https://www.linkedin.com/in/louise-eastwood-57973879/ *************************************************************************************************************** LETS CONNECT! Twitter – https://twitter.com/LogicmateL LinkedIn – https://www.linkedin.com/company/logi… YouTube – https://www.youtube.com/channel/UC6sP… Website – https://www.logicmate.co.uk/
From: Logicmate
Verified by MonsterInsights