Webmaster Forum / ColdFusion / Advanced Techniques / January 2008

I
http://molaro.wordpress.com/2008/01/28/using-plsql-functions-with-a-query-of-que
ries/ about an issue I was having trying to use the PL/SQL DECODE() function
with a Coldfusion Query of Queries. This function works fine when you query a
database for information.  However, when you query another query, it seems that
CF doesn't recognize it.  I got errors stating that it found a left parenthesis
where it expected a FROM key word.  Here is a simplified version of what I am
trying to do:

[Q]
<!--- Simulated query; similar to what I was calling from my database --->
<cfscript>
  qOriginal = queryNew("Name,Email,CountryCode", "VarChar,VarChar,VarChar");
  newRow = queryAddRow(qOriginal, 5);

  querySetCell(qOriginal, "Name", "Joe", 1);
  querySetCell(qOriginal, "Email", "a@b.com", 1);
  querySetCell(qOriginal, "CountryCode", "AMER", 1);
 
  querySetCell(qOriginal, "Name", "Sally", 2);
  querySetCell(qOriginal, "Email", "x@y.com", 2);
  querySetCell(qOriginal, "CountryCode", "AMER", 2);
 
  querySetCell(qOriginal, "Name", "Bob", 3);
  querySetCell(qOriginal, "Email", "d@e.com", 3);
  querySetCell(qOriginal, "CountryCode", "ASIA", 3);
 
  querySetCell(qOriginal, "Name", "Mary", 4);
  querySetCell(qOriginal, "Email", "g@c.com", 4);
  querySetCell(qOriginal, "CountryCode", "EURO", 4);
 
  querySetCell(qOriginal, "Name", "John", 5);
  querySetCell(qOriginal, "Email", "w@d.com", 5);
  querySetCell(qOriginal, "CountryCode", "EURO", 5);
</cfscript>

<cfquery name="qCountries" dbtype="query">
SELECT DISTINCT(CountryCode) AS CountryCode,
DECODE(states, "AMER", "North America &amp; Canada", "EURO", "Europe &amp;
Africa", "ASIA", "Japan &amp; Asia","") CountryName
FROM qOriginal
ORDER BY CountryCode
</cfquery>

<cfdump var="#qCountries#">
<!--- ========== END OF CODE ========== --->
[/Q]
So running this returned the following error:

[b]Query Of Queries syntax error.
Encountered "(. Incorrect Select Statement, Expecting a 'FROM', but
encountered '(' instead, A select statement should have a 'FROM' construct.[/b]

Does anybody know why this doesn't work?  Is it just not supported?  Please
note that I have also tried to use the CASE() function instead of DECODE() and
that resulted in basically the same error. For now I an looping over my
distinct query with a switch statement and manually loading a new query with
the data how I want it.  But it would be a lot cleaner and less code to have
the DECODE() to work.  Thx!