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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] 0 records - SQL structured query using FOC_NONE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] 0 records - SQL structured query using FOC_NONE
 Login/Join
 
Master
posted
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,


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
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]


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
You said that it worked fine with values, but not with FOC_NONE, is that true ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
yes, I said that


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
What do you get with the ECHO=ALL with the FOC_NONE ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
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


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Master
posted Hide Post
Yes, it requires the parameters start date, end date and dog id.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Expert
posted Hide Post
If you supply FOC_NONE as one of these parameters, there is every chance that the FEX line will be ignored.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Expert
posted Hide Post
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.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Master
posted Hide Post
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.




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Master
posted Hide Post
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.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
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.


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
Master
posted Hide Post
The stored procedure had to be modified to return data when it receives null values from the launch page. [SOLVED]


Tomsweb
WebFOCUS 8.1.05M, 8.2.x
APP Studio, Developer Studio, InfoAssist, Dashboards, charts & reports
Apache Tomcat/8.0.36
 
Posts: 573 | Location: Baltimore, MD | Registered: July 06, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] 0 records - SQL structured query using FOC_NONE

Copyright © 1996-2020 Information Builders