proplem with dynamic sql statement

[jgroove]

I have two close to identical sql statements from two different templates. Only of one of them works, but I can't fiqure out why one is getting an "ORA-00933: SQL command not properly ended " error here are two sql statements

working:

<cfparam name=&quot;form.DISTRIBUTORID&quot; default=&quot;All&quot;>
<cfquery name=&quot;qCurriculumKits&quot;>
	select * from CurriculumKits 
	<cfif not(form.DISTRIBUTORID eq &quot;All&quot;)>
		<cfif IsDefined(&quot;url.DISTRIBUTORID&quot;)>
			where DISTRIBUTORID='#url.DISTRIBUTORID#'
		</cfif>
		<cfif IsDefined(&quot;form.DISTRIBUTORID&quot;)>
			where DISTRIBUTORID='#form.DISTRIBUTORID#'
		</cfif>
	</cfif>
</cfquery>

not working:

<cfparam name=&quot;form.ITVLocation&quot; default=&quot;All&quot;>
<cfquery name=&quot;qITVSessions&quot;>
	select * from ITVSessions 
	<cfif not(form.ITVLocation eq &quot;All&quot;)>
		<cfif IsDefined(&quot;form.ITVLocation&quot;)>
			where ITVLOCATION='#form.ITVLocation#'
		</cfif>
		<cfif IsDefined(&quot;url.ITVLocation&quot;)>
			where ITVLOCATION='#url.ITVLocation#'
		</cfif>
	</cfif>
</cfquery>

Anyone have any ideas as why the second one might not work? Maybe I have been trying to fix it for so long, I am missing the obvious.

 

[strantheman]

hi jgroove

Your CFIF statements could result in two where clauses ending up in your query.

<cfif not(form.ITVLocation eq &quot;All&quot>
<cfif IsDefined(&quot;form.ITVLocation&quot>
where ITVLOCATION='#form.ITVLocation#'
</cfif>
<cfif IsDefined(&quot;url.ITVLocation&quot>
where ITVLOCATION='#url.ITVLocation#'
</cfif>
</cfif>

Since I don't really know the scope of your project here, ill just give you a simple solution. It still may result in both of the nested CFIFs resulting in true, but at least there won't be a SQL error. Just start with a where clause that can't fail, and then you can just add ANDs as much as you want.

<cfparam name=&quot;form.ITVLocation&quot; default=&quot;All&quot;>
<cfquery name=&quot;qITVSessions&quot;>
select * from ITVSessions
where 0 = 0
<cfif not(form.ITVLocation eq &quot;All&quot>
<cfif IsDefined(&quot;form.ITVLocation&quot>
and ITVLOCATION='#form.ITVLocation#'
</cfif>
<cfif IsDefined(&quot;url.ITVLocation&quot>
and ITVLOCATION='#url.ITVLocation#'
</cfif>
</cfif>
</cfquery>

 

[jgroove]

stantheman,

thanks for your input, although it didn't still quite work. But you did notice an inherent flaw in my code, although I don't think it would have ever happen having the url and form value being defined. Afer walking away from the code for a hour or two, I found an immediate fix when I got back.

<cfquery name=&quot;qITVClasses&quot;  debug>
	select * from TVSessions 

        <cfif IsDefined(&quot;form.ITVLocation&quot;) and form.ITVLocation neq &quot;All&quot;>
            where ITVLOCATION='#form.ITVLocation#'
        <cfelseif IsDefined(&quot;url.ITVLocation&quot;)>
            where ITVLOCATION='#url.ITVLocation#'
		</cfif>
</cfquery>

Although I am still puzzled as why the other query works from original post. I actually built eight templates from the same setup and this is the only page that doesn't work. Oh well. Thank you for helping out.

jgroove