Re: Correct Syntax for this piece of SQL ??

Dina Hess Sat, 01 Mar 2003 11:06:19 -0800

Ian,

Replace WHERE 0 = 1 in your current query with either WHERE 0 = 0 or WHERE 1
= 1.


Why? This is just a "dummy" clause that's used in a dynamic SQL statement to
let's you move past the WHERE and on to the dynamic AND/OR statements that
are based on your conditions. But this dummy WHERE clause must be true, or
processing will stop right there. Obviously, WHERE 0 = 1 is *not* true. Fix
that and you should be OK.

~Dina

----- Original Message -----
From: "Ian Vaughan" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, February 26, 2003 9:17 AM
Subject: Re: Correct Syntax for this piece of SQL ??


> Pascal
>
> I removed the cached feature so the query looks like what I have below.
> However If I search for an entry in the orgname field it displays the
result
> as it should.
>
> If I try to search in the other two fields it returns no results ???
>
> <CFQUERY datasource="liv8" name="funding">
> SELECT *
> FROM funding
> WHERE 0=1
> <cfif Len(Trim(form.orgname))>
>   OR UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> value="%#form.orgname#%">)
> </cfif>
> <cfif Len(Trim(form.funding))>
>   OR UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> value="%#form.funding#%">)
> </cfif>
> <cfif Len(Trim(form.commapproval))>
>   OR UPPER(commapproval) LIKE UPPER(<cfqueryparam
cfsqltype="CF_SQL_VARCHAR"
> value="%#form.commapproval#%">)
> </cfif>
> </CFQUERY>
>
>
>
>
> ----- Original Message -----
> From: "Pascal Peters" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, February 26, 2003 2:49 PM
> Subject: RE: Correct Syntax for this piece of SQL ??
>
>
> > You cannot use <cfqueryparam> with cached queries. Go back to
> UPPER('%#Trim(form.var)#%')
> >
> > -----Oorspronkelijk bericht-----
> > Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
> > Verzonden: wo 26/02/2003 14:17
> > Aan: CF-Talk
> > CC:
> > Onderwerp: Re: Correct Syntax for this piece of SQL ??
> >
> >
> >
> > Pascal
> >
> > Here is my query
> >
> > <CFQUERY datasource="liv8" name="funding"
> > cachedwithin="#CreateTimeSpan(0,6,0,0)#" blockfactor="100">
> > SELECT *
> > FROM funding
> > WHERE 0=1
> > <cfif Len(Trim(form.orgname))>
> >   OR UPPER(orgname) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> > value="%#form.orgname#%">)
> > </cfif>
> > <cfif Len(Trim(form.funding))>
> >   OR UPPER(funding) LIKE UPPER(<cfqueryparam cfsqltype="CF_SQL_VARCHAR"
> > value="%#form.funding#%">)
> > </cfif>
> > <cfif Len(Trim(form.commapproval))>
> >   OR UPPER(commapproval) LIKE UPPER(<cfqueryparam
> cfsqltype="CF_SQL_VARCHAR"
> > value="%#form.commapproval#%">)
> > </cfif>
> > </CFQUERY>
> >
> > <CFIF funding.RecordCount is 0>
> >                 <span class="black10"><p><b>No files found for specified
> > criteria</b></p>
> > <!--- ... else at least one file found --->
> >    <CFELSE>
> >             <cfif sgn(Evaluate(funding.RecordCount - Form.MaxRows -
> > Form.StartRow)) -1>
> >             <p>
> >             <CFOUTPUT>
> >
> >              <small><span class="black10">Viewing #Form.StartRow# to
> > #funding.RecordCount# of <b>#funding.RecordCount#</b> records
> > found.</font></small></span>
> >
> >
> >             </cfoutput>
> >
> >
> >             </cfif>
> >
> > <div align="left">
> > <table width="761" border="0">
> > <tbody>
> > <tr>
> > <th
> >
>
background="http://intranet.neath-porttalbot.gov.uk/images/table_height.gif";
> > align="left"><span class="black10">Organization Name</th>
> > <th
> >
>
background="http://intranet.neath-porttalbot.gov.uk/images/table_height.gif";
> > align="left"><span class="black10">Funding</th>
> > <th
> >
>
background="http://intranet.neath-porttalbot.gov.uk/images/table_height.gif";
> > align="left"><span class="black10">Committe Approval</th>
> >
> > </tr>
> > <cfoutput query="funding" startrow="#StartRow#"
maxrows="#Form.MaxRows#">
> > <tr BGCOLOR=###IIF(funding.currentrow MOD 2, DE ('e7efef'), DE
> ('f7f7de'))#>
> > <!--- <td align="left" valign="top"
> >
>
background="http://intranet.neath-porttalbot.gov.uk/images/tablebg1.gif";><sp
> > an class="black10">#Evaluate(CurrentRow)#</td> --->
> > <td align="left" valign="top" ><span class="black10">#orgname#</td>
> > <td align="left" valign="top"><span class="black10">#funding#</td>
> > <td align="left" valign="top"> <span class="black10">#commapproval#</td>
> >
> > </tr>
> > </cfoutput></tbody></table>
> > ----- Original Message -----
> > From: "Pascal Peters" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Wednesday, February 26, 2003 11:34 AM
> > Subject: RE: Correct Syntax for this piece of SQL ??
> >
> >
> > > This should work on ORACLE8/CF4.5.1 (I'm developping for that platform
> > right now)
> > > I can't make much of your error. Can you post your entire cfquery.
> > >
> > > CFQUERYPARAM is used for parameterized sql. It is usually good for
> > performence and it helps to avoid sql hacks.
> > > cfsqltype describes the datatype of your column (in this case a
> VARCHAR2).
> > You can check out the help or devnet
> > (http://www.macromedia.com/desdev/articles/ben_forta_faster.html)
> > >
> > > -----Oorspronkelijk bericht-----
> > > Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
> > > Verzonden: wo 26/02/2003 11:55
> > > Aan: CF-Talk
> > > CC:
> > > Onderwerp: Re: Correct Syntax for this piece of SQL ??
> > >
> > >
> > >
> > > Pascal
> > >
> > > Thanks for your solution but it does not work, I am using CF 4.5 and
> > Oracle
> > > 8.  I am getting the following error when using your code, is it not
> > > compatible with 4.5 ?
> > >
> > > and what is the need for
> > > <cfqueryparam cfsqltype="CF_SQL_VARCHAR" ??
> > >
> > > Error Occurred While Processing Request
> > >       Error Diagnostic Information
> > >       CFQuery
> > >
> > >
> > >       The error occurred while processing an element with a general
> > > identifier of (CFQUERY), occupying document position (61:1) to (62:59
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ----- Original Message -----
> > > From: "Pascal Peters" <[EMAIL PROTECTED]>
> > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > Sent: Wednesday, February 26, 2003 10:12 AM
> > > Subject: RE: Correct Syntax for this piece of SQL ??
> > >
> > >
> > > > If one of your form variables is empty, it will always return all
the
> > > records because you match '%%' (which means anything). If you want to
> > match
> > > one of the criteria you entered, this should work:
> > > >
> > > > SELECT *
> > > > FROM funding
> > > > WHERE 0=1
> > > > <cfif Len(Trim(form.orgname))>
> > > >   OR UPPER(orgname) LIKE UPPER(<cfqueryparam
> cfsqltype="CF_SQL_VARCHAR"
> > > value="%#form.orgname#%">)
> > > > </cfif>
> > > > <cfif Len(Trim(form.funding))>
> > > >   OR UPPER(funding) LIKE UPPER(<cfqueryparam
> cfsqltype="CF_SQL_VARCHAR"
> > > value="%#form.funding#%">)
> > > > </cfif>
> > > > <cfif Len(Trim(form.commapproval))>
> > > >   OR UPPER(commapproval) LIKE UPPER(<cfqueryparam
> > > cfsqltype="CF_SQL_VARCHAR" value="%#form.commapproval#%">)
> > > > </cfif>
> > > >
> > > > This will have no result if you don't enter a value at all. If you
> want
> > to
> > > match all if you don't provide any value, you can add a cfif around
the
> > > entire where clause
> > > >
> > > > <cfif Len(Trim(form.orgname&form.funding&form.commapproval))>
> > > >
> > > > -----Oorspronkelijk bericht-----
> > > > Van: Ian Vaughan [mailto:[EMAIL PROTECTED]
> > > > Verzonden: wo 26/02/2003 10:20
> > > > Aan: CF-Talk
> > > > CC:
> > > > Onderwerp: Re: Correct Syntax for this piece of SQL ??
> > > >
> > > >
> > > >
> > > > Hi
> > > >
> > > > I have just tried the following query
> > > >
> > > > select * from
> > > > funding
> > > > Where ( orgname LIKE '%#Form.orgname#%' )
> > > > OR    ( funding LIKE '%#Form.funding#%' )
> > > > OR    ( commapproval LIKE '%#Form.commapproval#%' )
> > > > ORDER BY recordid
> > > >
> > > > however whatever I type in the fields it returns all results ???
> > > >
> > > > All 3 criteria do no have to be met,
> > > >
> > > > Any ideas why the above sql is returning all results ??
> > > >
> > > > Ian
> > > >
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
> 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: 
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for 
dependable ColdFusion Hosting.

                                Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4

Re: Correct Syntax for this piece of SQL ??

Reply via email to