Re: An SQL learning opportunity for me -- MIDRANGE-L

That is the result needed. I felt that SQL could do this, but had no idea how
to approach the problem with SQL. I had a similar mess five years ago and due
to time constraints ended up writing an RPG program. It is way too easy to
fall back to the tool we know when better methods exist but have a learning
curve that exceeds the available time.

Thanks Rob for the time you invested in this issue for me.

John McKee

Quoting rob@xxxxxxxxx:

Baby steps (for the learning process).
Let's say you have a table that looks like this:
SELECT QSSEC2,QSFN01
FROM ROB/BSYSQSP2
ORDER BY QSSEC2,QSFN01
QSSEC2 QSFN01
LOU BPB101
PETE BPB400
ROB BPB101
ROB BPB400
SAM XYZ456

Now, we want a report that lists Lou and Pete because they are in one, but
not the other.

SELECT QSSEC2,QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400')
UNION
SELECT QSSEC2,QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101')
ORDER BY QSSEC2,QSFN01

QSSEC2 QSFN01
LOU BPB101
PETE BPB400

Now I will take that same select statement and switch the
SELECT QSSEC2, QSFN01
to something to show us what is needed. And, drop the code column from the
order by.
SELECT QSSEC2,'BPB400' AS QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400')
UNION
SELECT QSSEC2,'BPB101' AS QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101')
ORDER BY QSSEC2

QSSEC2 QSFN01
LOU BPB400
PETE BPB101

Now I will wrap that whole statement with this line at the beginning
INSERT INTO ROB/BSYSQSP2 (QSSEC2, QSFN01)
and drop the order by.

INSERT INTO ROB/BSYSQSP2 (QSSEC2, QSFN01)
SELECT QSSEC2,'BPB400' AS QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400')
UNION
SELECT QSSEC2,'BPB101' AS QSFN01
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB400'
AND QSSEC2 NOT IN(
SELECT QSSEC2
FROM ROB/BSYSQSP2
WHERE QSFN01='BPB101')
2 rows inserted in BSYSQSP2 in ROB.

Test
SELECT QSSEC2,QSFN01
FROM ROB/BSYSQSP2
ORDER BY QSSEC2,QSFN01
QSSEC2 QSFN01
LOU BPB101
LOU BPB400
PETE BPB101
PETE BPB400
ROB BPB101
ROB BPB400
SAM XYZ456

Is that what you are looking for?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com

John McKee <jmmckee@xxxxxxxxxxxxxx>
Sent by: midrange-l-bounces@xxxxxxxxxxxx
10/14/2008 01:39 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>

To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
An SQL learning opportunity for me

With the latest upgrade of our hospital database, a new twist was added.
For
users to be authorized to a function, they also need authorization to a
second
function.

What I have done thus far is create a simple query that groups by security
code
and selects the security records for the two functions, Made a 15 page
report
that I have to go through. Not a big deal. But, I am wondering if SQL
can do
this better, and I can learn an alternate method in the event this happens
again.

A simple attempt at a SELECT that accomplishes what my query did might be:

SELECT QSSEC2, QSFN01 FROM BSYSQSP2
WHERE QSFN01 = 'BPB101' or QSFN01 = 'BPB400'
GROUP BY QSSEC2
ORDER BY QSFN01

I'm not sure that is correct, but I am confidant that I can find any error
rather quickly in an actual SQL session. The file is keyed on QSSEC2, and
there can be a number of records for a given security code (QSSEC2).

I have two questions:

1) Based on what I have presented, is it possible to create a query that
only
shows QSSEC2 when there is a record containing BPB101 but not one (for the
same
security code) that contains BPB400?

2) If the answer to the first question is yes, can an INSERT be created
that
would add a security record containing BPB400 when that was needed?

My first reaction is to just go through my report and do it manually. This
isn't something I want to trash by me guessing at the process, I could
see an
RPG program that would loop through a logical keyed on QSSEC2. When a
BPB101
record was found, write a BPB400 record when the group changes.

I started thinking about an SQL statement to answer the first question. My
attempt did not make a lot of sense. I have seen references to using a
CTE in
other problems. Would that be beneficial here?

Just wanting to learn an alternate method, if it exists.

John McKee

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

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