Complex join = no results (for a query that shouldreturn results)

[jchapman16]

I am trying to use a rather complex join that returns many results when the SQL
server is queried directly, but returns no results when used on a coldfusion
page.

This is the SQL statement:

SELECT Courses.Crs_Num AS Course_Id, Courses.Crs_Title, CrseSkil.K_Skillid,
CrseSkil.Skill_Desc AS Skill
FROM Courses LEFT OUTER JOIN CrseSkil
ON CrseSkil.K_Crsnum = Courses.Crs_Num
AND CrseSkil.K_Skillid = (SELECT MAX(CrseSkil.K_Skillid) FROM CrseSkil WHERE
CrseSkil.K_Crsnum = Courses.Crs_Num)
WHERE Crs_OType = 'WBT'
AND Courses.ATM_Usage != 'N'
AND (Courses.Eff_Date_End = NULL OR Courses.Eff_Date_End > getdate ())
UNION
SELECT SSCourse.Course_Id, SSCourse.Crs_Title, SSCrSkil.K_Skillid,
SSCrSkil.Skill_Desc AS Skill
FROM SSCourse LEFT OUTER JOIN SSCrSkil
ON SSCrSkil.K_Crsnum = SSCourse.Course_Id
AND SSCrSkil.K_Skillid = (SELECT MAX(SSCrSkil.K_Skillid) FROM SSCrSkil WHERE
SSCrSkil.K_Crsnum = SSCourse.Course_Id)
WHERE Media_Type = 'WBT'
AND SSCourse.ATM_Usage != 'N'
AND (SSCourse.Eff_Date_End = NULL OR SSCourse.Eff_Date_End > getdate ())
ORDER BY 2 asc;

Background: The SSCourse and Courses tables contain information about
workshops; I'm selecting just the classes that match my criteria (type = 'wbt',
usage != 'n', within my date range) along with a skill description from a
separate table (SSCrSkil and CrseSkil). The skills table allows a single course
to have many skills, or none at all, but I only want to return a single skill
(or NULL) for each workshop that match my criteria. You can see from the above
SQL that as part of the join I use SELECT MAX(Skillid) to limit the results to
just the skill with the highest ID (or NULL).

This query returns the expected results when I query the SQL server directly
(about 40 records). Unfortunately when I add this query to my coldfusion code,
there are 0 (zero) results. Is there a problem with coldfusion returning the
correct results for complex joins? Simple SQL queries work just fine. For
reference, here's the CF code:

<cfquery name="CourseInfo" datasource=#AppDataSource#>
SELECT Courses.Crs_Num AS Course_Id, Courses.Crs_Title, CrseSkil.K_Skillid,
CrseSkil.Skill_Desc AS Skill
FROM Courses LEFT OUTER JOIN CrseSkil
ON CrseSkil.K_Crsnum = Courses.Crs_Num
AND CrseSkil.K_Skillid = (SELECT MAX(CrseSkil.K_Skillid) FROM CrseSkil WHERE
CrseSkil.K_Crsnum = Courses.Crs_Num)
WHERE Crs_OType = '#URL.Type#'
AND Courses.ATM_Usage != 'N'
AND (Courses.Eff_Date_End = NULL OR Courses.Eff_Date_End > getdate ())
<cfif URL.keyword NEQ "all">
AND Courses.Topics LIKE '%#URL.keyword#%'
</cfif>
UNION
SELECT SSCourse.Course_Id, SSCourse.Crs_Title, SSCrSkil.K_Skillid,
SSCrSkil.Skill_Desc AS Skill
FROM SSCourse LEFT OUTER JOIN SSCrSkil
ON SSCrSkil.K_Crsnum = SSCourse.Course_Id
AND SSCrSkil.K_Skillid = (SELECT MAX(SSCrSkil.K_Skillid) FROM SSCrSkil WHERE
SSCrSkil.K_Crsnum = SSCourse.Course_Id)
WHERE Media_Type = '#URL.Type#'
AND SSCourse.ATM_Usage != 'N'
AND (SSCourse.Eff_Date_End = NULL OR SSCourse.Eff_Date_End > getdate ())
<cfif URL.keyword NEQ "all">
AND SSCourse.Topics LIKE '%#URL.keyword#%'
</cfif>
ORDER BY #URL.sort# #URL.sortD#;
</cfquery>

Help? Known bug? Known Limitation? Am I doing something wrong?

[CF_Oracle]

Try insted Courses.Eff_Date_End = NULL...
Courses.Eff_Date_End IS NULL OR Courses.Eff_Date_End = ''...
Also check format of getdate(). Maybe it's required DateFormat around (see documentation).

[paross1]

I am assuming that your query that works in Enterprise Manager (or wherever you
are doing what you can your direct query), should return the same rows when
executed in CF. Once you fix the IS NULL problem, you might try using your
"direct" query (with the parameters as constants rather than variables)
directly in your CF template, then replace the constants one at a time with its
corresponding variable until it fails to return rows. (Also, you really don?t
need to include the OUTER in your LEFT OUTER joins, as LEFT JOIN works just
fine.)

Phil

[jchapman16]

You are an Oracle indeed. :) The change from "= NULLL" to "IS NULL" did the trick. Thank you!
Thanks to paross as well; good troubleshooting advice, and you're right about the extra "outer".

[mxstu]

jchapman16,

Glad your problem is solved. CF_Oracle's made a good point. You should
always use IS NULL to detect null values, even when querying your database
directly.

ORDER BY #URL.sort# #URL.sortD#;
Be careful with using url or form parameters directly in a query. It opens up
the possibility of sql injection, allowing a user to tack on a sql command
like DROP TABLE..., etc.