DB2/400 date query problem

could you post sample values from your table?
what does
DATE(SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6))
output for those values?
also, can you post the error you get with the first one?

Hi Francisco,

Can you post the error?  A "point of reference" would be a big help!


Thanks,
Kent

Thank you for your answers.

The values in FECHOC field are similiar to

"21032012" (it stands for 2012-03-21, ddmmyyyy format)

or

"1032012" (for 2012-03-01, dmmyyyy format)

Error using ODBC under PHP is

Warning: odbc_exec(): SQL error: [unixODBC], SQL state 37000

Under AS400 it displays a message

Error en la selección que tiene relación con el campo FECHOC.

Wich may be translated as "Error in the seleccion related with the FECHOC field"

To momi, your query gives the following error

Warning: odbc_exec(): SQL error: [unixODBC], SQL state S1000 in SQLExecDirect

Thanks again,

Francisco

I have a numeric field wich is supposed to represent a date. Somehow a developer thought that a mixed form of 'ddmmyyyy' and 'dmmyyyy' was ok to represent a date.

That is confusing.

If it really is a single 'numeric' field, then 'ddmmyyyy' and 'dmmyyyy' are both valid.

Any numeric field that properly holds a value of the form 'ddmmyyyy' will also hold a value of the form 'dmmyyyy' and there will be no way to tell the difference.

Please clarify what your second sentence means because it does not make sense.

Tom

Tom, I meant that since the FECHOC is a numeric field the are not leading zeroes. As you mentioned 1032012 is a valid number. I was trying to clarify why I used such a query to get a  correctly formed date.

The problem is that I cannot compare dates using the DATE(...) function as part of a condition in the WHERE statement.

Francisco

Hi Francisco,

This looks suspiciously like a driver issue (ODBC/JDBC, etc.).  Offhand, I suspect that the driver isn't handling the date in YYYY-MM-DD format.

Can you run the query if both terms are cast as dates?  If neither is?  Try both of these and see what happens.

SELECT *
FROM QS36F/MATECPRA
WHERE DATE(SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6)) >= DATE('2011-01-01')

Select allOpen in new window

                                 

SELECT *
FROM QS36F/MATECPRA
WHERE SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6) >= '2011-01-01'

Select allOpen in new window

                                 

Kent

Kent, I have executed both queries from the PHP scrip and the SQL prompt.

Both queries under the PHP script returned the following error

Warning: odbc_exec(): SQL error: [unixODBC], SQL state S1000 in SQLExecDirect in /srv/www/htdocs/intranet/qry.php on line 35 Error en odbc_exec [unixODBC]

and under the SQL prompt

Error en la selección que tiene relación con el campo FECHOC.

This is getting pretty anoying :(

Thank you,

Francisco

Actually, that's a good thing in that it's hugely telling.

That second query has no dates in it, just strings.  And it's still failing.  We know that it's not just a date issue.

Are you running the PHP code on the same system where you've got the SQL prompt?  If so, we need to check the DB2 driver(s) on it.


Kent

I don't have an AS400 at my disposal, but I do have an LUW system.  That second query works fine on LUW system with the correct drivers.

with "QS36F/MATECPRA" (fechoc) as (select * from (Values ('11223344')))
SELECT *
FROM "QS36F/MATECPRA"
WHERE SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6) >= '2011-01-01'

Select allOpen in new window

                                           
The only change from the query above is the CTE to create the pseudo-table, and to encapsulate the table name in quotes.  The query works just fine.
                                           

Kent

Kent, yes I execute the PHP script against the very same server but remember that its failing even from the SQL prompt (STRSQL)

In the other hand why did the query

SELECT DATE(SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6))
FROM QS36F/MATECPRA

Select allOpen in new window

work ok? That query returns dates like 2011-10-20, etc.

I tried something else: converting the whole thing to an integer with the format yyyymmdd using

select date(int(substr(fechoc, length(ltrim(fechoc))-3, 4))*10000 + int(substr(fechoc, length(ltrim(fechoc))-5, 2))*100 + int(substr(fechoc, 1, length(ltrim(fechoc))-6))) from qs36f.matecpra

