Help with INNER JOIN in SQL statement - DB2 1

[jtrapat1]

I'm using db2 and I need to add another table join to the following sql statement.

I'm confused about where I should add the INNER JOIN.
Maybe this statement wasn't written efficiently but I'm stuck.
I need to add another field into my select statement, bp13.dob_notify_date, and it's related to the bp12 table as such:
WHERE bp12.adds_id = bp13.adds_id
AND bp12.budget_yr = bp13.budget_yr
AND bp12.appr_id = bp13.appr_id
(where these three fields are the key in the bp13 table)

Can anyone help?

Thanks in Advance.
John

-----------------------------------------------------------

EXEC SQL DECLARE C1 CURSOR FOR
SELECT BPS12.adds_id, BPS12.child_seq, BPS12.adds_rec_amt, BPS12.program_desc
FROM
(
SELECT bp10.budget_yr, bp10.appr_id
FROM nysa.bp10t_appropriation bp10
WHERE bp10.budget_yr = :bp10.budget_yr
AND bp10.agency_code = :bp10.agency_code
) AS BPS10
INNER JOIN
(
SELECT bp12.budget_yr, bp12.adds_id, bp12.child_seq,
bp12.appr_id, bp12.adds_rec_amt, bp12.program_desc
FROM nysa.bp12t_approp_adds bp12
WHERE bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.adv_code = :bp12.adv_code
AND bp12.status = 'A'
AND bp12.adds_id NOT IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND status = 'A'
AND child_seq > 0
)
UNION
SELECT bp12.budget_yr, bp12.adds_id, bp12.child_seq,
bp12.appr_id, bp12.adds_rec_amt, bp12.program_desc
FROM nysa.bp12t_approp_adds bp12
WHERE bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.status = 'A'
AND bp12.child_seq > 0
AND bp12.adds_id IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND adv_code = :bp12.adv_code
AND status = 'A'
)
) AS BPS12
ON BPS10.budget_yr = BPS12.budget_yr
AND BPS10.appr_id = BPS12.appr_id
ORDER BY 4;

 

[Dagon]

You could add it to both parts of the UNION statement as follows:

EXEC SQL DECLARE C1 CURSOR FOR
SELECT BPS12.adds_id, BPS12.child_seq,
BPS12.adds_rec_amt, BPS12.program_desc,
BPS12.dob_notify_date
FROM
(
SELECT bp10.budget_yr, bp10.appr_id
FROM nysa.bp10t_appropriation bp10
WHERE bp10.budget_yr = :bp10.budget_yr
AND bp10.agency_code = :bp10.agency_code
) AS BPS10
INNER JOIN
(
SELECT bp12.budget_yr, bp12.adds_id,
bp12.child_seq,
bp12.appr_id, bp12.adds_rec_amt,
bp12.program_desc,
bp13.dob_notify_state
FROM nysa.bp12t_approp_adds bp12,
nysa.bp13t_approp_adds bp13
WHERE bp12.adds_id = bp13.adds_id
AND bp12.budget_yr = bp13.budget_yr
AND bp12.appr_id = bp13.appr_id
AND bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.adv_code = :bp12.adv_code
AND bp12.status = 'A'
AND bp12.adds_id NOT IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND status = 'A'
AND child_seq > 0
)
UNION
SELECT bp12.budget_yr, bp12.adds_id,
bp12.child_seq, bp12.appr_id,
bp12.adds_rec_amt, bp12.program_desc,
bp13.dob_notify_state
FROM nysa.bp12t_approp_adds bp12,
nysa.bp13t_approp_adds bp13
WHERE bp12.adds_id = bp13.adds_id
AND bp12.budget_yr = bp13.budget_yr
AND bp12.appr_id = bp13.appr_id
AND bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.status = 'A'
AND bp12.child_seq > 0
AND bp12.adds_id IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND adv_code = :bp12.adv_code
AND status = 'A'
)
) AS BPS12
ON BPS10.budget_yr = BPS12.budget_yr
AND BPS10.appr_id = BPS12.appr_id
ORDER BY 4;

Alternatively, you could just add it add the end:

EXEC SQL DECLARE C1 CURSOR FOR
SELECT BPS12.adds_id, BPS12.child_seq,
BPS12.adds_rec_amt, BPS12.program_des,
BPS13.dob_notify_date
FROM
(
SELECT bp10.budget_yr, bp10.appr_id
FROM nysa.bp10t_appropriation bp10
WHERE bp10.budget_yr = :bp10.budget_yr
AND bp10.agency_code = :bp10.agency_code
) AS BPS10
INNER JOIN
(
SELECT bp12.budget_yr, bp12.adds_id,
bp12.child_seq, bp12.appr_id,
bp12.adds_rec_amt, bp12.program_desc
FROM nysa.bp12t_approp_adds bp12
WHERE bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.adv_code = :bp12.adv_code
AND bp12.status = 'A'
AND bp12.adds_id NOT IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND status = 'A'
AND child_seq > 0
)
UNION
SELECT bp12.budget_yr, bp12.adds_id,
bp12.child_seq, bp12.appr_id,
bp12.adds_rec_amt, bp12.program_desc
FROM nysa.bp12t_approp_adds bp12
WHERE bp12.budget_yr = :bp12.budget_yr
AND bp12.neg_code = :bp12.neg_code
AND bp12.status = 'A'
AND bp12.child_seq > 0
AND bp12.adds_id IN (
SELECT DISTINCT adds_id
FROM nysa.bp12t_approp_adds
WHERE budget_yr = :bp12.budget_yr
AND neg_code = :bp12.neg_code
AND adv_code = :bp12.adv_code
AND status = 'A'
)
) AS BPS12
ON BPS10.budget_yr = BPS12.budget_yr
AND BPS10.appr_id = BPS12.appr_id
INNER JOIN nysa.bp13t_approp_adds bp13
ON bp12.adds_id = bp13.adds_id
AND bp12.budget_yr = bp13.budget_yr
AND bp12.appr_id = bp13.appr_id
ORDER BY 4;

I'd probably go for the first one because I'm not sure how DB2 would optimize a join to a union.

 

[jtrapat1]

thanks,

I tried the first one and that worked great!

John