| Author |
 Topic  |
|
|
nvoyatzopoulos
Starting Member
Greece
7 Posts |
 Posted - 03/05/2013 : 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
India
48032 Posts |
Posted - 03/05/2013 : 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/
|
 |
|
|
James K
Flowing Fount of Yak Knowledge
1730 Posts |
Posted - 03/05/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8529 Posts |
Posted - 03/05/2013 : 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  |
Edited by - webfred on 03/05/2013 09:27:18 |
|
|
| |
Topic |
|
|
|
isnull in subquery
