SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 isnull in subquery
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nvoyatzopoulos
Starting Member

Greece
7 Posts

Posted - 03/05/2013 :  09:06:28  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/05/2013 :  09:18:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 03/05/2013 :  09:19:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 03/05/2013 :  09:26:19  Show Profile  Visit webfred's Homepage  Reply with Quote
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

Edited by - webfred on 03/05/2013 09:27:18
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000