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

Jack,

I see that you have the result that you want. But I'd be curious to know
if you get the same results rewriting like so.

select a.field1
, case b.rcdtype when 'ABC' then b.rdItmVlu else ' ' end,
, case b.rcdtype when 'XYZ' then b.rdItmVlu else ' ' end
From Header a
join DetailFile B
ON a.Key1 = B.Key1
WHERE b.RcdType in ('ABC', 'XYZ')
AND b.rdItmVlu <> ' '

Your where clause indicates that you only want records that have a detail
record that is either XYZ or ABC or Both but not if any exists. Therefore I
removed the left outer join in my statement.
My Where clause will select detail records that have either ABC or XYZ.

Your statement will work well if you have only 1 XYZ and 1 ABC. But what
happens when there are more than one detail record for each of those
values? If you have two XYZs and one ABC. your select will return two
records which is fine if that's what you want. Just understand that the
same 1 DetailFile B record will be joined with the two from DetailFile C in
the result set.

My statement has an issue too. As written, if the detail file has both ABC
and XYZ.You will get two records in the result set even though I've used a
case statement to put ABC in column 2 and XYZ in column 3.

If you are going to have only one record type of RCDTYPE of each value in
the DETAIL file, then you could add a group by and wrap the case statements
with MAX.

select a.field1
, MAX(case b.rcdtype when 'ABC' then b.rdItmVlu else ' ' end),
, MAX(case b.rcdtype when 'XYZ' then b.rdItmVlu else ' ' end)
From Header a
join DetailFile B
ON a.Key1 = B.Key1
WHERE b.RcdType in ('ABC', 'XYZ')
AND b.rdItmVlu <> ' '
GROUP BY a.field1

This is just food for thought. Experiment some more if you have the time.

On Thu, Mar 24, 2016 at 9:23 AM, Jack Prucha <Jack.Prucha@xxxxxxx> wrote:

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