Re: Correct Syntax for this piece of SQL ??

[Ian Vaughan]

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