QofQ CASE Statement

[JohnandSwifty]

Hi,

I need to replicate the CASE statement below in a QofQ. Any ideas?

CASE
							WHEN quotationProduct.sequence > 0 AND quotationProduct.sequence <= 5 THEN '1-5'
							WHEN quotationProduct.sequence > 5 AND quotationProduct.sequence <= 10 THEN '6-10'
							WHEN quotationProduct.sequence > 10 AND quotationProduct.sequence <= 15 THEN '11-15'
							WHEN quotationProduct.sequence > 15 AND quotationProduct.sequence <= 20 THEN '16-20'
							WHEN quotationProduct.sequence > 20 AND quotationProduct.sequence <= 25 THEN '21-25'
							WHEN quotationProduct.sequence > 25 AND quotationProduct.sequence <= 30 THEN '26-30'
							WHEN quotationProduct.sequence > 30 AND quotationProduct.sequence <= 35 THEN '31-35'
							WHEN quotationProduct.sequence > 35 AND quotationProduct.sequence <= 40 THEN '36-40'
							WHEN quotationProduct.sequence > 40 AND quotationProduct.sequence <= 45 THEN '41-45'
							WHEN quotationProduct.sequence > 45 AND quotationProduct.sequence <= 50 THEN '46-50'
							WHEN quotationProduct.sequence > 50 AND quotationProduct.sequence <= 55 THEN '51-55'
							WHEN quotationProduct.sequence > 55 AND quotationProduct.sequence <= 60 THEN '56-60'
							WHEN quotationProduct.sequence > 60 AND quotationProduct.sequence <= 65 THEN '61-65'
							WHEN quotationProduct.sequence > 65 AND quotationProduct.sequence <= 70 THEN '66-70'
							WHEN quotationProduct.sequence > 70 AND quotationProduct.sequence <= 75 THEN '71-75'
							ELSE '75+'
						END AS sequenceGroup

 

[r937]

i would use CFIFs

r937.com | rudy.ca

 

[JohnandSwifty]

We use the same page for output, but have either a DB or a QofQ source - so the query needs to return the same results. At the moment we create the query, then break it up to create groupings the re-create the query which is a bit sloppy. It seems that CASE is not supported in QofQ so I was looking for some alternative method to perform the same function but still within the Q0fQ...

 

[r937]

if your objective is to have code which works whether the source is a query or a QofQ, then you need to find code that works in both, and if CASE doesn't, then don't use CASE in either

make sense?

i didn't understand this part -- "At the moment we create the query, then break it up to create groupings the re-create the query which is a bit sloppy."

r937.com | rudy.ca

 

[JohnandSwifty]

For example - We have a getDB component function and a getVAR component function. At the moment regardless of whether we are getting data from a query in a var or from the DB, we can get the same fields, same data in the same format from either. In one of our functions however we create groups (in the getDB) which utilise CASE. In the getVAR, for it to continue being able to serve up consistent data with the getDB we also need to perform a CASE. At the moment we get round this by looping the query stored in a var and creating a new query using queryNew, querySetCell etc and use CF conditional to create the columns that way. I just wanted to find out if we were being dumb and there was another way within a QofQ to create the same functionality as a CASE - looks like there isn't...?

 

[r937]

same functionality as CASE = series of CFIFs

r937.com | rudy.ca

 

[FALCONSEYE]

r937 already mentioned: You can also query only the "quotationProduct.sequence" variable in your QofQ and then:

<cfif quotationProduct.sequence GT AND quotationProduct.sequence LTE 5>
<cfset myRange = '1-5'>
<cfelseif ...>
...
<cfelse>
...
</cfif>


hope it helps.

http://www.fuzzysiberians.com

 

[JohnandSwifty]

I dont think I have been clear enough about the use of the query functions. All parts of the application use one function to query each record type from either source (sort of a query builder/ single point of interrogation). So the get function needs the ability to return everything, every time from either a QofQ or a DB query. As i mentioned we already use cfifs to rebuild the query with any additional fields that cant be constructed with QofQ. I was looking for a method to keep it all in the cfquery but it seems there isnt one... no problem - thanks for all your help.

 

