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,

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.

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!

Forgot to mention, I have set statements for both variables. Thanks.

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.

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!

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.

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.

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