Solved

DB2/400 date query problem

Posted on 2012-03-21
30
3,010 Views
Last Modified: 2012-03-23
Hi all, 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.

So to compare dates I want to create a query using the DATE, SUBSTR, LENGTH and LTRIM to create a date-like string but the query gives an error

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)) >= '2011-01-01'
 

Open in new window


If I make this query it works ok

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

Open in new window


As always any help would be much appreciated,

Francisco
0
Comment
Question by:Panchux
  • 13
  • 12
  • 2
  • +2
30 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 37748015
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?
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37748046
Hi Francisco,

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


Thanks,
Kent
0
 
LVL 8

Author Comment

by:Panchux
ID: 37748756
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
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37749183
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
0
 
LVL 8

Author Comment

by:Panchux
ID: 37749269
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37749314
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')

Open 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'

Open in new window


                                 

Kent
0
 
LVL 8

Author Comment

by:Panchux
ID: 37749492
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37749589
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37749626
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'

Open 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
0
 
LVL 8

Author Comment

by:Panchux
ID: 37749627
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

Open 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

Open 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

Open in new window


Again, I'm really lost. Thank you!

Francisco
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37749653
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
0
 
LVL 8

Author Comment

by:Panchux
ID: 37749719
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

Open in new window


Francisco
0
 
LVL 8

Author Comment

by:Panchux
ID: 37750672
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' 

Open in new window


So how can I omit the records with 0 value?

Thanks again,

Francisco
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37750700
Hi Francisco,

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


Kent
0
 
LVL 8

Author Comment

by:Panchux
ID: 37750815
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
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 37751678
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
0
 
LVL 8

Author Comment

by:Panchux
ID: 37752005
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')                             

Open in new window


Francisco
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37752140
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
0
 
LVL 8

Author Comment

by:Panchux
ID: 37752233
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
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 37752306
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
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 37752379
Hi Francisco,

I'm really bothered by the conversion failing even when there is no reference or casting to a date type.  Strings are strings and should be treated that way regardless of the string  value.

I think that I understand the issues when casting the string to a date.  When the value is zero, casting '0000-00-00' to a date type generates an obvious error.  I'm now wondering if recasting the illegal date is the issue.  But that doesn't explain the issue with the query when there is no recast to a date.

Can you filter for non-zero values?

  WHERE fecha <> 0

Also, can *fecha* ever be negative?  If so, the displayed value could be 9 characters (with the leading '-').  This would effect the biasing into the string to format it as a date.  And easy workaround here would be to filter the value by passing it through the DIGITS function.  Or excluding them, at least for now.

  WHERE fecha > 0


Kent
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37752395
Sorry Tom -- I started typing and got distracted.....
0
 
LVL 8

Author Comment

by:Panchux
ID: 37753113
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                                

Open 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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37753727
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 

Open in new window

                     


Kent
0
 
LVL 8

Author Comment

by:Panchux
ID: 37754125
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37754182
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

Open in new window




Kent
0
 
LVL 8

Author Comment

by:Panchux
ID: 37754328
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 37754342
That's fine.  I was just trying to help find the problem data.


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 37755226
...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
0
 
LVL 8

Author Closing Comment

by:Panchux
ID: 37756586
It was really helpful the way that I was leaded to the root of the problem
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now