SQL QUERY - TIME

Posted in 2004

Valeriano Villegas Juan — Thu, 25 Nov 2004 16:40:33 — source: IIUG Forums & Mailing Lists

This is a multi-part message in MIME format. ------_=_NextPart_001_01C4D30C.D2E00BB1 Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01C4D30C.D2E00BB1" ------_=_NextPart_002_01C4D30C.D2E00BB1 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable HI : =20 HOW WILL BE THE QUERY FOR DETECT,=20 MORE THAN 30 MINUTES OF DIFERENCE BETWEEN 2 FIELDS TYPE DATETIME (HOUR TO SECOND) =20 THANKS IN ADVANCE =20 ----------------------------------------------------------------- =20 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] =20 ------_=_NextPart_002_01C4D30C.D2E00BB1 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html> <head> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered)"> <style>  </style> <![if mso 9]> <style> p.MsoNormal {margin-left:37.5pt;} </style> <![endif]> </head> <body bgcolor=3Dwhite background=3D"cid:image001.jpg@01C4D2DA.89432900" = lang=3DES link=3Dblue vlink=3Dpurple style=3D'margin-left:37.5pt'> <div class=3DSection1> HI :   HOW WILL BE THE QUERY FOR DETECT, = MORE THAN 30 MINUTES OF = DIFERENCE BETWEEN 2 FIELDS TYPE DATETIME (HOUR TO = SECOND)   THANKS IN ADVANCE  </sp= an> ----------------------------------------------= -------------------   PERFORM:&n= bsp;  Query  Next  Previous  View  Add  Update  Remove  Table  Screen  ... Changes a row in the active database table.     ** 1: cdr_delay_crones table** usuario&nb= sp;           [cdr       ] pid &= nbsp;           &n= bsp;  [27422 ] ppid =             &= nbsp; [27420 ] c &nb= sp;           &nbs= p;    [13    ] h_inicio&n= bsp;          [10:05:00] terminal&n= bsp;          [?         ] cpu_time&n= bsp;          [0:00      ] <p class=3DMsoNormal style

↪ replying to Valeriano Villegas Juan

JHAYS2@sears.com — Fri, 26 Nov 2004 16:12:36 — source: IIUG Forums & Mailing Lists

Use INTERVAL. See Chap 3 of SQL Reference Manual for more info. An example: DATETIME (12:30:00) HOUR TO SECOND - DATETIME (12:00:00) HOUR TO SECOND Result: INTERVAL (30:00) MINUTE TO SECOND "Valeriano V...." <jvaleriano@ref.pemex.com> Sent by: forum.subscriber@iiug.org 11/25/2004 10:40 AM To: ids@iiug.org cc: Subject: SQL QUERY - TIME [3781] This is a multi-part message in MIME format. ------_=_NextPart_001_01C4D30C.D2E00BB1 Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01C4D30C.D2E00BB1" ------_=_NextPart_002_01C4D30C.D2E00BB1 charset="us-ascii" Content-Transfer-Encoding: quoted-printable HI : =20 HOW WILL BE THE QUERY FOR DETECT,=20 MORE THAN 30 MINUTES OF DIFERENCE BETWEEN 2 FIELDS TYPE DATETIME (HOUR TO SECOND) =20 THANKS IN ADVANCE =20 ----------------------------------------------------------------- =20 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] =20 ------_=_NextPart_002_01C4D30C.D2E00BB1 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable <html> <head> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Dus-ascii"> <meta name=3DGenerator content=3D"Microsoft Word 11 (filtered)"> <style>  </style> <![if mso 9]> <style> p.MsoNormal {margin-left:37.5pt;} </style> <![endif]> </head> <body bgcolor=3Dwhite background=3D"cid:image001.jpg@01C4D2DA.89432900" = lang=3DES link=3Dblue vlink=3Dpurple style=3D'margin-left:37.5pt'> <div class=3DSection1> HI :   HOW WILL BE THE QUERY FOR DETECT, = MORE THAN 30 MINUTES OF = DIFERENCE BETWEEN 2 FIELDS TYPE DATETIME (HOUR TO = SECOND)   THANKS IN ADVANCE  </sp= an> ----------------------------------------------= -------------------   PERFORM:&n= bsp;  Query  Next  Previous  View  Add  Update  Remove  Table  Screen  ... Changes a row in the active database table.     ** 1: cdr_delay_crones table** usuario&nb= sp;           [cdr       ] pid &= nbsp;           &n= bsp;  [27422 ] ppid =             &= nbsp; [27420 ] c &nb= sp;           &nbs= p;    [13    ] h_inicio&n= bsp;          [10:05:00] terminal&n= bsp;          [?         ]

↪ replying to Valeriano Villegas Juan

Jonathan Leffler — Mon, 29 Nov 2004 18:13:27 — source: IIUG Forums & Mailing Lists

"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!"