IDS Forum

Re: SQL QUERY - TIME

Posted By: Jonathan Leffler
Date: Monday, 29 November 2004, at 1:13 p.m.

In Response To: SQL QUERY - TIME (Valeriano Villegas Juan)

"Valeriano V...." <jvaleriano@ref.pemex.com> wrote on 11/25/2004 08:40:37
AM:

> This is a multi-part message in MIME format.

Please avoid using MIME (and SHOUTING - see below).


> HOW WILL BE THE QUERY FOR DETECT,
> MORE THAN 30 MINUTES OF DIFERENCE
> BETWEEN 2 FIELDS TYPE DATETIME (HOUR TO SECOND)

> -----------------------------------------------------------------
>
> PERFORM: Query Next Previous View Add Update Remove Table
> Screen ...
>
> Changes a row in the active database table. ** 1: cdr_delay_crones
> table**
>
> usuario [cdr ]
> pid [27422 ]
> ppid [27420 ]
> c [13 ]
> h_inicio [10:05:00]
> terminal [? ]
> cpu_time [0:00 ]
> comando [ps -ef ]
> [ ]
> h_actual [12:55:09]

If your question is about how to express the query in ISQL Perform, the
short answer is "You can't". The longer answer discusses issues such as
the inability to specify criteria that relate to two separate fields in
the Perform query language. You could consider working on a view which
included the difference between the two fields as a named, calculated
column. You'd then be unable to use the same fields for updating the
data, though, unless you got fancy and gave each field except the
difference column references to both the table and the view. You'd still
have to play fancy games with the Table option (or, more likely, the nT
key strokes, where n is a digit).

If it is any consolation, you can't do inter-field queries in I4GL any
more readily.

As someone else suggested, if you are dealing with raw SQL, you need to
treat the difference as an interval. Given the example screen, something
like:

SELECT * FROM cdr_delay_crones WHERE (h_actual - h_inicio) >
INTERVAL(30:00) MINUTE TO SECOND;


--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"

Messages In This Thread

• SQL QUERY - TIME
  Valeriano Villegas Juan -- Thursday, 25 November 2004, at 11:40 a.m.
  • Re: SQL QUERY - TIME
    JHAYS2@sears.com -- Friday, 26 November 2004, at 11:12 a.m.
  • Re: SQL QUERY - TIME
    Jonathan Leffler -- Monday, 29 November 2004, at 1:13 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.