Select allOpen in new window

It works but when I use that integer within the WHERE statement it gives an error again

SELECTt date(int(substr(fechoc, length(ltrim(fechoc))-3, 4))*10000 + int(substr(fechoc, length(ltrim(fechoc))-5, 2))*100 + int(substr(fechoc, 1, length(ltrim(fechoc))-6))) FROM qs36f.matecpra WHERE date(int(substr(fechoc, length(ltrim(fechoc))-3, 4))*10000 + int(substr(fechoc, length(ltrim(fechoc))-5, 2))*100 + int(substr(fechoc, 1, length(ltrim(fechoc))-6)))>20110101

Select allOpen in new window

Again, I'm really lost. Thank you!

Francisco

Hi Francisco,

Sorry, but I'm wondering if your PHP environment and the SQL prompt are running on the same client machine.  

They may be running on your desktop, a server, even the DB2 server, but they're both creating client connections to DB2.  I'm suspecting that there's an issue in the DB2 driver that they're using to connect to the database, and if PHP and the SQL prompt are on the same computer, that's a really strong suggestion that it's the driver.  

If they're on different computers, it's still probably a driver issue, but we'll have to diagnose two clients, not just the one.


Kent

Kent, when I use the SQL promp I'm connecting against an iSeries server using a Telnet terminal so no driver is being used. Just the old and well known black and green terminal. It's a 5250 emulation.

BTW I made a mistake in the last query when I converted to integer I used the INT function not DATE, like this

SELECTt int(
int(substr(fechoc, length(ltrim(fechoc))-3, 4))*10000 + 
int(substr(fechoc, length(ltrim(fechoc))-5, 2))*100 + 
int(substr(fechoc, 1, length(ltrim(fechoc))-6))
)
FROM qs36f.matecpra
WHERE int(
int(substr(fechoc, length(ltrim(fechoc))-3, 4))*10000 + 
int(substr(fechoc, length(ltrim(fechoc))-5, 2))*100 + 
int(substr(fechoc, 1, length(ltrim(fechoc))-6))
)>20110101

Select allOpen in new window

Francisco

Kent et all,

I think I've found part of the solution. In the FECHOC field there are several records with 0 (zero) value, not NULL but zero.

So it might be possible that a substring of several characters cannot be obtained from a 1 character value. Am I right?

I've created a table with an integer field having two values 20021978 and 1021978 and the following query works ok

SELECT * 
FROM prueba
WHERE  date(
substr(fecha, length(ltrim(fecha))-3, 4) || '-' ||
substr(fecha, length(ltrim(fecha))-5, 2) || '-' ||
substr(fecha, 1, length(ltrim(fecha))-6)
) > '1978-02-10' 

Select allOpen in new window

So how can I omit the records with 0 value?

Thanks again,

Francisco

Hi Francisco,

Would that be a single character "0", or a is the value missing a leading zero?


Kent

Kent, if I do a

SELECT * FROM QS36F/MATECPRA WHERE fechoc=0

in the terminal it works ok so I was pretty sure it was a simple 0 (zero) but when I do the very same (and simple) query within PHP it gives an error. So how can I tell?

Sorry if I'm asking the obvious,

Francisco

Hi!

Does this work ?

SELECT *
FROM QS36F/MATECPRA
WHERE DATE(SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6)) >= Date('2011-01-01')

Regards,
     Tomas Helgi

Tomas, thank you for your time.

Unfortunately your query did not work but it does work if I use it querying a field with no zeroes in it, so syntax is ok but I need to exclude zero valued records.

SELECT *                                            
FROM prueba                                         
WHERE DATE(                                         
SUBSTR(fecha, LENGTH(LTRIM(fecha))-3, 4) || '-' ||  
SUBSTR(fecha, LENGTH(LTRIM(fecha))-5, 2) || '-' ||  
SUBSTR(fecha, 1, LENGTH(LTRIM(fecha))-6)            
) >= Date('2011-01-01')                             

Select allOpen in new window

Francisco

Hi Francisco,

Can you post the table structure for QS36F/MATECPRA?  It could be helpful to see/know the exact data type(s) involved.


Thanks,
Kent

Kent,

There are 88 fields in that table and they are CHAR, NUMERIC or DECIMAL.

FECHOC is defined as NUMERIC with a size of 8 digits with no decimals

I've made another test and created a logical view of a one field tabled (PRUEBA) omiting the zero valued records and it worked ok.  So a logical view might be a solution but since there are 88 fields the logical view might be a bit long to do.

What do you think?

Francisco

SELECT *
FROM QS36F/MATECPRA
WHERE DATE(SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-3, 4) || '-' || SUBSTR(fechoc, LENGTH(LTRIM(fechoc))-5, 2) || '-' || SUBSTR(fechoc, 1, LENGTH(LTRIM(fechoc))-6)) >= Date('2011-01-01') AND fechoc > 0

This should do it.

Regards,
    Tomas Helgi

Sorry Tom -- I started typing and got distracted.....

Tom and Kent,

I tried the "fechoc > 0 " and "fechoc <> 0" and the very same error is shown.

I know there is a problem with the data in that field bechause being a numeric field if I do

select fechoc                                  
from qs36f/matecpra                            
where fechoc is not null                       
order by fechoc                                

Select allOpen in new window

the statement "order by..." generates an error

Error de conversión o de correlación de datos.

I think data in this field has no consistency at all.

Francisco

Hi Francisco,

This sure keeps moving, doesn't it?  :(

Is it possible that the field contains data that is non-numeric?  

How many rows are in the table?

Let's see if we get an error if we just try to use all of the data

select (fechoc+1)
from qs36f/matecpra                            
where fechoc is not null 

Select allOpen in new window

                     


Kent

Kent,

That query is very insteresting. It does not give any error when listed the initial records but when you browse to the bottom of the list the system gives the

Error en la selección que tiene relación con el campo FECHOC.

error. So I guess some values are not numeric.

It seems the original query was ok in first place, data in that field is corrupt. I'm wondering if there is a isNaN function to see wich records are in conflict with the data type.

Francisco

Whew...   We're finally zeroing in.

I've seen that kind of error with linked tables, where one DBMS may have different rules for data types that the client, but have no idea how it can come about on an AS400.

This might actually be tough to find.  If we can't apply standard string functions to the field without generating an error somewhere in the data, we can't do much with it.

So let's start with the basics.  Can we recast the field as a varchar?  

select cast (fechoc as varchar (8))
from qs36f/matecpra                            
where fechoc is not null

Select allOpen in new window

Kent

Kent, the behaviour is the same as in the last query.

As we know wich the problem is I think this question is already answered.

All, the tests done lead me to this conclusion. If you are ok with I it I will close this question.

Francisco

That's fine.  I was just trying to help find the problem data.


Kent

...but have no idea how it can come about on an AS400.

In a real technical sense, it apparently didn't come about on "an AS/400". The table is "QS36F/MATECPRA", which first implies that it isn't a table at all and second implies that it's a System/36 file that's more or less 'hosted' in a S/36 environment running on an AS/400.

The only validation that was likely done to the data when records were written was whatever somebody coded into the program that wrote the data. No DBMS-enforced rules at all, not even for data type at a given offset into the records.

It's perfectly possible to copy record images from some unrelated file into that data space. For that type of file, validation is done when records are read, not when written.

Personally, I would first use non-SQL tools to retrieve the data into an AS/400-defined table outside of the S/36 environment. I might use the CPYF command specifying ERRLVL(*NOMAX) and FMTOPT(*NOCHK) into a file that's been fully defined.

Then I'd review to see if any records were dropped and what they were. Once I have every record, I would clean up data in the new file before copying it back into the S/36 environment.

This is just one of the costs of trying to keep an environment going that's been obsolete for a couple decades.

Tom

It was really helpful the way that I was leaded to the root of the problem