[thelist] OT -- toronto holiday get-together

Joshua OIson joshua at alphashop.net
Mon Dec 11 13:24:06 CST 2000


Nice tip Rudy.  I do soemthing similar on updates too.

<tip type="conditional sql - updates">
If you are generating conditional update clauses, you can also avoid many
id/else tesing clauses by starting and ending your SET clauses with
something that does nothing, ie, id=id

So a query may look like this:

UPDATE foo
SET id=id,
  <cfif Val(set_name)>name = '#name#',</cfif>
  <cfif Val(set_address)>address = '#address#',</cfif>
  id=id
WHERE id=#id#

Since the first and "id=id" don't really do anything, there's no harm,
except that you don't have to worry about those pesky commas in the sql
statement.

</tip>

-joshua

----- Original Message -----
From: "rudy" <Rudy_Limeback at maritimelife.ca>
To: <thelist at lists.evolt.org>
Sent: Monday, December 11, 2000 10:06 AM
Subject: [thelist] OT -- toronto holiday get-together


> toronto evolt holiday get-together wednesday night 8 p.m. --
> for details, see below
>
>
> <tip type="conditional sql">
> when generating exclusive WHERE conditions, avoid needless if/else testing
> by starting the WHERE clause with something guaranteed to be true --
>
>      select foo from yourTable
>         where 1=1
>
> if no additional conditions are needed, i.e. selected by the user (imagine
> this query being dynamically tailored by user choices on a search form),
> then the above query returns the entire table
>
> but when additional exclusive conditions are selected, you don't have to
> bother testing each one (to see if it's the first one which would've
> otherwise had to start with the WHERE keyword)
>
> just generate each one with an AND keyword, tacking it on to the query so
> far --
>
>      select foo from yourTable
>         where 1=1
>    <cfif userField1Checked>
>            and dbColumn1 = '<cfoutput>#userField1Value#</cfoutput>'
>     </cfif>
>    <cfif userField2Checked>
>            and dbColumn2 = '<cfoutput>#userField2Value#</cfoutput>'
>     </cfif>
>
> of course, if you are doing inclusive selection, you'll be generating OR
> conditions, in which case you want the WHERE clause to start with
> something NOT true
>
>      select foo from yourTable
>         where 1=0
>    <cfif userFieldChecked>
>            or dbColumn = '<cfoutput>#userFieldValue#</cfoutput>'
>     </cfif>
>
> </tip>






More information about the thelist mailing list