Hi,

I can not select dates from a DB2 table

the table is defined as:

I read the table with the following instructions:

where fromdate and todate are difened as date.

SQLCODE=-301, SQLSTATE=07006, DRIVER=3.63.75[sqlstate:07006][sqlcode:-301]

If I define a variable: datenull date=null; (which, however, assumes the value of the day and no blank) and if I replace  DATESE =:null with DATESE =:datenull no records are selected.

In the table the field DATESE has  no values (equals blank).

How to test if the value of a  blank date ?

paoloc

How is :null defined?  This is a host variable based on how you listed it here?

What is its value?

What are the values of :fromdate and :todate?

Is the column set to "null" or not null, but no value

 

Hi 

I cannot solve this problem.

I defined a date filed DayDate= dateTimeLib.currentDate();

DATESE in the table as no values.

In the select statement if I set the condition: DATESE <>:DayDate then the selection extract the rigth values

But if set the condition DATESE =: Day Date no records are found.

The table has both records with DATESE with values and with no values.

In EGL's record definition DATESE id defined DATESE? (nullable).

selecting with the condition DATESE is null no records are found.

paoloc

Paoloc,

I did not see where you answered Hsieh's question which is how is the column defined in the database.   If it is a date column type, normally it cannot have a "blank" value.

What happens if you try to use the same SQL queries in something like the database Development perspective with the "values" instead of host variables.  Do you get the same results?

 

Mark!

I attached a screenshot for the table.

The column is defined as DATESE date and is not char or varchar.

I cannot understan Hsieh's because the field are of date type.

paoloc

Ok... can you show the DDL for that table.

and since things can work differently between database servers....are you using DB2 on Windows or DB2 on iSeries or something else?

thanks.

Never mind no the DDL question.. I see it at the top.

 

But could you still answer the question on which database product/server is used.

 

I believe Hsieh is correct.  A DATE column will have a value or a value of NULL.  I think the tool you are using (Control Center) is not showing you that the columns without a value are set to NULL.   See the following link in the DB2 knowledge center related to Data types and there possible values - http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008474.html?cp=SSEPGG_10.1.0%2F2-9-2-3-0-6

In addition, I created your table and just inserted two rows..but purposely did NOT enter anything into DATESE for one of the rows.   When I display the data using the Database Development perspective (choose the table and do data->Return all rows), I get the data in the attached screenshot.   Notice the top row's value is NULL and the second row is a real date.

If I run the following queries:

select * from mevans.AZIONIUSER where datese <> '2015-05-20' OR datese IS NULL;

        I get the first row (the one with the NULL DATESE.

select * from mevans.AZIONIUSER where datese IS NULL;

        I get the first row (the one with the NULL DATESE)

select * from mevans.AZIONIUSER where datese = '2015-05-20';

        I get the second row (the one with the DATESE with the 2015-05-20 value)

select * from mevans.AZIONIUSER where datese <> '2015-05-20' ;

        I get no rows returned, but I don't have any with any other value

select * from mevans.AZIONIUSER where datese = NULL;

       I get no rows returned as "=" NULL is not the same as IS|NOT NULL

If you want to test for NULL in EGL and are using a host variable, you need to set that field to NULL (i.e. SET myfield NULL) before running the query.

 

     
 

 

Yes !  Mark.

NULL is showed as blank in the DB2 Control Center and

NULL is showed as NULL indicator in the Eclipse Data Studio or Database Developer.

Ok Mark !

The probleb was to set the EGL host variable myfiled to NULL and compare DATESE with this host variable. (DATESE =: myfield or DATESE <>:myfield).

Thats works well now !!!!

paoloc

Hi Paolo,

I hope so help.

Hsieh