Professional Web Applications Themes

Complex join = no results (for a query that shouldreturn results) - Coldfusion Database Access

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 ...

  1. #1

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

    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?

    jchapman16 Guest

  2. #2

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

    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 doentation).
    CF_Oracle Guest

  3. #3

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

    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

    paross1 Guest

  4. #4

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

    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".
    jchapman16 Guest

  5. #5

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

    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.




    mxstu Guest

Similar Threads

  1. formatting find results in results window or in savedfile
    By InQuistve in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: January 23rd, 03:19 PM
  2. Query results don't display properly in results table.IGNORE PREVIOUS
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: March 24th, 07:28 PM
  3. Query results don't display properly in results table.
    By JoyRose in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 24th, 07:17 PM
  4. Presenting complex query results through datagrid
    By Bijoy Naick in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: February 24th, 08:19 PM
  5. query results
    By Campbell, John (CONS FIN , MCS) in forum Informix
    Replies: 9
    Last Post: November 18th, 08:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139