problems in composing a sql statment

Hello... I don't have much experience with Informix database and I'm trying to query it: my main problem is that I need to do manipulation on the date In the query I need to know the number of seconds past between 2 dates. I know that when doing an operation of extraction between 2 DATETIME fields the result is an INTERVAL but I don't know how to make it be a number of seconds and how to round it to be an integer. Help.... this is the query: SELECT Left$(c.callstart,19) as ts, (ROUND(24 * 3600 * (a.ringstart - c.callstart)) + a.talkstart) as waiting_time FROM agentrecord a, callrecord c WHERE a.callid = c.callid and c.origdestination like '3275' and Left$(c.callstart,19) > '1970-01-01 00:00:00' order by ts) and this is the error message I recieve: [[Informix][Informix ODBC Driver][Informix]Routine (left$) can not be resolved. (HR: 0x80004005) (Unspecified error) (Source = Microsoft OLE DB Provider for ODBC Drivers)][(Error #80004005) (Source = Microsoft OLE DB Provider for ODBC Drivers) (Description = [Informix][Informix ODBC Driver][Informix]Routine (left$) can not be resolved. ) (SQLState = S1000) (NativeError: fffffd5e)] I will be so happy if anyone can help me because I'm trying to look for an answer in the manuals with no luck... thanks, Yael.

