SQL error

[khue]

I can't figure out what's wrong with my SQL with the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The identifier that starts with ' FROM course c WHERE c.calendarID = '31' AND c.courseID NOT IN (SELECT CourseID ' is too long. Maximum length is 128.


This is my sql syntax:

...

<CFQUERY NAME=&quot;qryCourse&quot; DBNAME=&quot;#APPLICATION.DSN1#&quot; DBTYPE=&quot;ODBC&quot; DATASOURCE=&quot;#APPLICATION.DSN1#&quot;>
SELECT DISTINCT c.courseID,
c.course_num + ' ' + c.course_name AS cname,
c.course_num
FROM course c
WHERE c.calendarID = '31'
AND c.courseID NOT IN
(SELECT CourseID
FROM trial_section
WHERE courseID = c.courseID
AND trialID = '35')
ORDER BY c.course_num, c.courseID
</CFQUERY>
...

Anyone have a solution to this?

 

[tleish]

Try this:

<CFQUERY NAME=&quot;qryCourse&quot; DBNAME=&quot;#APPLICATION.DSN1#&quot; DBTYPE=&quot;ODBC&quot; DATASOURCE=&quot;#APPLICATION.DSN1#&quot;>
SELECT DISTINCT c.courseID,
c.course_num + ' ' + c.course_name AS cname,
c.course_num
FROM course c
WHERE c.calendarID = 31
AND c.courseID NOT IN
(SELECT CourseID
FROM trial_section
WHERE courseID = c.courseID
AND trialID = 35)
ORDER BY c.course_num, c.courseID
</CFQUERY> - tleish

 

[khue]

tleish,

There is no difference from your code and mine except that yours you have taken out the qoutes. I've tested and it still gives me the same sql errors

 

[khue]

tleish,

I'm sorry that I did not copy the correct syntax of my SQL. Here it is:

...

<!--- find courses without sections --->
<CFQUERY NAME=&quot;qryCourse&quot; DBNAME=&quot;#APPLICATION.DSN1#&quot; DBTYPE=&quot;ODBC&quot; DATASOURCE=&quot;#APPLICATION.DSN1#&quot;>
SELECT DISTINCT c.courseID,
c.course_num + ' ' + c.course_name AS cname,
c.course_num &quot;
FROM course c
WHERE c.calendarID = '#URL.ContextID#'
AND c.courseID NOT IN
(SELECT CourseID
FROM trial_section
WHERE courseID = c.courseID
AND trialID = '#qryTrial.trialID#')
ORDER BY c.course_num, c.courseID
</CFQUERY>
...

 

[tleish]

Why are you using single quotes around numbers? - tleish

 

[khue]

because the sql is in Coldfusion. And if you look at the sql syntax in coldfusion, all supplied coldfusion that is included in the sql must be in single quote.

 

[tleish]

Actually they don't, they only need to be between ##. The only time they should be between quotes is if they are strings, not numbers.

[COLOR=666666]<!--- find courses without sections --->[/color]
<CFQUERY NAME=&quot;qryCourse&quot; DBNAME=&quot;#APPLICATION.DSN1#&quot; DBTYPE=&quot;ODBC&quot; DATASOURCE=&quot;#APPLICATION.DSN1#&quot;>
SELECT DISTINCT c.courseID,
c.course_num + ' ' + c.course_name AS cname,
c.course_num &quot;
FROM course c
WHERE c.calendarID = #URL.ContextID#
AND c.courseID NOT IN
(SELECT CourseID
FROM trial_section
WHERE courseID = c.courseID
AND trialID = #qryTrial.trialID#)
ORDER BY c.course_num, c.courseID
</CFQUERY>

I'm not saying this is your problem, but it would be the first place I'd look. If your db is expecting a number (3) and you put it into quotes ('3'), the db looks at it as a string instead of a number and it can cause problems.
- tleish