CFIF statement in query

Hi all, Here's the deal. I need to order a query by a particular sequence that is not in the database, nor can it be implemented in the database (the data is imported daily from a third party which does not provide this sequence). So, the solution my co-worker came up with is to actually assign the sequence (value) into a virual (computed) column. All good. My problem now is to dynamically assign that sequence value to the computed column depending on the value of a physical column. What I tried (simplisticly, just for testing) is: <cfquery datasource="computedColumnTest" name="testquery"> SELECT ucDescription, ucTitle, <cfif ucDescription contains "Graduate Certificate">   1 <cfelse>   2 </cfif> AS seq FROM testtable ORDER BY ucDescription </cfquery> The error message I get from this is "Element UCDESCRIPTION is undefined in QUERY". Is there an easy way to get around this? Thanks for your help. Adrian

Looks like you are confusing CF processing and DBMS processing. You'll want to do that if test in the DBMS itself, and CFML does not execute in the DBMS. The exact syntax for an if or case statement can vary by DBMS, so hard to give you exact syntax, but take a look at SQL if and case statements for your own database. Make sure the SELECT statement itself returns the computed value properly using pure SQL (no CFML), then you'll be able to do what you want. --- Ben Hi all, Here's the deal. I need to order a query by a particular sequence that is not in the database, nor can it be implemented in the database (the data is imported daily from a third party which does not provide this sequence). So, the solution my co-worker came up with is to actually assign the sequence (value) into a virual (computed) column. All good. My problem now is to dynamically assign that sequence value to the computed column depending on the value of a physical column. What I tried (simplisticly, just for testing) is: <cfquery datasource="computedColumnTest" name="testquery"> SELECT ucDescription, ucTitle, <cfif ucDescription contains "Graduate Certificate">   1 <cfelse>   2 </cfif> AS seq FROM testtable ORDER BY ucDescription </cfquery> The error message I get from this is "Element UCDESCRIPTION is undefined in QUERY". Is there an easy way to get around this? Thanks for your help. Adrian

Adrian Wagner wrote: ----- Excess quoted text cut - see Original Post for more ----- Use a CASE statement in your SQL... <cfquery datasource="computedColumnTest" name="testquery"> SELECT ucDescription, ucTitle, CASE WHEN ucDescription = 'Graduate Certificate' THEN 1 ELSE 2 END AS seq FROM testtable ORDER BY seq </cfquery>

He wants it when the ucDescription contains that value and is not just equal to it or was that a typo in his CFIF?  The case when then method may not work depending on what DB he is using and the version of it. ----- Excess quoted text cut - see Original Post for more ----- -- Aaron Rouse http://www.happyhacker.com/

Another way to do the same thing if you dont want to put a IF statement in the SQL (which is the better way to do it but just suppose you dont want to do that)  you coudl do something like this: <cfif ucDescription contains 'Graduate Certificate' >   <cfquery datasource="computedColumnTest" name="testquery">       SELECT ucDescription, ucTitle, 1 AS seq FROM testtable      ORDER BY ucDescription      </cfquery> <cfelse>   <cfquery datasource="computedColumnTest" name="testquery">       SELECT ucDescription, ucTitle, 2 AS seq FROM testtable      ORDER BY ucDescription      </cfquery> </cfif> This way all the conditional stuff is done in the coldfusion.   If there were more than two possibilities, you could CFSWITCH instead of the CFIF instead. This would be the way to do it if you're sure of your coldfusion but not too sure about how to do conditional processing in SQL.  But it has the disadvantage of requiring you to maintain two queries in the future rather than one.   (opportunity for you to forget to change one of them if your database changes) Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month ----- Excess quoted text cut - see Original Post for more -----

Aar ----- Excess quoted text cut - see Original Post for more ----- CASE does have pretty broad support, from what I know(and I believe it is part of a SQL spec).  The versions of MySQL, MSSQL and Postgres that I have used support it...not sure about Oracle.  Access does not. The WHEN comparison can change to a LIKE or PATINDEX if that is what he is looking for.

I know it has broad support but just felt that disclaimer should be put in since never know what someone is using on here.  I'd never put it past a single person to be using Access for example but heck they could even be using an old FoxPro system just never do know.  My primary point though was the leaving off the contains and using equals by accident just in case they tried your example to the tooth so to speak. ----- Excess quoted text cut - see Original Post for more -----

I would recommend Jim's suggestion, with a slight modification. Since he wanted ucDescription contains "Graduate Certificate", I think in SQL Server that would be "LIKE" Anyway try this. <cfquery datasource="computedColumnTest" name="testquery"> SELECT ucDescription, ucTitle, CASE WHEN ucDescription LIKE '%Graduate Certificate%' THEN 1 ELSE 2 END AS seq FROM testtable ORDER BY seq </cfquery> Good luck, Ali ----- Excess quoted text cut - see Original Post for more -----

No typo. We're using an SQL Server 8. >>> aaron.rouse@gmail.com 4/10/2006 12:28 pm >>> He wants it when the ucDescription contains that value and is not just equal to it or was that a typo in his CFIF?  The case when then method may not work depending on what DB he is using and the version of it. ----- Excess quoted text cut - see Original Post for more ----- seq > FROM testtable > ORDER BY seq > </cfquery> > -- Aaron Rouse http://www.happyhacker.com/

Hi, Thanks for you patience. ;) Ben was right about my confusion (I knew it didn't feel correct but...you know). Anyhow, I've been taking up Jim's suggestion and applied the correct MSSQL (we're using version 8) syntax. It runs now, but as mentioned before, because of then WHEN statement, the wrong sequence numbers are assigned now. Just got to figure out the correct syntax for a 'LIKE' statement (like doesn't seem to work on MSSQL). Here's what I've got so far:    SELECT ucTitle,objectid,ucNumber AS indexno,ucDescription,locked,lastupdatedby,datetimecreated,datetimelastupdated,bActive,ucNumber,      CASE ucDescription     WHEN 'Graduate Certificate' THEN '1'     WHEN 'Graduate Diploma' THEN '2'     WHEN 'Postgraduate Diploma' THEN '3'     WHEN 'Master' THEN '4'     WHEN 'Master of Commerce' THEN '5'     ELSE '6'      END    AS seq    FROM cbsUnitsCourses    WHERE #preserveSingleQuotes(arguments.searchString)#    AND bActive IN (#preserveSingleQuotes(arguments.bActive)#)    and status = '#arguments.status#'    <cfif len(arguments.schoolid)>     AND ucSchool = '#arguments.schoolid#'    </cfif>    <cfif len(arguments.StudyAreaID)>     AND studyarea = '#arguments.StudyAreaID#'    </cfif>    ORDER BY seq, ucTitle ASC Thanks for pointing me into the right direction. Adrian Wagner Web Analyst Programmer Curtin Business School d: +61 (0) 8 9266 4220 m: +61 (0) 40 110 3252 e: Adrian.Wagner@cbs.curtin.edu.au