Query question 1

[amethystct]

I have 2 questions.

I have a query that I need to return the difference between 2 dates. One is a timestamp field the other is a char field but it's in timestamp format.

First question is, is the extend the way I have it correct? It works but only if there's a value.

Second question is, isn't there a case statement for informix sql? If column is this do this otherwise do this? Whenever the charfield is null I get an sql exception and it stops processing. If the field is empty I just need to set the diff field to 0.

Thanks!

select (time1 - (extend(charfield)) diff
from tablename

 

[vpshriyan]

Hi,

select extend(today) from systables where tabid=1 ;

will fill zeros for all the values from hour to second. It's ok, if you are not interested in these values. It is always better to use key words like year to hour, year to minute etc.

Here is an example which list table name and days, hours to second lapsed since it's creation at the back-end:

select tabname, current - extend(created, year to second) from systables where tabid between 100 and 120 ;

The above query can be modified to reflect only the lapsed days as:

select tabname, extend(current,year to day) - extend(created, year to day) from systables where tabid between 100 and 105

SQL does support CASE keyword:

select
case
when charfield is null then '31121899'
when charfield = "01012003" then '02012003'
else '01011980'
end,
...
from ...

Regards,
Shriyan

 

[amethystct]

Shriyan, you're fantastic!! This is exactly what I was looking for!

Thank you!!!!!