Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 isnull in subquery

Author  Topic 

nvoyatzopoulos
Starting Member

7 Posts

Posted - 2013-03-05 : 09:06:28
SELECT
A.CMPCODE '?O?.????????S',
A.CMPNAME '????.????????S',
A.FLDNUM '?O?.??????',
B.FLDDSC '?????',
A.AXIA '????',
A.CODE ,
A.SYEAR '???S'
FROM
(select
ISNULL(FLDS.FLDNUM,-1) FLDNUM,
ISNULL(dbo.UDF_ConvertToFloat(NTPA_FLDS.FLDVALUE),0) AXIA,
ntpa.CODE,
(SELECT ntpa.SYEAR FROM NTPA WHERE NTPA.CODE = NTPA_FLDS.NTPACODE AND NTPA.CMPCODE=NTPA_FLDS.CMPCODE ) SYEAR,
(SELECT NTPA.CMPCODE FROM NTPA WHERE NTPA.CODE = NTPA_FLDS.NTPACODE AND NTPA.CMPCODE=NTPA_FLDS.CMPCODE ) CMPCODE,
(SELECT CMP.CMPNAME FROM CMP WHERE CMP.CMPCODE = NTPA_FLDS.CMPCODE ) CMPNAME ,
NTPA_FLDS.NTPACODE
from FLDS
left join NTPA_FLDS on NTPA_FLDS.FLDSID=FLDS.ID
left join NTPA ON NTPA.CODE = NTPA_FLDS.NTPACODE and ntpa.CMPCODE = NTPA_FLDS.CMPCODE
where
NTPA.CODE = NTPA_FLDS.NTPACODE AND
FLDS.TYPE=9
and NTPA.SAVED = 1
AND NTPA.CMPCODE = 125
GROUP BY NTPA.CODE,FLDS.FLDNUM,
NTPA_FLDS.FLDVALUE,FLDS.FLDDSC,NTPA_FLDS.NTPACODE,NTPA_FLDS.CMPCODE
) A
left JOIN
(select
isnull(GISOFLDS.FLDNUM,-1) as FLDNUM,
isnull(F.FLDNUM,'II')as FLDNUMS,
isnull(GISOFLDS.FLDDSC,'a') FLDDSC
from GISOFLDS
LEFT join (select ID, FLDNUM from FLDS where FLDS.TYPE = 9) F on GISOFLDS.FLDNUMS = F.FLDNUM
where (GISOFLDS.FLDTYPE = 3)
) B ON A.FLDNUM = B.FLDNUMS

the script above works fine but the isnull(GISOFLDS.FLDDSC,'a') FLDDSC) it keep bringing nulls in results ? what can i do for that ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-05 : 09:18:45
thats because the NULL is actually not a NULL value but absence of record from left join. so make it like


SELECT
A.CMPCODE '?O?.????????S',
A.CMPNAME '????.????????S',
A.FLDNUM '?O?.??????',
ISNULL(B.FLDDSC,'a') '?????',
A.AXIA '????',
A.CODE ,
A.SYEAR '???S'
FROM
(select
ISNULL(FLDS.FLDNUM,-1) FLDNUM,
ISNULL(dbo.UDF_ConvertToFloat(NTPA_FLDS.FLDVALUE),0) AXIA,
ntpa.CODE,
(SELECT ntpa.SYEAR FROM NTPA WHERE NTPA.CODE = NTPA_FLDS.NTPACODE AND NTPA.CMPCODE=NTPA_FLDS.CMPCODE ) SYEAR,
(SELECT NTPA.CMPCODE FROM NTPA WHERE NTPA.CODE = NTPA_FLDS.NTPACODE AND NTPA.CMPCODE=NTPA_FLDS.CMPCODE ) CMPCODE,
(SELECT CMP.CMPNAME FROM CMP WHERE CMP.CMPCODE = NTPA_FLDS.CMPCODE ) CMPNAME ,
NTPA_FLDS.NTPACODE
from FLDS
left join NTPA_FLDS on NTPA_FLDS.FLDSID=FLDS.ID
left join NTPA ON NTPA.CODE = NTPA_FLDS.NTPACODE and ntpa.CMPCODE = NTPA_FLDS.CMPCODE
where
NTPA.CODE = NTPA_FLDS.NTPACODE AND
FLDS.TYPE=9
and NTPA.SAVED = 1
AND NTPA.CMPCODE = 125
GROUP BY NTPA.CODE,FLDS.FLDNUM,
NTPA_FLDS.FLDVALUE,FLDS.FLDDSC,NTPA_FLDS.NTPACODE,NTPA_FLDS.CMPCODE
) A
left JOIN
(select
isnull(GISOFLDS.FLDNUM,-1) as FLDNUM,
isnull(F.FLDNUM,'II')as FLDNUMS,
isnull(GISOFLDS.FLDDSC,'a') FLDDSC
from GISOFLDS
LEFT join (select ID, FLDNUM from FLDS where FLDS.TYPE = 9) F on GISOFLDS.FLDNUMS = F.FLDNUM
where (GISOFLDS.FLDTYPE = 3)
) B ON A.FLDNUM = B.FLDNUMS


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-05 : 09:19:02
It could be because of the LEFT JOIN. ISNULL(GISOFLDS.FLDDSC, 'a') would return a non-null value, but then when you join that with A, you are doing a LEFT JOIN on A.FLDNUM = B.FLDNUMS. So if there is no match in B for a given A.FLDNUM, the value of FLDDSC in the corresponding row would be null.

Examine whether you need a LEFT JOIN with A, and if you do, why there are A.FLDNUM values for which there are no matching B.FLDNUMS.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-05 : 09:26:19
What you are doing is A LEFT JOIN B ON A.FLDNUM = B.FLDNUMS

Hence there can be NULL for each column of B in the main-select-list if there is no match while left joining.

That has NOTHING to do with the ISNULL() applied in the derived table named B.


Too old to Rock'n'Roll too young to die.





lol - double-sniped
Go to Top of Page
   

- Advertisement -