Hello, everyone.
Got a weird issue (IMHO)..
I have a medium sized SELECT query that takes four dynamic values - three for the WHERE clause, one for the ORDER BY.
If I run the query within a CFC method without using CFQUERYPARAM, it runs exactly as expected.
If I use CFQUERYPARAM on any/all of the WHERE clause, it ignores the ORDER BY and just returns data as it is.
What is happening???
Thanks,
^_^
The query is about 12 lines long and goes all the way across the screen.
Basically
SELECT a.colA, a.colB, a.colC, b.colC, b.colD, c.colA, c.colC, c.colD, d.colA, d.colF
FROM tableA a LEFT OUTER JOIN tableB b on b.id = a.id
LEFT OUTER JOIN tableC c on c.id = a.id
LEFT OUTER JOIN tableD d on d.tid = c.tid
LEFT OUTER JOIN tableE e on e.org = b.org
WHERE d.tid = [training id] AND c.auth = [authorization id] AND b.year = [4 digit year]
ORDER BY #by#
by can be last name, date completed, or organization.
If this:
SELECT a.colA, a.colB, a.colC, b.colC, b.colD, c.colA, c.colC, c.colD, d.colA, d.colF
FROM tableA a LEFT OUTER JOIN tableB b on b.id = a.id
LEFT OUTER JOIN tableC c on c.id = a.id
LEFT OUTER JOIN tableD d on d.tid = c.tid
LEFT OUTER JOIN tableE e on e.org = b.org
WHERE d.tid = [training id] AND c.auth = [authorization id] AND b.year = [4 digit year]
ORDER BY #by#
resembles your actual query, you have a logic problem. You are specifying left joins to tables b,c, and d and then using those tables in your where clause. This changes your outer join to an inner join. You fix it by moving those filters to your from clause, like this:
from tablea a left join tableb b on b.id = a.id and b.year = [4 digit year]
Already have an account? Login
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
AdChoices