I have written a focexec to create a report. The user can select parameters on a launch page to be passed on to the focexec.

Unfortunately I am very new to extracting data via an SQL structured query and using FOC_NONE to include ALL Userids in the output rather than just a single Userid.

As I am passing values from the launch page I declare the variables with –DEFAULTH &DOGID = ‘ ’, (and other variables) before executing stored procedure.

I have great results when I select parameters at the launch page but NO Records when I rely on the FOC_NONE option.
I am posting my code here to see if anyone can provide advice on how to get this right.

-*** TESTING FOC_NONE and _FOC_NULL commands

SET PAGE=NOPAGE, HOLDLIST=PRINTONLY, NODATA = ' ', ASNAMES=ON

-*Get global Variables
-INCLUDE IBFS:/WFC/Repository/DAU_Transcript/Other_Files/prc_ini_global_vars.fex
-RUN

-DEFAULTH &MONTH = 12
-DEFAULTH &YEAR = 2016
-DEFAULTH &DAUID = 'DAU6000000011'
-*
-SET &REPORT_TITLE = 'STUDENT USAGE REPORT';
-SET &FEX_NAME = 'FOCFEXNAME.EVAL';

-SET &MONTH_1 = IF '&MONTH.EVAL' EQ ' ' THEN 'FOC_NONE' ELSE '&MONTH.EVAL' ;
-SET &YEAR_1 = IF '&YEAR.EVAL'   EQ ' ' THEN 'FOC_NONE' ELSE '&YEAR.EVAL' ;
-SET &DAUID_1 = IF '&DAUID.EVAL' EQ ' ' THEN 'FOC_NONE' ELSE '&DAUID.EVAL' ;

-SET &MONTH_TEXT = IF '&MONTH_1.EVAL'  EQ 'FOC_NONE' THEN 'All' ELSE '&MONTH_1.EVAL' ;
-SET &YEAR_TEXT  = IF '&YEAR_1.EVAL'   EQ 'FOC_NONE' THEN 'All' ELSE '&YEAR_1.EVAL' ;
-SET &DAUID_TEXT  = IF '&DAUID_1.EVAL' EQ 'FOC_NONE' THEN 'All' ELSE '&DAUID_1.EVAL' ;

Month = &MONTH.EVAL
Year  = &YEAR.EVAL
DauId = &DAUID.EVAL
-RUN

SET SQLENGINE=SQLMSS
-RUN

SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report &MONTH, &YEAR, '&DAUID' ;

TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS H5
END
-RUN

?FF H5
-RUN

TABLE FILE H5
HEADING CENTER
"Report for &MONTH_TEXT, &YEAR_TEXT "
"Dauid: &DAUID "
PRINT 
FullName 
OfficialRequest   
UnofficialRequest  
AllRequest          
OfficialProcessed   
UnofficialProcessed 
AllProcessed  
-*
BY Year    
BY Month
BY DauId
END
-RUN

Thank you !

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

Is passed to the stored Proc in SQL server ?

What do you get with &ECHO=ALL ?

FOC_NONE should cause the FOCUS line to be ignored.

quote:

Originally posted by Waz:
Is passed to the stored Proc in SQL server ?

What do you get with &ECHO=ALL ?

FOC_NONE should cause the FOCUS line to be ignored.

I am a little confused by the arguments I am providing to the stored procedure...

 
SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report &MONTH, &YEAR, '&DAUID' ; 
 

Should these arguments be left as they are? Or should real values be declared, like:
CODE]
SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report 12, 2016, 'DAU6000000011' ;
[/CODE]

quote:

Originally posted by Tomsweb:

quote:

Originally posted by Waz:
Is passed to the stored Proc in SQL server ?

What do you get with &ECHO=ALL ?

FOC_NONE should cause the FOCUS line to be ignored.

I am a little confused by the arguments I am providing to the stored procedure...

 
SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report &MONTH, &YEAR, '&DAUID' ; 
 

Should these arguments be left as they are? Or should real values be declared, like:
CODE]
SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report 12, 2016, 'DAU6000000011' ;
[/CODE]

The &ECHO = ALL just shows that values are bwing passed, MONTH=12 YEAR=2016

You said that it worked fine with values, but not with FOC_NONE, is that true ?

yes, I said that

What do you get with the ECHO=ALL with the FOC_NONE ?

I developed this fex from a spec that didn't specify that it use FOC_NONE. So, in executing the stored procedure I declared the variables on the command line, and WHERE statements were NOT required.

SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report 12, 2016, 'DAU6000000011' ;

Now, the spec was modified to include the option to create a report ALL COUNTRIES from the CAR file rather than a report for just ITALY. So I am VERY NEW to the concept of extracting data via a stored procedure, BUT I wonder if I need to abandon providing values to the Stored procedure on the command line and use the WHERE statements.

SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report ;
TABLE FILE  CAR 
PRINT HOOT NANNY
BY YEAR
BY MONTH
BY DAUID
WHERE YEAR EQ ‘2016’;
Etc.
END

Ah, do you know what the SP actually does ?

If it does or does not have the WHERE conditions in it ?

You will need to know this if you are to pass parms.

Otherwise if no parms works, then yes filter after the extract.

Yes, it requires the parameters start date, end date and dog id.

If you supply FOC_NONE as one of these parameters, there is every chance that the FEX line will be ignored.

You can use parameters in your SQL call, but the 'NO records' might mean the FOC_NONE is causing the whole line to be ignored. Try separating the parameters to individual lines:

SQL SQLMSS EX DAUTRANSCRIPT.dbo.usp_StudentUsage_Report 
&MONTH
, 
&YEAR
, 
'&DAUID' 
;

I don't know if this will work - a FOC_NONE should now exclude only the particular line, but it also means nothing is passed to the stored procedure. Depending on what the stored procedure is expecting for "All" values, it might be better to use Dialogue Manager to turn the FOC_NONE to something else.

quote:

...So I am VERY NEW to the concept of extracting data via a stored procedure...

When we faced this situation, our development community decided to use the WFDS Metadata Builder to create .mas and .acx for our Oracle Stored Procs.

The MFD does a nice job of documenting the required INPUTs and ANSWERSET.

Our developers then use their normal TABLE FILE command structure/standards like they do for their Oracle tables/views.

I didn't write the stored procedure (usp) so I don't know what "command" or token I might be required to pass to the usp to receive an answer set of ALL Countries.

This is a great baptism to learn about structured queries and NULL variables.

quote:

Originally posted by Tomsweb:
I didn't write the stored procedure (usp) so I don't know what "command" or token I might be required to pass to the usp to receive an answer set of ALL Countries.

This is a great baptism to learn about structured queries and NULL variables.

After further testing I do not believe the stored procedure is programmed to bring back all data for a field when it prompt is passed as a NULL. Time to
change that.

The stored procedure had to be modified to return data when it receives null values from the launch page. [SOLVED]