All Forums Database
gskaushik 56 posts Joined 09/10
22 Sep 2015
Issue while converting float to time and inserting to table

Hi
 
I ran on an issue where I am trying to insert the values from DBC.RESUSAGESPMA to a volatile table, but when doing it I am getting an 
Error - 5407:  Invalid operation for DateTime or Interval. 
Database Version - 14.0
I ran the below code in BTEQ
Code as below

CREATE VOLATILE TABLE TEMP2
(
rumtime time,
CPUVAL integer
)
ON COMMIT PRESERVE ROWS;

INSERT INTO TEMP2
SELECT 
CAST(THETIME AS FORMAT '99:99:99.99')  ,
((SUM(CPUUEXEC)+SUM(CPUUSERV)) / (SUM(CPUIOWAIT+CPUUEXEC+CPUUSERV+CPUIDLE)) ) *100 
FROM DBC.RESUSAGESPMA
WHERE THEDATE = DATE AND
THETIME BETWEEN  CAST(TIME-'3:00:00' AS TIME) AND TIME
GROUP BY THETIME;

 
 

Regards Subramanian kaushik Gurumoorthy
CarlosAL 512 posts Joined 04/08
22 Sep 2015

Hi.
TheTime is FLOAT and you are comparing it with TIME in the BETWEEN clause.
You must CAST it to TIME first.
Also, you must CAST '03:00:00' to INTERVAL TYPE.
HTH.
Cheers.
Carlos.
 
 

gskaushik 56 posts Joined 09/10
22 Sep 2015

Hi Carlosal,
 
Thanks for your reply, but I think there is some issue with the site , I am not able to see your reply.
Kindly do help in replying back again.

Regards
Subramanian kaushik Gurumoorthy

gskaushik 56 posts Joined 09/10
22 Sep 2015

Hi All,
 
Would anyone would eb able to help me on the above query.
Thanks in advance

Regards
Subramanian kaushik Gurumoorthy

dnoeth 4628 posts Joined 11/04
23 Sep 2015

THETIME is defined as a FLOAT (due to historical reasons), same for the built-in TIME function  and you can't mix FLOAT and TIME datatypes.
This should work:

select
   cast(trim(THETIME) AS time) as Time_, 
   CPUUsage
from
 (
   SELECT
   THETIME,
   ((SUM(CPUUEXEC)+SUM(CPUUSERV)) / (SUM(CPUIOWAIT+CPUUEXEC+CPUUSERV+CPUIDLE)) ) *100 as CPUUsage
   FROM DBC.RESUSAGESPMA
   WHERE THEDATE = DATE AND
     THETIME BETWEEN  TIME - 3 AND TIME
   GROUP BY THETIME
 ) as dt;

 

Dieter

CarlosAL 512 posts Joined 04/08
23 Sep 2015

Hi.
Bloody drupal...
I was pointing the same CASTing issue as Dieter did already.
However, I think TIME - 3 will substract three seconds from TIME instead the three hours desired by the op.
HTH.
Cheers.
Carlos.
 

gskaushik 56 posts Joined 09/10
23 Sep 2015

Hi Diter and Carlosal,
 
Thanks for your response and in advance for your help.
I have made thechanges and tried  but in where clause there i some typecasting problem
and it shows the same error in BTEQ
is my below query correct.
 

SELECT A.RUNTIME
FROM
(
select
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time)  BETWEEN (time-'3:00:00') AND time
GROUP BY THETIME
) A
ORDER BY RUNTIME;

 
 
 

Regards
Subramanian kaushik Gurumoorthy

dnoeth 4628 posts Joined 11/04
23 Sep 2015

Of course TIME - 3 is wrong, should be TIME - 30000 instead.
 
 
You can't compare the TIME function (FLOAT) with the TIME dataype.
It's either the corrected version of my query or:

cast(trim(THETIME) AS time)  BETWEEN (current_time- interval '3' hour) AND current_time

 

Dieter

gskaushik 56 posts Joined 09/10
23 Sep 2015

Hi Dieter,
 
Thanks a lot for your timely help.
Yes you are right i had compared TIME with FLOAT .
Its working fine now after changing the above code.
 
Nice day ahead
 
 

Regards
Subramanian kaushik Gurumoorthy

gskaushik 56 posts Joined 09/10
23 Sep 2015

Hi,
 
One more dumb question , again I am getting the below issue wrt to type mismatch when I am trying to concatenater a string with time.
 
As per my understanding , I thought it would be handled by an implicit conversion.
 
 

 SELECT 'aaa'||A.RUNTIME 
FROM
(
select 
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time)  BETWEEN (current_time- interval '3' hour) AND current_time
GROUP BY THETIME
) A
ORDER BY RUNTIME;

 
 

Regards
Subramanian kaushik Gurumoorthy

dnoeth 4628 posts Joined 11/04
23 Sep 2015

There's no implicit cast for TIME and TIMESTAMP, so you must do it explicitly:
SELECT 'aaa'||cast(A.RUNTIME as char(8))

Dieter

gskaushik 56 posts Joined 09/10
23 Sep 2015

Hi Dieter,
 
Thanks for your response.
I have tried it now but it gives me the error "Invalid operation for Date time or interval"
 

SELECT 'aaa'||cast(A.RUNTIME as char(8))
FROM
(
select
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time)  BETWEEN (current_time- interval '3' hour) AND current_time
GROUP BY THETIME
) A

 

Regards
Subramanian kaushik Gurumoorthy

gskaushik 56 posts Joined 09/10
23 Sep 2015

Hi 
 
I could find the same working finde with 

SELECT 'aaa'||cast(current_time as char(8))

But with the below query having the 5407 error.

SELECT 'aaa'||cast(A.RUNTIME as char(8))
FROM
(
select
cast(trim(THETIME) AS time) AS RUNTIME
FROM DBC.RESUSAGESPMA
WHERE thedate = DATE and
cast(trim(THETIME) AS time)  BETWEEN (current_time- interval '3' hour) AND current_time
GROUP BY THETIME
) A

 

Regards
Subramanian kaushik Gurumoorthy

Fred 1096 posts Joined 08/04
23 Sep 2015

Are you using ODBC driver to submit the query? If so, is the middle letter of DateTimeFormat set to backward-compatible "I"? Generally you should set DateTimeFormat to either IAA or AAA.
But don't do range comparison with DATE and TIME separately. What if the range spans midnight? And if time zone handling is enabled on your system, that's midnight UTC (GMT) and not local time. Instead, combine date and time into a TIMESTAMP (and compare with CURRENT_TIMESTAMP).
CAST(CAST(CAST(TheDate AS FORMAT 'yyyy-mm-dd') AS CHAR(10))||' '||CAST(THETIME AS CHAR(8)) AS TIMESTAMP)
 
 

gskaushik 56 posts Joined 09/10
24 Sep 2015

Hi Fred ,
 
Thanks a lot for your response.
Let me check with it , I suppose that the above fix is for SQLAw which connects through ODBC.
Can we know if it applies for BTEQ too ?
 
As im finiding the issue is with BTEQ
 
Regarding the second point , yes thats a very valid point , need to remodify the code.
Thanks for pointing out the same.
 
regards,
Subramanian kaushik Gurumoorthy
 

Regards
Subramanian kaushik Gurumoorthy

Fred 1096 posts Joined 08/04
27 Sep 2015

The potential DateTimeFormat issue applies only to ODBC, not CLIv2 (which BTEQ uses) or Teradata.Net or JDBC.

You must sign in to leave a comment.