Re: Correct Syntax for this piece of SQL ??

[Ian Vaughan]

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
Signup for the Fusion Authority news alert and keep up with the latest news in 
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm

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