RE: SQL Select This and/or That -- MIDRANGE-L

Thanks to all (Alan, Darren, Matt, Rob and co-worker Lewis) this is what I'm using:

select a.field1, COALESCE(b.rdItmVlu, ' '), COALESCE(c.rdItmVlu, ' ') From Header a
left outer join DetailFile B
On a.Key1 = B.Key1
AND b.RcdType = 'ABC'
AND b.rdItmVlu <> ' '
left outer join DetailFile C
On a.Key1 = c.Key1
AND c.RcdType = 'XYZ'
AND c.rdItmVlu <> ' '
Where B.rditmVlu IS NOT NULL Or C.rdImVlu IS NOT NULL;

It selects headers only when at least one of the two conditions exist in the detail. It also includes the detail data from multiple records on the same line. Forgive me if I didn't explain it clearly enough. Yes, the actual statement has a bunch more selection criteria that has nothing to do with my selecting the detail records correctly.

Thanks again.

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Michael Schutte
Sent: Thursday, March 24, 2016 9:11 AM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Select This and/or That

Now, I'll take that back. The entire statement is there, I didn't read it write because it wasn't written to my liking. HAHA.

On Thu, Mar 24, 2016 at 9:09 AM, Michael Schutte <mschutte369@xxxxxxxxx>
wrote:

That's what happens when people just assume we know what's going on
with their stuff and only provide us snippets of their code. What
would be the harm of providing the entire sql statement? Are they
worried about us figuring something out about their system? If that's
a concern, then just rename the files and fields. Hiding information
only limits our ability to help!!!

On Wed, Mar 23, 2016 at 1:26 PM, Darren Strong <darren@xxxxxxxxx> wrote:

I didn't get that the DetailFile was the same file. Alan Shore's
solution may be the better one.

From: Darren Strong <darren@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/23/2016 01:23 PM
Subject: Re: SQL Select This and/or That
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

Try a "left outer join"

select a.field1, b.rdItmVlu, c.rdItmVlu from HeaderFile A left outer
join DetailFile B on a.Key1 = B.Key1
AND b.RcdType = 'ABC'
AND b.rdItmVlu <> ' '

left outer join DetailFile C
on a.Key1 = c.Key1
AND c.RcdType = 'XYZ'
AND c.rdItmVlu <> ' '

From: Jack Prucha <Jack.Prucha@xxxxxxx>
To: "midrange-l@xxxxxxxxxxxx" <midrange-l@xxxxxxxxxxxx>
Date: 03/23/2016 01:15 PM
Subject: SQL Select This and/or That
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>

I'm having a hard time visualizing how to do this. There's a
"Header" and "Detail" file where I want to return information from
both whenever at least one of these situations occur. What I've
coded only returns if both situations occur. This gets me most of what I want:

select a.field1, b.rdItmVlu, c.rdItmVlu from HeaderFile A,
DetailFile B, DetailFile C

WHERE a.Key1 = B.Key1
AND b.RcdType = 'ABC'
AND b.rdItmVlu <> ' '

AND a.Key1 = c.Key1
AND c.RcdType = 'XYZ'
AND c.rdItmVlu <> ' '

Selection should occur if either the ABC or XYZ record has the, well,
undesired values rather than my example which selects only if both
records meet the selection criteria. I know I can use the Coalesce
to default the rditmvlu for which ever record isn't there. But how
can I "subselect" and return the value back to the original select?
Can't assume each record type exists.

7.1 with all the TRs installed

Thanks in advance!

Jack Prucha
Programming Team Supervisor
College Foundation, Inc.
919.835.2530

This email, including any documents, files, or previous email
messages attached to it, has been sent from an email account of
College Foundation Inc., (CFI) and may contain confidential,
proprietary, or legally privileged information belonging to CFI. If
you are not the intended recipient, any dissemination, distribution,
or copying of this email or its attachments is strictly prohibited.
If you have received this email in error, please immediately notify
the sender by email and destroy the original email and any
attachments.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take
a moment to review the archives at
http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.