Re: An SQL learning opportunity for me

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