Hi All,

I am trying to convert values in rows into columns and then have them read by variables. Then have the variables display values in the graph.
I am using the below query to achieve this.
I was hoping that all oracle regular functions would work. But for some reason I get the below error when I use DECODE.
I am using oracle 11.0.2.3 as database. I got the same error for the 'LISTAGG' function.
Can you please what I am missing here to make it work.
If there is an alternate way to achieve, please suggest.
Thank you!

Error:

 
 (FOC14067) STATEMENT CONTAINS UNKNOWN SCALAR FUNCTION 'DECODE' ON LINE 2
 0 ERROR AT OR NEAR LINE     20  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: TEMPHLD1
 BYPASSING TO END OF COMMAND
 (FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: CIS
 0 ERROR AT OR NEAR LINE     25  IN PROCEDURE ADHOCRQ FOCEXEC *
 (FOC2907)  ERROR WITH DIALOGUE MANAGER COMMAND: -READFILE CIS 

Code

 SQL
SELECT MAX(DECODE(COMPONENT_ID, 12, FOOTNOTE)) FN2 ,
  MAX(DECODE(COMPONENT_ID, 13, FOOTNOTE)) FN3 ,
  MAX(DECODE(COMPONENT_ID, 14, FOOTNOTE)) FN4 ,
  MAX(DECODE(COMPONENT_ID, 15, FOOTNOTE)) FN5 ,
  MAX(DECODE(COMPONENT_ID, 16, FOOTNOTE)) FN6 ,
  MAX(DECODE(COMPONENT_ID, 17, FOOTNOTE)) FN7 ,
  MAX(DECODE(COMPONENT_ID, 18, FOOTNOTE)) FN8
FROM
  ( SELECT DISTINCT COMPONENT_ID,
    FOOTNOTE
  FROM MDARTADMIN.VW_RPT_MDART_COMP_DATA
  WHERE COMPONENT_ID IN (12, 13, 14, 15, 16, 17, 18)
  );
TABLE
ON TABLE HOLD AS TEMPHLD1
END
-RUN

TABLE FILE TEMPHLD1
SUM FN2 FN3 FN4 FN5 FN6 FN7 FN8
ON TABLE HOLD AS CIS
END
-RUN
-READFILE CIS

-DEFAULTH &ABC=&FN3.EVAL;
-DEFAULTH &XYZ=&FN4.EVAL;

DEFINE FILE COMP_DATA 

This message has been edited. Last edited by: Tamra,

I ran this SQL and it worked. Did you try running your SQL in q SQL client?

SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION myconn
-RUN

SQL
SELECT
  MAX(DECODE(OBJECT_ID,  368, OBJECT_NAME)) FN2
, MAX(DECODE(OBJECT_ID, 1233, OBJECT_NAME)) FN3
, MAX(DECODE(OBJECT_ID, 1266, OBJECT_NAME)) FN4
, MAX(DECODE(OBJECT_ID, 1271, OBJECT_NAME)) FN5
, MAX(DECODE(OBJECT_ID, 1292, OBJECT_NAME)) FN6
FROM (
    SELECT DISTINCT OBJECT_NAME, OBJECT_ID  FROM ALL_OBJECTS WHERE OBJECT_ID IN (368, 1233, 1266, 1271, 1292)
);
END
-RUN

Hi Francis,

Thank you for your quick response!

So in my attempt I did not add "SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION".

Other similar SQL queries (without Decode) that I had used in other graphs, it worked fine without the "SET" statement.
I added it this time it worked fine. I guess to use "SQL functions", SET command is must.

Thank you for your help!

When using SQL to access DBMS tables (like Oracle tables), you MUST specify the SQL engine.

This is done in one of two ways:

SET SQLENGINE = SQLORA
SQL SET DEFAULT_CONNECTION myconn

SQL
SELECT ...

or

ENGINE SQLORA SET DEFAULT_CONNECTION myconn

SQL SQLORA
SELECT ...

In the first method, you specify the engine once, then use SQL commands.
In the second method, you specify the engine each time you use a SQL command.

If you do not specify the SQL engine, you can still use SQL, but WebFOCUS will use the metadata (.mas and .acx). You will get an error if the metadata does not exist.

Thank you Francis!