a.JURISDICTIONCODE,
a.NAME,
a.FISCAL_YEAR,
a.FREECASH_AMOUNT,
a.DATE_CERTIFIED,
b.OUTSTD_ACCT_ID,
b.OUTSTD_ACCT_DESC,
b.OUTSTD_ACCT_NAME,
from
j.JURISDICTIONCODE,
j.NAME,
cf.FISCAL_YEAR,
cf.FREECASH_AMOUNT,
cf.DATE_CERTIFIED,
cf.DATE_RECEIVED
FROM
DLSGATEWAY.JURISDICTION j,
DLSGATEWAY.CERTIFICATION_FREECASH cf,
DLSGATEWAY.JURISDICTION_TYPE jt
WHERE
AND jt.JUR_TYPE_ID IN (1, 2, 4)
AND j.JURISDICTIONCODE=cf.JUR_CODE
AND j.JURISDICTIONCODE <= ‘351’)a,
j.JURISDICTIONCODE,
j.NAME,
o.FISCAL_YEAR,
o.OUTSTD_ACCT_ID,
oa.OUTSTD_ACCT_DESC,
oa.OUTSTD_ACCT_NAME,
o.AMOUNT_REPORTED
FROM
DLSGATEWAY.JURISDICTION j,
DLSGATEWAY.OUTSTD_RECEIVABLES o,
DLSGATEWAY
Hello,
I’m writing a nested query and I’m running into a problem.
select clause a produces JURISDICTIONCODES 1 thru 351
select clause b produces just one lonely JURISDICTIONCODE, 212 (which is correct).
When I run the query below (joining the two select clauses), I get the data I want but only for the the ONE jurisdictioncode, 212. How can I incorporate a clause (or which clause should I incorporate into the query) that allows NULLS values where select clause b has no data? My goal is to see all 351 records.
Please help! Confused as hell!
Thanks
Mike B
select b.AMOUNT_REPORTED
****(SELECT ********j.JUR_TYPE_ID=jt.JUR_TYPE_ID AND cf.fiscal_year = 2012
(SELECT ****
Try an outer join and see if there isn’t something “funny” with the data in one of the queries :
WHERE
a.JURSIDCITIONCODE=b.JURISDICTIONCODE (+)
AND a.FISCAL_YEAR=b.FISCAL_YEAR (+)
AND a.FISCAL_YEAR = 2012
There is likely something going on with one of the datasets that excludes it in the join.
So just adding the (+) is how an outer join is created? If so, that doesn’t seem to solve my problem. But I agree with your solution and dont see why it wouldnt work. I’ll try adding the outer join a different way.
A.NAME, A
A.FREECASH_AMOUNT, A
B.OUTSTD_ACCT_ID, B
B.AMOUNT_REPORTED
B.OUTSTD_ACCT_NAME,
(SELECT J
J.NAME, CF
CF.FREECASH_AMOUNT, CF
CF.DATE_RECEIVED
DLSGATEWAY.JURISDICTION J INNER JOIN
DLSGATEWAY.JURISDICTION_TYPE JT ON J.JUR_TYPE_ID = JT.JUR_TYPE_ID INNER JOIN
AND (J.JURISDICTIONCODE <= ‘351’)) A
LEFT OUTER JOIN***
J.JURISDICTIONCODE, J
O.FISCAL_YEAR, O
OA.OUTSTD_ACCT_DESC, OA
O.
Hello,
You were correct, it was an outer join issue. I went to the DESIGN VIEW (although I couldnt find the design view in SQL Navigator so I cut and pasted the query into Visual Studio. Do you know how to show the design view in navigator?) and added the outer join, on jurisdiction code only, there. It altered my syntax a bit, but got the same results. I’ve included if you care to take a look.
Thanks for your help!
SELECT A.JURISDICTIONCODE, .FISCAL_YEAR, .DATE_CERTIFIED, .OUTSTD_ACCT_DESC, FROM .JURISDICTIONCODE, .FISCAL_YEAR, .DATE_CERTIFIED, FROM DLSGATEWAY.CERTIFICATION_FREECASH CF ON J.JURISDICTIONCODE = CF.JUR_CODEWHERE (JT.JUR_TYPE_ID IN (1, 2, 4))
(SELECT .NAME, .OUTSTD_ACCT_ID, .OUTSTD_ACCT_NAME,
Ooops, strike that. I included the outer join on year too. My fault.