can u help me debug this!!!! - 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

can u help me debug this!!!!

Author

Topic

bentong
Starting Member

5 Posts

Posted - 2007-08-23 : 07:55:56

to all the experts can u help me about my codes Truncated incorrect DOUBLE value: 'NoOfPositives'
here's my code

select
ref_region.regionName,
ref_facility.facilityName,
ref_facility.adr,
ref_facility.facilityNo,

(select count(*)
from dto_initial_screening
where (dto_initial_screening.facilityNo = ref_facility.facilityNo and
dto_initial_screening.testDt between '2007-01-01' and '2008-01-01')) as 'TotalScreened',

(select count(*)
from dto_initial_screening
where (dto_initial_screening.testResult = 'POS' and
dto_initial_screening.facilityNo = ref_facility.facilityNo and
dto_initial_screening.testDt between '2007-01-01' and '2008-01-01')) as 'NoOfPositives'

from dto_initial_screening
left join ref_facility on dto_initial_screening.facilityNo = ref_facility.facilityNo
left join ref_region on ref_facility.adrRegionCd = ref_region.regionCd

where
('NoOfPositives') < (select Cast(paramVal as UNSIGNED) from ref_param where paramCd = 'minPositives')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-23 : 08:14:50

[code]SELECT *
FROM
(
SELECT
ref_region.regionName,
ref_facility.facilityName,
ref_facility.adr,
ref_facility.facilityNo,
(
SELECT COUNT(*)
FROM dto_initial_screening
WHERE (dto_initial_screening.facilityNo = ref_facility.facilityNo
AND dto_initial_screening.testDt BETWEEN '2007-01-01' AND '2008-01-01')
) AS 'TotalScreened',
(
SELECT COUNT(*)
FROM dto_initial_screening
WHERE (dto_initial_screening.testResult = 'POS'
AND dto_initial_screening.facilityNo = ref_facility.facilityNo
AND dto_initial_screening.testDt BETWEEN '2007-01-01' AND '2008-01-01')
) AS 'NoOfPositives'
FROM dto_initial_screening
left JOIN ref_facility ON dto_initial_screening.facilityNo = ref_facility.facilityNo
left JOIN ref_region ON ref_facility.adrRegionCd = ref_region.regionCd
) a
WHERE ('NoOfPositives') < (SELECT CAST(paramVal AS UNSIGNED) FROM ref_param WHERE paramCd = 'minPositives')
[/code]

Note : debug only. Query will still have bad performance. You should rewrite it

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 10:23:41

[code]SELECT r.regionName,
f.facilityName,
f.adr,
f.facilityNo,
sum(case when s.testDt >= '2007-01-01' and s.testDt < '2008-01-01' then 1 else 0 end) as TotalScreened
sum(case when s.testResult = 'POS' and s.testDt >= '2007-01-01' and s.testDt < '2008-01-01' then 1 else 0 end) as NoOfPositives
FROM dto_initial_screening AS s
LEFT JOIN ref_facility AS f ON f.facilityNo = s.facilityNo
LEFT JOIN ref_region AS r ON r.regionCd = f.adrRegionCd
GROUP BY r.regionName,
f.facilityName,
f.adr,
f.facilityNo
HAVING sum(case when s.testResult = 'POS' and s.testDt >= '2007-01-01' and s.testDt < '2008-01-01' then 1 else 0 end) < (select Cast(paramVal as INT) from ref_param where paramCd = 'minPositives')[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources