How to process the blank dates in SQLRPGLE statement.

[arifg2005]

Hi all,

I have a table with multiple fields in that I have date field too..some of the dates has blank value.While processing the table it skipping the records which have blank dates..But i need to process the blank dates too..How to rectify this problem in SQLRPLE..Can any one give me the right solution for this..

Here i have the details below..

I have employee table has employee id,name,join date,relieve date.ect.
I used SQLRPGLE to fectch some employee id as like below.
SQLSTMT =' SELECT EMPPF*. FROM EMPPF WHERE EMPID>='Dynamic value'.

I have issue,when the date field has blank value.So it don't proceed further records.
How to handle this?

Kindly give me your suggestion
How to overcome from this..

Thanks in advance
Syed Arif

[Niraj]

Hi Arif,

Grab the query in debug and run it using the SQL utility(STRSQL Command) AS400 provides. See if those records come here or not. If they don't come here then there's some problem with the query text.

If you're able to see those missing data using SQL tool, Check if the host variables or the DS you're using has the same data type defined as in the file. Blank date values get initialized to some value anyways, so that should not cause any problem.

Let us know how it works out.

Thanks!
Niraj

[arifg2005]

Hi Niraj,

It working in normal STRSQL, how to declare the host variable in this program..because problem occuring while fetching the records from the database..

can you please give some examples how to declare it and how to retrieve the blank date..

Thanks in advance.
Syed Arif

[Niraj]

Hi Arif,

I will have to login to as400 system to give you the exact syntax. However, the syntax used to be something like this.
a) In D spec define a data structure
D OutHost      DS
D Customer            10
D DoB                          D
D xxx                     ...

b) Declare Cusror C1 for your SQL statement.
c) Fetch from Cursor into OutHost

You can refer IBM documentation for help on cursor.

[arifg2005]

Hi Niraj,

This is the one nice method..I will try it and let u know.

Thanks for your reply,
Syed Arif.

[Niraj]

Make sure that the order of database fields (data type and length) in your sql query match exactly with those defined in the data structure.

All the best!
Niraj