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.
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.NTPACODEfrom FLDSleft join NTPA_FLDS on NTPA_FLDS.FLDSID=FLDS.ID left join NTPA ON NTPA.CODE = NTPA_FLDS.NTPACODE and ntpa.CMPCODE = NTPA_FLDS.CMPCODEwhere NTPA.CODE = NTPA_FLDS.NTPACODE ANDFLDS.TYPE=9 and NTPA.SAVED = 1 AND NTPA.CMPCODE = 125GROUP BY NTPA.CODE,FLDS.FLDNUM,NTPA_FLDS.FLDVALUE,FLDS.FLDDSC,NTPA_FLDS.NTPACODE,NTPA_FLDS.CMPCODE ) Aleft 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 likeSELECT 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.NTPACODEfrom FLDSleft join NTPA_FLDS on NTPA_FLDS.FLDSID=FLDS.ID left join NTPA ON NTPA.CODE = NTPA_FLDS.NTPACODE and ntpa.CMPCODE = NTPA_FLDS.CMPCODEwhere NTPA.CODE = NTPA_FLDS.NTPACODE ANDFLDS.TYPE=9 and NTPA.SAVED = 1 AND NTPA.CMPCODE = 125GROUP BY NTPA.CODE,FLDS.FLDNUM,NTPA_FLDS.FLDVALUE,FLDS.FLDDSC,NTPA_FLDS.NTPACODE,NTPA_FLDS.CMPCODE ) Aleft 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 MVPhttp://visakhm.blogspot.com/ |
|
|
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. |
|
|
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.FLDNUMSHence 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 |
|
|
|
|
|
|
|