Toad World® Forums

Allowing NULL values (I think?)

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.