Re: Need to capture information when something -- MIDRANGE-L

Alan, here's a few sites that may help out a bit plus assist any of our MidRange users
that contribute here. I refer to this material often. There's many more but this is a decent start.

http://www.tylogix.com/ - Select the SQL page on the left.

https://www.rpgpgm.com/ - Simon Hutchinson's site. Excellent SQL reference material.

http://www.as400pro.com - Browse by Topic then ISeries SQL.

Enjoy!

Very Respectfully,
Michael Mayer
IBM i Support / System Admin.
IT Operations.
The Florida Bar
651 E. Jefferson St
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906

message: 6
date: Wed, 12 Aug 2020 14:18:32 +0000
from: Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
subject: RE: [EXTERNAL] Re: Need to capture information when something
is NOT there

Thanks for your response Vernon
Ive been trying to get this final piece of the logic, with no success, for almost a week now Your suggestion of where (fld1, fld2) not in (select fld1, fld2 from 6th file) maybe what I need

Once I get a couple of minutes (lol) I will play with this

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
?If you're going through hell, keep going.?
Winston Churchill

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Vernon Hamberg
Sent: Wednesday, August 12, 2020 8:59 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: Need to capture information when something is NOT there

Hi Alan

If you add another EXCEPTION JOIN on those 2 columns, your list would be what you want - or you can do a NOT IN - where (fld1, fld2) not in (select fld1, fld2 from 6th file) - that's really the same as EXCEPTION JOIN, though.

Do you need that extra column? What would it contain? I'm guessing some constant label, such as "not in file 6" or 'X' - just add that as a constant to the "field" list.

So what am I missing??? :)

Vern

On 8/12/2020 7:19 AM, Alan Shore via MIDRANGE-L wrote:

Hi everyone
We are at V7r3
I hope that what I am about to ask, makes sense

I have an SQL query using 5 files with joins, exception joins etc,
that extracts a number of columns I would like to add another column,
that would use data from two of these columns and look in a sixth file
and let me know if there are NO records in that sixth file with those
2 values I cannot use any data from this sixth file as I am looking to
see if a "no record" condition exists

Anyone any ideas as to how to achieve this?

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

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

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

------------------------------

Subject: Digest Footer

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest 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.

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

------------------------------

End of MIDRANGE-L Digest, Vol 19, Issue 1264
********************************************

________________________________
Please note: Florida has very broad public records laws. Many written communications to or from The Florida Bar regarding Bar business may be considered public records, which must be made available to anyone upon request. Your e-mail communications may therefore be subject to public disclosure.