Thanks Michael
https://www.rpgpgm.com/ - Simon Hutchinson's site. Excellent SQL reference material.
I already have this bookmarked - and I agree - EXCELLENT material on EVERYTHING and ANYTHING
http://www.as400pro.com - Browse by Topic then ISeries SQL.
Also have this bookmarked
The problem I was having was, to get my answer, I didn't know what I was looking/searching for
Vernon supplied that information for me
The third web page, I did NOT know about. tylogix
I have bookmarked that and will peruse it at my leasure.
(its been so long since I had any leisure - I forgot how to spell it correctly)
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 Mayer, Michael via MIDRANGE-L
Sent: Wednesday, August 12, 2020 10:35 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Mayer, Michael <MMayer@xxxxxxxxxxxxxx>
Subject: [EXTERNAL] Re: Need to capture information when something
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.
--
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
As an Amazon Associate we earn from qualifying purchases.