Coldfusion query returns no records when same query returns records in winSQL
There are two tables: u_case and schedule There is a one to many relationship between u_case and the schedule u_case.cs_caseid = schedule.sd_caseid Database - SqlServer
I want all records from 13 in u_case.cs_chapter but don't have "dline" in u_schedule.sd_class or "pln13" in schedule.sd_type file.
This EXACT query runs in WinSQL and returns 22 records. The same EXACT query returns 2 records when using ColdFusion (verified with cfdump)
Are there any suggestions as to why Coldfusion has problems with this and how to fix it? I found several queries with this problem.
SELECT a.cs_caseid, a.cs_case_number, a.cs_date_filed, a.cs_short_title, a.cs_office, a.cs_type
FROM u_case a
WHERE a.cs_chapter = 13
AND a.cs_date_term is null
AND 0 = (
select count(b.sd_caseid)
from schedule b
WHERE b.sd_caseid = a.cs_caseid
AND b.sd_class = "dline"
and b.sd_type = "pln13"
)
I have this problem on 3 servers (2 CF10 and 1 CF9). I also have other queries where this happens ... often the CF query does not return any records.
Thanks in advance.
source to share
I wonder if CF just has a problem parsing the weird SQL format you have there? Try the following:
SELECT a.cs_caseid, a.cs_case_number, a.cs_date_filed, a.cs_short_title, a.cs_office, a.cs_type
FROM u_case a
WHERE a.cs_chapter = 13
AND a.cs_date_term is null
AND NOT EXISTS (
SELECT 1
FROM schedule b
WHERE b.sd_caseid = a.cs_caseid
AND b.sd_class = "dline"
AND b.sd_type = "pln13"
)
source to share