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  SQL Server 2005 Forums  Transact-SQL (2005)  sql 2005 isnull problems

Author  Topic  derya666 Starting Member 1 Post Posted - 2010-09-29 : 06:46:07 Hello everybody i have a problem in a query which is at the below. IASCUSTOMER and IASCUSTREP are tables, the others are views. I also send this query with csv attachments by using msdb.dbo.sp_send_dbmail object. So everthing works fine. But sometimes REPLACE(CAST(ISNULL(MO2.CR1,0) AS DECIMAL(10,2)),'.',',') AS AGL, this columns and the following 6 columns get 0-zero altough there are not. All 7 colums show zero. But i run same query, results are not zero. Did you face this kind of problem before. SELECT CS.COMPANY , CS.CUSTOMER, CS.NAME1, CS.TAXNUM, CR.REPCODE, REPLACE(CAST(CS.CREDITLIMIT AS DECIMAL(10,2)),'.',',') AS AKL, REPLACE(CAST(CS.CREDITLIMIT1 AS DECIMAL(10,2)),'.',',') AS TKL, REPLACE(CAST(ISNULL(MO2.CR1,0) AS DECIMAL(10,2)),'.',',') AS AGL, REPLACE(CAST(ISNULL(MO2.CR2,0) AS DECIMAL(10,2)),'.',',') AS TGL, REPLACE(CAST(ISNULL(GL.BAL,0) AS DECIMAL(10,2)),'.',',') AS BAK, REPLACE(CAST(ISNULL(GL.BAL1,0) AS DECIMAL(10,2)),'.',',') AS DGBAK, REPLACE(CAST(ISNULL(GL.BAL2,0) AS DECIMAL(10,2)),'.',',') AS GRBAK, REPLACE(CAST(ISNULL(CEKS.CSBAL,0) AS DECIMAL(10,2)) ,'.',',') AS CEKSE, REPLACE(CAST(ISNULL(CEKS.TOPLAMRISK,0) AS DECIMAL(10,2)),'.',',') AS TOTCEKSE, REPLACE(CAST((CS.CREDITLIMIT+ISNULL(MO2.CR1,0)+(CASE WHEN EX.ADDCR1<0 THEN 0 ELSE EX.ADDCR1 END )-ISNULL(GL.BAL,0)-ISNULL(GL.BAL1,0)-ISNULL(GL.BAL2,0)) AS DECIMAL(10,2)),'.',',') AS KAKL, REPLACE(CAST((CS.CREDITLIMIT1+ISNULL(MO2.CR2,0)+(CASE WHEN EX.ADDCR2<0 THEN 0 ELSE EX.ADDCR2 END )-ISNULL(GL.BAL,0)-ISNULL(GL.BAL1,0)-ISNULL(GL.BAL2,0)-ISNULL(CEKS.TOPLAMRISK,0)) AS DECIMAL(10,2)),'.',',') AS KTKL FROM MYDB..IASCUSTOMER CS LEFT OUTER JOIN MYDB..IASCUSTREP CR ON CS.COMPANY = CR.COMPANY AND CS.CUSTOMER = CR.CUSTOMER LEFT OUTER JOIN MYDB..MO2_INSLIMITTL MO2 ON CS.COMPANY = MO2.COMPANY AND CS.CUSTOMER = MO2.CUSTOMER LEFT OUTER JOIN MYDB..MO4_GLTLBALALL GL ON CS.COMPANY = GL.COMPANY AND CS.CUSTOMER = GL.CUSTOMER LEFT OUTER JOIN MYDB..MO6_CEKSENETRISK CEKS ON CS.COMPANY = CEKS.COMPANY AND CS.CUSTOMER = CEKS.CUSTOMER LEFT OUTER JOIN MYDB..MO7_ADDEXTRAINSL EX ON CS.COMPANY = EX.COMPANY AND CS.CUSTOMER = EX.CUSTOMER WHERE (CS.COMPANY = '32') AND CR.ISREP = 1 AND (CS.ISCUSTORVEND = 1 OR CS.ISCUSTORVEND = 3) AND CS.ISDELETE = 0 AND CS.CUSTOMER LIKE '12345'