Hi Sebastien.

Sorry, I have no better solution than that supplied by Art.  Though the "Not implemented yet" error message indicates that IBM does have plas to implement it.  Hence, the RFE might be to step on the gas on this one.

But how are you trying to use coalesce()?  I have yet to try it but I would think you could still use it with a literal string, like:

select name, coalesce(middle, "(?)") middle, last from customer;

Please post the way you are using it so we can all start clamoring for it like it's a game-changer. <Grin>

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
------------------------------

Hi Jacob,

When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

The request came from user code written in Genero BDL (4GL), where a complex SELECT statement was actually using the NVL() function, which has same limitation regarding host variables.

I thought COALESCE() is more standard and would support host variables like with other DB engines, but it does not with Informix.

The goal is to do use some default value from an SQL column, if the provided host variable is NULL:

 .... WHERE column1 = NVL(variable1,column2)

If variable1 is not null, compare column1 to variable1, otherwise, compare to column2.

column1 most never be null of course.

We could solve this by using a CASE instruction (CASE WHEN variable1 IS NOT NULL THEN variable1 ELSE column2 END), but I thought NVL() or COALESCE() is more elegant.

Making sure that the variable1 is not null before executing the main SQL statement would require some (complex) additional SELECT statements before the main SQL statement.

Any best practice for such case is welcome.

Seb

------------------------------
Sebastien FLAESCH
------------------------------


Original Message:
Sent: Fri October 13, 2023 01:15 PM
From: Jacob Salomon
Subject: SQL COALESCE() does not support SQL parameters

Hi Sebastien.

Sorry, I have no better solution than that supplied by Art.  Though the "Not implemented yet" error message indicates that IBM does have plas to implement it.  Hence, the RFE might be to step on the gas on this one.

But how are you trying to use coalesce()?  I have yet to try it but I would think you could still use it with a literal string, like:

select name, coalesce(middle, "(?)") middle, last from customer;

Please post the way you are using it so we can all start clamoring for it like it's a game-changer. <Grin>

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra

Sebastien replied to me:

> When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

Correct.  I meant the string literal "(?)" but that was a poor choice of literal due to the special meaning of ? as a placeholder for PREPAREing sql statements.

In any case, I found out many years ago that ? could be used only where a literal value would go, not where a column name would go.  So I suppose one could try using COALESCE(some-column, ?) in the query, like this:

select name, coalesce(middle, ?) middle, last from customer;

Of course, this would barf in straight SQL ie. dbaccess.  But where it can be PREPAREd ie. ESQL it should also fail; it is being used in a column specification.  My Linux system is not booted right now; can someone please check if the syntax I just suggested works in the context of a PREPARE?  As I said, it should not work but I might be pleasantly surprised.

Hi Jacob,

When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

The request came from user code written in Genero BDL (4GL), where a complex SELECT statement was actually using the NVL() function, which has same limitation regarding host variables.

I thought COALESCE() is more standard and would support host variables like with other DB engines, but it does not with Informix.

The goal is to do use some default value from an SQL column, if the provided host variable is NULL:

 .... WHERE column1 = NVL(variable1,column2)

If variable1 is not null, compare column1 to variable1, otherwise, compare to column2.

column1 most never be null of course.

We could solve this by using a CASE instruction (CASE WHEN variable1 IS NOT NULL THEN variable1 ELSE column2 END), but I thought NVL() or COALESCE() is more elegant.

Making sure that the variable1 is not null before executing the main SQL statement would require some (complex) additional SELECT statements before the main SQL statement.

Any best practice for such case is welcome.

Seb

------------------------------
Sebastien FLAESCH
------------------------------

Hi Jacob,

When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

The request came from user code written in Genero BDL (4GL), where a complex SELECT statement was actually using the NVL() function, which has same limitation regarding host variables.

I thought COALESCE() is more standard and would support host variables like with other DB engines, but it does not with Informix.

The goal is to do use some default value from an SQL column, if the provided host variable is NULL:

 .... WHERE column1 = NVL(variable1,column2)

If variable1 is not null, compare column1 to variable1, otherwise, compare to column2.

column1 most never be null of course.

We could solve this by using a CASE instruction (CASE WHEN variable1 IS NOT NULL THEN variable1 ELSE column2 END), but I thought NVL() or COALESCE() is more elegant.

Making sure that the variable1 is not null before executing the main SQL statement would require some (complex) additional SELECT statements before the main SQL statement.

Any best practice for such case is welcome.

Seb

------------------------------
Sebastien FLAESCH
------------------------------


Original Message:
Sent: Fri October 13, 2023 01:15 PM
From: Jacob Salomon
Subject: SQL COALESCE() does not support SQL parameters

Hi Sebastien.

Sorry, I have no better solution than that supplied by Art.  Though the "Not implemented yet" error message indicates that IBM does have plas to implement it.  Hence, the RFE might be to step on the gas on this one.

But how are you trying to use coalesce()?  I have yet to try it but I would think you could still use it with a literal string, like:

