Adrian Wagner wrote: > 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 > 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>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting, up-to-date ColdFusion information by your peers, delivered to your door four times a year. http://www.fusionauthority.com/quarterly Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:255329 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
