Four Js Development Tools Forum

When I prepare some SQL statement with question marks "?" Genero replaces it with some variable value, that works just fine. If the variable value is null, the query also executes and return sqlcode 0, which means no error, but the query does not return the expected values.

So I have a query with a "date" field and the value could be a date or null, both are right and must return some value, but when the date is null ir simply don't return nothing and gives no error.

My question is, how does Genero handle a null variable at the "execute using" command?

Genero Studio 2.50.20 build-136704
fglrun 2.50.11 build-2343.24
IBM Informix Version 11.70

Hello Anderson,

NULL is a special value that causes a lot of trouble in SQL programming.
As a general advice try to avoid it when you can set a default value.

When using ? place holders + EXECUTE USING a NULL value, Genero SQL drivers just pass the parameter as NULL to the database client library, as for any other SQL statement (INSERT, UPDATE, DELETE).

Then database server rules apply regarding NULL comparison...
SQL standard requires NULL comparison with the IS NULL or IS NOT NULL operator.
When using the = equal operator and an operand is NULL, the boolean expression evaluates to FALSE (or NULL, but never to TRUE with NULL=NULL).

Therefore, if you need to search for NULLs and for a specific date in the same query, you will have to adapt the SQL to distinguish "colname = ?" and "colname IS NULL", depending on the NULL state of the parameter...

The problem is that colname = ? requires a USING parameter while colname IS NULL does not.
The number of parameters in the USING clause must match the ? place holders.
So you would have to write different FOREACH USING or EXECUTE USING statements.
The trick here is to add a second parameter to perform the (colname IS NULL) test:

WHERE d=? OR (1 = ? AND d IS NULL)
 

Try the following:

MAIN
    DEFINE vd DATE, nt INT, vk INT
 
    DATABASE test1
    --CONNECT TO "test1+driver='dbmpgs'" USER "pgsuser" USING "fourjs"
 
    CREATE TEMP TABLE tt1 ( k INT, d DATE )
    INSERT INTO tt1 VALUES ( 1, TODAY )
    INSERT INTO tt1 VALUES ( 2, NULL )
 
    DECLARE curs1 CURSOR FROM "SELECT k FROM tt1 WHERE d=? OR (1 = ? AND d IS NULL) ORDER BY k"
 
    DISPLAY "With TODAY in USING parameter:"
    LET vd = TODAY
    LET nt = 0
    FOREACH curs1 USING vd, nt INTO vk
        DISPLAY vk
    END FOREACH
 
    DISPLAY "With NULL in USING parameter:"
    LET vd = NULL
    LET nt = 1
    FOREACH curs1 USING vd, nt INTO vk
        DISPLAY vk
    END FOREACH
 
END MAIN
 

However, I wonder:
Why would you need to fetch (in the same result set) rows with a specific date, and with nulls in the date?
Don't you want you treat these rows in 2 separate queries?

Seb

Sebastien,

Thank you very much for your reply, the solution with WHERE d=? OR (1 = ? AND d IS NULL) solves the problem. Maybe I will also be able to do something like

WHERE d=? OR (? IS NULL AND d IS NULL)

Not sure if this is gonna work, but worth a shot.

I need this for a query that generates a report of sent files and files that will be send. So if the user chose the option to list files sent in a specific date, the query runs fine, but if the user chose the option to list files that will be send, this are the ones with the null date.

In other words, files with the informed date are the ones that was already sent, and files with null date are the ones that will be sent, and i need to list both, according to the option.

If you are ever unsure of the SQL statement that is actually passed to the database, the output when FGLSQLDEBUG is set http://4js.com/online_documentation/fjs-fgl-manual-html/index.html#c_fgl_EnvVariables_FGLSQLDEBUG.html will show you


Another approach you may want to investigate is to use Multi-Dialog,  where  the date field is in a CONSTRUCT and your other report options are in an INPUT.

OPTIONS FIELD ORDER FORM
DIALOG
   INPUT report_options...
   END INPUT
   CONSTRUCT date_qbe BY NAME ON sent_date ...
   END CONSTRUCT
...
END DIALOG
 
IF include_sent_reports THEN
   LET l_sql = l_sql, " AND ", date_qbe
ELSE
   LET l_sql = l_sql, " AND sent_date IS NULL "
END IF

This gives your user the flexibility of date-ranges and multiple dates but if you didn't want that you could add some field validation to ensure a single-date is entered (you may need to add validation to ensure user does not enter "=")

We encourage you to use SQL parameters with dates as it avoids the problem of date formats.  Using CONSTRUCT like this also avoids the problems of date formats, and can be handy in situations where report, enquiry QBE's have multiple options that influence the number of parameters that need to be passed to the SQL and you just want to create the resultant SQL rather than having multiple OPENs to cater for differing number of parameters and permutations

 

Reuben,

Thank you for your answer, the use of "construct" is indeed a good approach to solve various input formats. But in this case, i would like to have the SQL already prepared, as this makes the execution faster, at least in our database, when the SQL is executed multiple times.

Hi,

Another possible approach for this kind of select is using UNION

select * from table where date=?
union
select * from table where date is null


regards

Benjamin

Thanks for your reply! Unfortunately, the "union" will not return the desired results. This solution will always return the "null" values and i want the null values only if the date is null. If the date has been passed, then i don't want the null values.

ok then with union you can :

select * from table where date=?
 union all
select * from table where date is null and ? is null

see attached sample

but in fact is the same approach than a combined OR with one select ...

if you work with informix you can also use the function Nvl ...

select * from table where Nvl(dat,"311289") = ?

The SQL expression (? is null) will not work with all databases, for example with PostgreSQL you get a param type unknown error...

I agree that this "? is null" approach is a bit odd, but I guess there is no "easy way" to solve this, by easy way I mean an official solution, that will be easy to read and understand.

We have chose to declare the SQL twice, one time with "?" and again with "is null". The down side is the need to declare the "foreach" block twice:

if date is null
  then
       foreach pont_date_null into dates[dates.getLength()+1].*
       end foreach
  else
       foreach pont_date using date into dates[dates.getLength()+1].*
       end foreach
end if
 

Not the most elegant solution, but at least it's easy to read and understand.

Yes your solution is easier to read and certainly the most optimal regarding SQL query performances...
Seb

the following syntax would  also be possible if we wish only one prepare statement (don't know if progress accept that)

 select * from table where (dat=?) or (dat is null and 1=?)

       open cursor using date_null,1  --> if looking for NULL records
or
       open cursor using date_value,0  --> if looking records matched a specific date

... but this is only "une affaire de goût" because there is not only one "best trick"


Benjamin