1 Reply Latest reply on Jan 30, 2008 7:46 AM by paross1

    Problem using DECODE() function with a Query of Queries

    molaro Level 1
      I posted on my blog 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:

      quote:


      <!--- 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 ========== --->


      So running this returned the following error:

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


      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!