using isnull - SQL Server Forums

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

using isnull

Author

Topic

selvakumark
Starting Member

5 Posts

Posted - 2008-02-28 : 01:24:01

for the query i created i need zeros where ever the filed is blank. i have used count(acc) for selecting the count . can any one help me out with sample query. Thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 01:26:05

Maybe it's count(*) you need? but I doubt that.
COUNT(colname) always return a value that is 0 or greater.

Post your full query here. I think you need to change an INNER JOIN to a LEFT JOIN.

E 12°55'05.25"
N 56°04'39.16"

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2008-02-28 : 01:37:08

Hi

These are some various methods CREATE TABLE #test
(col1 VARCHAR(10),
col2 VARCHAR(10)
)

INSERT INTO #test (col1,col2)VALUES(NULL,'1')
INSERT INTO #test (col1,col2)VALUES(NULL,'2')
INSERT INTO #test (col1,col2)VALUES(' ','3')
INSERT INTO #test (col1,col2)VALUES(' ','4')

SELECT col1 = CASE
WHEN RTRIM(LTRIM(col1)) = '' THEN '0'

ELSE ISNULL(col1,0)
END
,col2 FROM #test

DROP TABLE #test

Jack Vamvas
--------------------
Search IT jobs from multiple sources- http://www.ITjobfeed.com

selvakumark
Starting Member

5 Posts

Posted - 2008-02-28 : 01:45:43

Hi im posting the entire query. If you could find a way please help me . Thanks for your early info.

SELECT DISTINCT branch,
(select count(doc_ac_num) from MandateSummaryReport where branch=a.branch and segmentcode in ('10','20','25') group by branch) AS expectedCON,
(select count(doc_ac_num) from ddtmain where bnchcode=a.branch and CurrentStatus=0 and DateTakenAway_CFU='NULL'and segmentcode in ('10','20','25') group by bnchcode) AS missCON,
(select count(doc_ac_num) from MandateSummaryReport where branch=a.branch and segmentcode in ('30','35','40','42','44','46','50','60','63','65') group by branch) AS expectedCOR,
(select count(doc_ac_num) from ddtmain where bnchcode=a.branch and CurrentStatus=0 and DateTakenAway_CFU='NULL'and segmentcode in ('30','35','40','42','44','46','50','60','63','65') group by bnchcode) AS missCOR,
(select count(doc_ac_num) from MandateSummaryReport where branch=a.branch and segmentcode in ('11','12','14','16','26','95') group by branch) AS expectedOTR,
(select count(doc_ac_num) from ddtmain where bnchcode=a.branch and CurrentStatus=0 and DateTakenAway_CFU='NULL'and segmentcode in ('11','12','14','16','26','95') group by bnchcode) AS missOTR
FROM bnch AS a;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 04:31:42

I think this code will run faster and will be easier to maintain.

SELECT		b.Branch,
		SUM(CASE WHEN msr.SegmentCode IN ('10', '20', '25') THEN 1 ELSE 0 END) AS expectedCON,
		SUM(CASE WHEN dm.Segmentcode IN ('10', '20', '25') THEN 1 ELSE 0 END) AS missCON,
		SUM(CASE WHEN msr.SegmentCode IN ('30', '35', '40', '42', '44', '46', '50', '60', '63', '65') THEN 1 ELSE 0 END) AS expectedCOR,
		SUM(CASE WHEN dm.SegmentCode IN ('30', '35', '40', '42', '44', '46', '50', '60', '63', '65') THEN 1 ELSE 0 END) AS missCOR,
		SUM(CASE WHEN msr.SegmentCode IN ('11', '12', '14', '16', '26', '95') THEN 1 ELSE 0 END) AS expectedOTR,
		SUM(CASE WHEN dm.SegmentCode IN ('11', '12', '14', '16', '26', '95') THEN 1 ELSE 0 END) AS missOTR
FROM		Bnch AS b
LEFT JOIN	MandateSummaryReport AS msr ON msr.Branch = b.Branch
LEFT JOIN	DdtMain AS dm ON dm.BnchCode = b.Branch
			AND dm.CurrentStatus = 0
			AND dm.DateTakenAway_CFU = 'NULL'
GROUP BY	b.Branch

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-02-28 : 21:56:32

quote:
Originally posted by Peso

I think this code will run faster and will be easier to maintain.[code]

umm. Understatement?

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources