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]Null Parameters Passed to SQL Wizard

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]Null Parameters Passed to SQL Wizard
 Login/Join
 
Sara
Silver Member
posted
I've got a sql query that is designed to take parameters if entered but also account for null values and therefore return a complete result set. The query works as expected in SQL Navigator however upon running the query in WebFocus via the SQL Wizard there are no results returned.
I've seen similar issues where the SQL Wizard in WebFocus doesn't seem to agree with the syntax however this time I'm unable to get around it. Any suggestions?

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


WebFocus Reporting 7.1.1 Windows
 
Posts: 34 | Registered: November 22, 2005Report This Post
<Tim Howard_ABCBS>
posted
I usually use the 'FOC_NONE' value when I need to do that.


Set your variable to either be the value your user selected or set it equal to 'FOC_NONE' if it's null.

Ex:
-SET &INVAR = IF &INVAR EQ '' THEN &INVAR ELSE 'FOC_NONE';

ENGINE DB2 SET DEFAULT_CONNECTION DB2NAME
SQL DB2 PREPARE SQLOUT FOR
select country, car, model
from db.car
where car = &INVAR

Your where statement must be on a separate line from the rest of it, because the 'FOC_NONE' variable basically tells it to ignore the whole line.

This may not be the only way, but it's worked for me in the past. Hope it helps.
 
Report This Post
Sara
Silver Member
posted Hide Post
Thanks for the info, unfortunately it doesn't seem to be working. I still receive 0 rows with the set statement.

My query is something like this:
ENGINE SQLORA SET DEFAULT_CONNECTION KNP2.fmr.com
SQL SQLORA PREPARE SQLOUT FOR
select country, car, model
from db.car
where country='USA'
and ((car = &invar)
or (&invar is null)
or (&invar = ''))
and ((model = &invar2)
or (&invar2 is null)
or (&invar2 = ''))

What I'm trying to get at is to allow the user to filter the results on any number of parameters as they wish but also not be required to enter them.

As always, any help is greatly appreciated!


WebFocus Reporting 7.1.1 Windows
 
Posts: 34 | Registered: November 22, 2005Report This Post
Sara
Silver Member
posted Hide Post
Forgot to mention, I have set statements for both variables. Thanks.


WebFocus Reporting 7.1.1 Windows
 
Posts: 34 | Registered: November 22, 2005Report This Post
<Tim Howard_ABCBS>
posted
The key is to set the variables (&invar and &invar2) equal to 'FOC_NONE' if the user doesn't enter a value (or the variables are null). Try this and see if it helps:

-SET &INVAR = IF &INVAR NE '' THEN &INVAR ELSE 'FOC_NONE';
-SET &INVAR2 = IF &INVAR NE '' THEN &INVAR2 ELSE 'FOC_NONE';

ENGINE SQLORA SET DEFAULT_CONNECTION KNP2.fmr.com
SQL SQLORA PREPARE SQLOUT FOR
select country, car, model
from db.car
where country = 'USA'
and car = &invar
and model = &invar2
END

If the variable is equal to 'FOC_NONE', then it would be the same thing as completely deleting the line from the query. So you should get all cars or all models, depending on the situation. Hope this helps.
 
Report This Post
Sara
Silver Member
posted Hide Post
I'm not sure if this is expected or due to the design of my procedure however in order to create the SQLOUT report I had to set a default for the variables in Dialogue Manager first then insert the set statements you mentioned. Upon running the report and being prompted for parameter values I removed the default values and found it worked! The report returned all the data! Thanks so much for your help!


WebFocus Reporting 7.1.1 Windows
 
Posts: 34 | Registered: November 22, 2005Report This Post
yogi
Member
posted Hide Post
Sara
try building each WHERE phrase in amper variables (as below) and then add that variable in where statement of SQL.


-SET &CARWHER = IF &CAR GT 'AUDI'
- THEN 'AND car= ' | '''' | 'AUDI' | ''''
- ELSE IF &CAR EQ 'BMW'
- THEN 'AND car IN (' | '''' |
- '(BMW' | '''' | ',' | '''' | 'VolksWagan' | '''' | ')' ELSE IF &CAR EQ 'FOC_NONE' OR ' ' THEN ' ' ;


Select * from car
where country = 'england'
&CARWHER
;

Based on value of amper variable you can set where clause.
 
Posts: 19 | Location: Allstate Insurance Co. | Registered: January 25, 2005Report This Post
fpompizzi
Member
posted Hide Post
So what is the value of &invar if user leave input field blank? "FOC_NONE", NULL, ''? I have similar issue and you suggestion worked, but now when i reference a computer field in my report with the input field, when report runs it says the compute field does not exist.


WebFOCUS 8
Windows, All Outputs
 
Posts: 8 | Registered: February 01, 2016Report This Post
Tamra
Guru
posted Hide Post
Hello,

In the following link please find a description of FOC_NONE and _FOC_NULL with examples.

Amper Auto-Prompting

Scroll through the link to find:


Rules for a Multiselect List of Values
Selecting Values for Multiselect Variables
Internal Processing of _FOC_NULL
Internal Processing of FOC_NONE
Internal Processing of Select All

Hope this helps to clear up a few things.

Thank your for participating in the Focal Point Forum,
Tamra Colangelo
Focal Point Moderator
Information Builders


WebFOCUS 8x - BI Portal, Developer Studio, App Studio, Excel, PDF, Active Formats and HTML5
 
Posts: 487 | Location: Toronto | Registered: June 23, 2009Report 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]Null Parameters Passed to SQL Wizard

Copyright © 1996-2020 Information Builders