Re: NULL generating "Sintax Error"

#1

Preetinder,

To do this I would have to alter the code of all my programs. I
support a system comprised of a few dozen executables that run fine
with other RDBMs (such as Oracle, SQL Server and Sybase) through ODBC.
Now this system is being certified to Informix. We were hopping to
port the system with a minimal change in code.

Jorge

Preetinder Dhaliwal <preetinder.dhaliwal (AT) dhl (DOT) com> wrote

Quote:

Why dont u use "" instead of null?? as informix anyway does not know
which kind of null ( if u r using into clause ). This will save time
over calling a function.

Rgds
Preetinder

Fernando Nunes wrote:

Jorge Vergara wrote:
Hello All.

The query bellow generates a sintax error:
SELECT 'ADP'
, '!'
, null
, '6.1.1.06'
#^
# 201: A syntax error has occurred.
#

Use "null()". It's created by a script in $INFORMIXDIR/etc

Regards

sending to informix-list

#2

Jorge Vergara wrote:

Quote:

Fernando.

Which script?

Jorge

xpg4_is.sql

(IDS 7.31...)
{ INFORMATION SCHEMA FOR TRI-STAR WITH X/OPEN XPG4 COMPLIANCE }

{ Utility Procedures }

{----------------------------------------------------------------------}
{------creating supporting procedures used by views--------------------}
{----------------------------------------------------------------------}
create procedure 'informix'.null()
returning char;
return null;
end procedure
document
'Procedure null() returns a null value when it is called',
'Synopsis: null() takes no arguments';

Regards

#3

Gregg.

Do you know wich engines do support casting?

Vergara

"Gregg Walker" <gregg (AT) rrmca (DOT) com> wrote

Quote:

If you want to return an null value constant in a select statement then your
engine must support casting.

Assuming your engine allows casting you can use the following if you want a
null of type integer.

SELECT null::integer FROM table;

The database can't return a null without knowing what data type to use for
the null value.

If your engine does not support casting then you could simulate by creating
a table with different columns for the different data types you would need
null values for. You could insert one row with null values and join to this
table in your queries where you need a null value returned.

Gregg Walker

"Jorge Vergara" <jlvergara (AT) sispro (DOT) com.br> wrote in message
news:2f175445.0306200556.4ebda7f8 (AT) posting (DOT) google.com...
Hello All.

The query bellow generates a sintax error:
SELECT 'ADP'
, '!'
, null
, '6.1.1.06'
#^
# 201: A syntax error has occurred.
#
, '!'
, '!'
, '!'
, '!'
, GF_CTOP_CTB.CD_CTOP_CTB

FROM GF_CTOP_CTB

If I replace the word "null" with "''" the statement runs without
error.
Is there any configuration parameter I could manipulate that would
allow me to use "null" as a constant in my queries?

Thanks in advance.

Jorge Vergara

#4

Jorge Vergara wrote:

Quote:

Do you know which engines do support casting?

IDS 9.x.

--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler (AT) earthlink (DOT) net, jleffler (AT) us (DOT) ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

#5

I believe was first available in IDS.2000 with Universal Option which is now
included in all version 9.x engines. Someone please correct me if it's
available in 7.x versions.

Is there a problem using the stored procedure Null() as someone suggested?
It seems an elegant solution as you don't have to worry about defining a
type for null. It simply returns a null of type char which will then be
converted to a null of any other type when fetched into a host variable.
Basically it's doing the same as selecting null::char but without the
requirement of your engine supporting casting.

Gregg Walker

"Jorge Vergara" <jlvergara (AT) sispro (DOT) com.br> wrote

Quote:

Gregg.

Do you know wich engines do support casting?

Vergara

"Gregg Walker" <gregg (AT) rrmca (DOT) com> wrote

If you want to return an null value constant in a select statement then
your
engine must support casting.

Assuming your engine allows casting you can use the following if you
want a
null of type integer.

SELECT null::integer FROM table;

The database can't return a null without knowing what data type to use
for
the null value.

If your engine does not support casting then you could simulate by
creating
a table with different columns for the different data types you would
need
null values for. You could insert one row with null values and join to
this
table in your queries where you need a null value returned.

Gregg Walker

"Jorge Vergara" <jlvergara (AT) sispro (DOT) com.br> wrote in message
news:2f175445.0306200556.4ebda7f8 (AT) posting (DOT) google.com...
Hello All.

The query bellow generates a sintax error:
SELECT 'ADP'
, '!'
, null
, '6.1.1.06'
#^
# 201: A syntax error has occurred.
#
, '!'
, '!'
, '!'
, '!'
, GF_CTOP_CTB.CD_CTOP_CTB

FROM GF_CTOP_CTB

If I replace the word "null" with "''" the statement runs without
error.
Is there any configuration parameter I could manipulate that would
allow me to use "null" as a constant in my queries?

Thanks in advance.

Jorge Vergara

#6

Gregg Walker wrote:

Quote:

"Jorge Vergara" <jlvergara (AT) sispro (DOT) com.br> wrote in message
news:2f175445.0306261047.676bafe8 (AT) posting (DOT) google.com...

Gregg.

Do you know wich engines do support casting?

I believe was first available in IDS.2000 with Universal Option which is now
included in all version 9.x engines. Someone please correct me if it's
available in 7.x versions.

No it's not, but just to clarify, IDS 2000 was the new marketing name
for IDS+UDO, which was the marketing name for IDS version 9.20. :-)

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

Quote:

Mark D. Stock mailto:mdstock (AT) MydasSolutions (DOT) com |//////// /|
Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
+-----------------------------------+//// / ///|
|We value your comments, which have |/// / ////|
|been recorded and automatically |// / /////|
|emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+

sending to informix-list

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: NULL generating "Sintax Error"

comp.databases.informix comp.databases.informix

Re: NULL generating "Sintax Error" - 06-24-2003 , 07:37 AM

Re: NULL generating "Sintax Error" - 06-24-2003 , 02:32 PM

Re: NULL generating "Sintax Error" - 06-26-2003 , 02:47 PM

Re: NULL generating "Sintax Error" - 06-27-2003 , 01:08 AM

Re: NULL generating "Sintax Error" - 06-27-2003 , 01:24 AM

Re: NULL generating "Sintax Error" - 06-27-2003 , 05:06 AM