[FALCONSEYE]

I see. How about something like this:
Might need to be debugged but it will give you an idea...

<cfcomponent>

<cfset init()>

<cffunction name="init" access="public" output="no" returntype="string">
<cfargument name="sid" type="string" required="no" default="#SESSION.CFID##SESSION.CFToken#">

<cfset VARIABLES.sessionId = ARGUMENTS.sid>
<cfreturn VARIABLES.sessionID>
</cffunction>

<cffunction name="getAll" access="public" returntype="query">
<cfargument name="sequenceStart" type="numeric" required="false">
<cfargument name="sequenceStop" type="numeric" required="false">

<cfif IsDefined('ARGUMENTS.sequenceStart')>
<cfset beginAt = ARGUMENTS.sequenceStart + 1>
</cfif>

<cfquery name="selectStuff" datasource="#REQUEST.datasource#">
SELECT ...
<cfif IsDefined('ARGUMENTS.sequenceStart') AND IsDefined('sequenceStop')>
(CASE WHEN quotationProduct.sequence > #ARGUMENTS.sequenceStart# AND
quotationProduct.sequence <= #ARGUMENTS.sequenceStop#
THEN '#beginAt#-#ARGUMENTS.sequenceStop#'
END) as tResult
</cfif>
FROM ...
WHERE ...
<cfif IsDefined('ARGUMENTS.sequenceStart') AND IsDefined('sequenceStop')>
AND quotationProduct.sequence BETWEEN #ARGUMENTS.sequenceStart# AND #ARGUMENTS.sequenceStop#
</cfif>
</cfquery>

<cfreturn selectStuff>
</cffunction>

<cffunction name="getSpecificRecs" access="public" returntype="query">
<cfargument name="seqStart" type="numeric" required="true">
<cfargument name="seqStop" type="numeric" required="true">

<cfscript>
tR = getAll(seqStart, seqStop);
</cfscript>

<cfreturn tR>
</cffunction>
</cfcomponent>

http://www.fuzzysiberians.com

 

[JohnandSwifty]

Ok, but how does that help me with a Query Var rather than a query from DB? Im not looking to get a set of results by the sequence group, just return the sequence group against the record - most common use is grouping on recordsets.

So the data returned would be:

ID REF SEQUENCE SEQUENCEGROUP
01 blah01 1 1-5
09 blah09 9 6-10

Where the data held in the table or query would only be the first three columns - so sequnce group has to be generated each time...

 

[cfSearching]

As i mentioned we already use cfifs to rebuild the query with any additional fields that cant be constructed with QofQ. I was looking for a method to keep it all in the cfquery but it seems there isnt one

The only other option I could think of is to generate a second query containing the sequence groups, either manually or from a database query. Then join the two queries together on the Sequence value, using a QoQ.

However there may not be much value to it .. unless you were storing the sequence groups in a database table. The benefit there being it would remove some of the hard coding.





----------------------------------

http://cfsearching.blogspot.com/

 

[philhege]

JohnAndSwifty answered his own question in this thread, I believe.

Phil H.
Some Bank
-----------
Time's fun when you're having flies.

 

[JohnandSwifty]

cfSearching: Thanks for your comment, on reflection, as with the get Data functions we use, we do something similar for inserts and updates. We realised that it would just be so much easier if we keep the sequenceGroup in the table (so write it when inserting and updating). The benefit of having a single insert function and a single update is that we only added code to two files, and the sequence groups are working perfectly now without and stupid rebuilding of queries.

Thanks for all your suggestions.

PS philhedge: I'm interested to know how you feel my other post relates to the question here?

 

[philhege]

Simply the syntax. Don't mind me; I was tired.

I also wonder why you have to use a QofQ. Can't you develop a stored procedure to do the same work?

Phil H.
Some Bank
-----------
Time's fun when you're having flies.