What does left$ do? The engine is complaining that there isn't a function called left$ Yael wrote: > > Hello... > I don't have much experience with Informix database and I'm trying > to query it: my main problem is that I need to do manipulation on the > date > In the query I need to know the number of seconds past between 2 > dates. > I know that when doing an operation of extraction between 2 DATETIME > fields > the result is an INTERVAL but I don't know how to make it be a number > of seconds and how to round it to be an integer. > > Help.... > > this is the query: > SELECT > > Left$(c.callstart,19) as ts, > (ROUND(24 * 3600 * (a.ringstart - c.callstart)) + a.talkstart) as > waiting_time > > FROM > agentrecord a, callrecord c WHERE a.callid = c.callid > and c.origdestination like '3275' > and Left$(c.callstart,19) > '1970-01-01 00:00:00' > order by ts) > > and this is the error message I recieve: > > [[Informix][Informix ODBC Driver][Informix]Routine (left$) can not be > resolved. (HR: 0x80004005) (Unspecified error) (Source = Microsoft > OLE DB Provider for ODBC Drivers)][(Error #80004005) (Source = > Microsoft OLE DB Provider for ODBC Drivers) (Description = > [Informix][Informix ODBC Driver][Informix]Routine (left$) can not be > resolved. ) (SQLState = S1000) (NativeError: fffffd5e)] > > I will be so happy if anyone can help me because I'm trying to look > for an answer in the manuals with no luck... > > thanks, > Yael. -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #

Yael wrote: > Hello... > I don't have much experience with Informix database and I'm trying > to query it: my main problem is that I need to do manipulation on the > date > In the query I need to know the number of seconds past between 2 > dates. > I know that when doing an operation of extraction between 2 DATETIME > fields > the result is an INTERVAL but I don't know how to make it be a number > of seconds and how to round it to be an integer. > > Help.... > > this is the query: > SELECT > > Left$(c.callstart,19) as ts, > (ROUND(24 * 3600 * (a.ringstart - c.callstart)) + a.talkstart) as > waiting_time > > FROM > agentrecord a, callrecord c WHERE a.callid = c.callid > and c.origdestination like '3275' > and Left$(c.callstart,19) > '1970-01-01 00:00:00' > order by ts) > > and this is the error message I recieve: [error clipped] > > I will be so happy if anyone can help me because I'm trying to look > for an answer in the manuals with no luck... I'm guessing at the relationship between the fields ringstart, callstart, and talkstart, but give the following a try: SELECT c.callstart AS ts, (interval(0) second(3) to second + (a.ringstart - c.callstart)) + (interval(0) second(3) to second + (a.talkstart - a.ringstart)) ASwaiting_time FROM agentrecord a, callrecord c WHERE a.callid = c.callid AND c.origdestination like '3275' AND c.callstart > '1970-01-01 00:00:00' ORDER BY ts; See the Informix SQL Reference for information on INTERVAL. In the example above, I used a small precision value (3) for seconds. You may want to bump that up, depending on the number of seconds that may result based on your data. As I said, I'm just guessing at the meaning behind your fields, but wouldn't the following also give you what you are looking for: SELECT c.callstart AS ts, interval(0) second(3) to second + (a.talkstart - c.callstart) ASwaiting_time FROM agentrecord a, callrecord c WHERE a.callid = c.callid AND c.origdestination like '3275' AND c.callstart > '1970-01-01 00:00:00' ORDER BY ts; -- June Hunt

Yael wrote: > I don't have much experience with Informix database and I'm trying > to query it: my main problem is that I need to do manipulation on > the date In the query I need to know the number of seconds past > between 2 dates. I know that when doing an operation of extraction > between 2 DATETIME fields the result is an INTERVAL but I don't > know how to make it be a number of seconds and how to round it to > be an integer. You need to know how to coerce the difference between two DATETIME values into a given number of seconds. It worries me that you think you might have any data more than 30 years old, as implied by the comparison. I'll come back to why in a bit. You can do it one expression, but for clarity, I'd use a stored procedure. It also isn't clear what the type of ringstart is; given your description, it is presumably DATETIME YEAR TO FRACTION(n) for some value 0 < n < 6. So, first we get an interval in seconds and fractions of a second: INTERVAL(0) SECOND(9) TO FRACTION(n) + (a.ringstart - c.callstart) Now, given such a value, x, you can convert it to a string: CAST(x AS VARCHAR) and given such a string, y, you can convert it to a DECIMAL(n): CAST(y AS DECIMAL(n)) and given such a decimal, z, you can round that with the ROUND function: ROUND(z, 0) yielding the unwieldy expression: ROUND(CAST(CAST(INTERVAL(0) SECOND(9) TO FRACTION(n) + (a.ringstart - c.callstart) AS VARCHAR) AS DECIMAL(n), 0) I reserve the right to be told that the VARCHAR cast needs a size - that would presumably be VARCHAR(11+n), where you'd have to determine a single integer and place that in the code, rather than using an expression. Note, too, that the CAST notation is part of IDS 9.x and not earlier server versions. OK - converting that into an stored procedure (for clarity): CREATE PROCEDURE seconds_between(d1 DATETIME YEAR TO FRACTION(5), d2 DATETIME YEAR TO FRACTION(5)) RETURNING DECIMAL(14,5) { AS nsecs -- in 9.40 or later }; DEFINE i INTERVAL SECOND(9) TO SECOND; DEFINE s VARCHAR(16); LET i = d1 - d2; LET s = i; -- This is the critical step! RETURN s; END PROCEDURE; There are other, more compact ways of writing that. The code in that stored procedure is portable to OnLine and SE and XPS and IDS 7.x as well as IDS 9.x. OK, and why does the 30+ year old data worry me? Because there are more than 1 (US) billion seconds between current dates and 1970. So, that number of seconds won't fit into an INTERVAL SECOND(9) TO SECOND or the fractional variants on that. So, if any of your intervals might be anything like 30 years long, the code above is too simplistic. You'd need to go the IIUG Software archive (http://www.iiug.org/software in the 'Miscellaneous section') or to Google to find a stored procedure called to_unix_time(). Version 1.6 is available at the IIUG - recommended - and v1.3 on Google (passable, unless you have date/time values with fractional seconds. It deals with the overflow problem. > Help.... > > this is the query: > SELECT > > Left$(c.callstart,19) as ts, As other people pointed out, LEFT$ isn't part of Informix either - it is probably equivalent to c.callstart[1,19], which returns the first 19 characters of c.callstart when treated as a string. If that won't work, you may have to use the SUBSTR() function instead - which will coerce the column into a character string. If your server doesn't support SUBSTR(), it is probably overdue for an upgrade - unless it is OnLine 5.20 or SE 7.25 (which don't support SUBSTR). > (ROUND(24 * 3600 * (a.ringstart - c.callstart)) + a.talkstart) as > waiting_time > > FROM > agentrecord a, callrecord c WHERE a.callid = c.callid > and c.origdestination like '3275' There are no metacharacters in the RHS of the LIKE operand - don't use LIKE when you mean '='. > and Left$(c.callstart,19) > '1970-01-01 00:00:00' Better: EXTEND(c.callstart, YEAR TO SECOND) > DATETIME(1970-01-01 00:00:00) YEAR TO SECOND > order by ts) I think this close parenthesis is wrong; I can't see a matching open parenthesis. > and this is the error message I recieve: > > [[Informix][Informix ODBC Driver][Informix]Routine (left$) can not be > resolved. (HR: 0x80004005) (Unspecified error) (Source = Microsoft > OLE DB Provider for ODBC Drivers)][(Error #80004005) (Source = > Microsoft OLE DB Provider for ODBC Drivers) (Description = > [Informix][Informix ODBC Driver][Informix]Routine (left$) can not be > resolved. ) (SQLState = S1000) (NativeError: fffffd5e)] > > I will be so happy if anyone can help me because I'm trying to look > for an answer in the manuals with no luck... Google and the IIUG Software archives are your friends! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/