select name, coalesce(middle, "(?)") middle, last from customer;

Please post the way you are using it so we can all start clamoring for it like it's a game-changer. <Grin>

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra

Sebastien:

A couple of points:

1. Coalesce() and NVL() may appear more elegant, but both are functions which have to iterate while the CASE statement is internal to the SQL parser and will, I believe, execute faster. After some limited testing on an int column in a table with 387000 rows, they run about the same.Testing with a char(128) column on the same table, coalesce() and CASE ran about the same while NVL was about 3X faster! 
2. I don't understand why testing the value of the variable in code to be used in the CASE test would involve complex processing. Personally, I would just select the column and test for NULL after the fact when the data was returned. Testing for NULL in 4GL is trivial and if NULL you can replace the NULL with your default value in general. In your case I would just select both column1 and column2 and, again, decide in code space whether to report/use column1 or column2 depending on whether the value in the local variable is or is not NULL. You are just overthinking this.
3. The above does not change the fact that an RFE is called for, but that won't help you. The suggestions in #2 above will.

Art

Hi Jacob,

When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

The request came from user code written in Genero BDL (4GL), where a complex SELECT statement was actually using the NVL() function, which has same limitation regarding host variables.

I thought COALESCE() is more standard and would support host variables like with other DB engines, but it does not with Informix.

The goal is to do use some default value from an SQL column, if the provided host variable is NULL:

 .... WHERE column1 = NVL(variable1,column2)

If variable1 is not null, compare column1 to variable1, otherwise, compare to column2.

column1 most never be null of course.

We could solve this by using a CASE instruction (CASE WHEN variable1 IS NOT NULL THEN variable1 ELSE column2 END), but I thought NVL() or COALESCE() is more elegant.

Making sure that the variable1 is not null before executing the main SQL statement would require some (complex) additional SELECT statements before the main SQL statement.

Any best practice for such case is welcome.

Seb

------------------------------
Sebastien FLAESCH


Original Message:
Sent: Fri October 13, 2023 01:15 PM
From: Jacob Salomon
Subject: SQL COALESCE() does not support SQL parameters

Hi Sebastien.

Sorry, I have no better solution than that supplied by Art.  Though the "Not implemented yet" error message indicates that IBM does have plas to implement it.  Hence, the RFE might be to step on the gas on this one.

But how are you trying to use coalesce()?  I have yet to try it but I would think you could still use it with a literal string, like:

select name, coalesce(middle, "(?)") middle, last from customer;

Please post the way you are using it so we can all start clamoring for it like it's a game-changer. <Grin>

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra

Sebastien:

A couple of points:

1. Coalesce() and NVL() may appear more elegant, but both are functions which have to iterate while the CASE statement is internal to the SQL parser and will, I believe, execute faster. After some limited testing on an int column in a table with 387000 rows, they run about the same.Testing with a char(128) column on the same table, coalesce() and CASE ran about the same while NVL was about 3X faster! 
2. I don't understand why testing the value of the variable in code to be used in the CASE test would involve complex processing. Personally, I would just select the column and test for NULL after the fact when the data was returned. Testing for NULL in 4GL is trivial and if NULL you can replace the NULL with your default value in general. In your case I would just select both column1 and column2 and, again, decide in code space whether to report/use column1 or column2 depending on whether the value in the local variable is or is not NULL. You are just overthinking this.
3. The above does not change the fact that an RFE is called for, but that won't help you. The suggestions in #2 above will.

Art

Hi Jacob,

When using "(?)" you don't use a host variable, it's just a plain SQL string literal.

The request came from user code written in Genero BDL (4GL), where a complex SELECT statement was actually using the NVL() function, which has same limitation regarding host variables.

I thought COALESCE() is more standard and would support host variables like with other DB engines, but it does not with Informix.

The goal is to do use some default value from an SQL column, if the provided host variable is NULL:

 .... WHERE column1 = NVL(variable1,column2)

If variable1 is not null, compare column1 to variable1, otherwise, compare to column2.

column1 most never be null of course.

We could solve this by using a CASE instruction (CASE WHEN variable1 IS NOT NULL THEN variable1 ELSE column2 END), but I thought NVL() or COALESCE() is more elegant.

Making sure that the variable1 is not null before executing the main SQL statement would require some (complex) additional SELECT statements before the main SQL statement.

Any best practice for such case is welcome.

Seb

------------------------------
Sebastien FLAESCH


Original Message:
Sent: Fri October 13, 2023 01:15 PM
From: Jacob Salomon
Subject: SQL COALESCE() does not support SQL parameters

Hi Sebastien.

Sorry, I have no better solution than that supplied by Art.  Though the "Not implemented yet" error message indicates that IBM does have plas to implement it.  Hence, the RFE might be to step on the gas on this one.

But how are you trying to use coalesce()?  I have yet to try it but I would think you could still use it with a literal string, like:

select name, coalesce(middle, "(?)") middle, last from customer;

Please post the way you are using it so we can all start clamoring for it like it's a game-changer. <Grin>

------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra