Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
sql case expression
 Login/Join
 
<Kee Zealy>
posted
This code does not work (Note the case expression):

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE FILE SQLOUT
ON TABLE HOLD AS ORFKEYS
PRINT *
END

The error is as follows:
0 ERROR AT OR NEAR LINE 30 IN PROCEDURE FOCSQL01FOCEXEC *
(FOC012) THE WORD 'FILE' OR THE FILENAME APPEARS TWICE
BYPASSING TO END OF COMMAND


This code brings back rows in the browser:

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;



This code actually saves the file but does not include the case:

SQL

select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE FILE SQLOUT
ON TABLE HOLD AS ORFKEYS
PRINT *
END

Can anyone shed any light on this? I can pass the query I am sure directly to the database, but I would prefer to use the metadata if possible.
 
Report This Post
Virtuoso
posted Hide Post
Change the first instance to this:

SQL
select count(*) as row_cnt,
prfl_run_key as prfl_run_key,
function_key as function_key,
col1_value as col1_value,
col2_value as col2_value,
col3_value as col3_value,
master_row_flag as master_row_flag,
CASE
WHEN col1_value is null then 'Y'
ELSE 'N' END AS null_flag
From dpr_prfl_oj_fn_verbose
group by
prfl_run_key,
function_key,
col1_value,
col2_value,
col3_value,
master_row_flag;
TABLE
ON TABLE HOLD AS ORFKEYS
END

TABLE FILE ORFKEYS
PRINT *
END

You shouldn't (or can't, not sure here) do a ON TABLE HOLD before a print. Also, the FILE SQLOUT is only used when executing sql stored procedures.
 
Posts: 1102 | Location: Toronto, Ontario | Registered: May 26, 2004Report This Post
Guru
posted Hide Post
As far as I know all SQL passthroughs use SQLOUT, stored procedures or not.
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
<Kee Zealy>
posted
This is not a sql pass through query. It is using the web focus sql engine to execute the code through the metadata that exists. If it were pass through, it would explicitly use the following syntax:

Note the key word oracle and the reference to the database instance INFADEV:

SQL SQLORA SET SERVER INFADEV
SQL SQLORA
SELECT
CASE WHEN SOURCE_FIELD_NUMBER <8 THEN 7
ELSE
SOURCE_FIELD_NUMBER END AS MAXID,
T2.PRFL_RUN_KEY,
t3.prfl_ver_key,
t3.function_key,
T3.SOURCE_NAME1,
T3.COLNAME1
FROM infarep.rep_all_source_flds T1,PROFILER.DPR_PRFL_RUN_DTLS T2, PROFILER.DPR_PRFL_COL_FN T3
WHERE T1.SUBJECT_AREA=T2.PRFL_FOLDER_NAME AND
T2.PRFL_VER_KEY=T3.PRFL_VER_KEY AND
T1.source_name=T3.source_NAME1 AND
T1.source_field_NAME =T3.COLNAME1 AND
T2.PRFL_RUN_KEY=&prf_rnkey AND
T3.FUNCTION_KEY=&prf_fnkey;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE AS MAXCOL
END
 
Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders