Issue while converting float to time and inserting to table

Database
Enthusiast

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;
Tags (1)
15 REPLIES
Junior Supporter

Re: Issue while converting float to time and inserting to table

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.

Enthusiast

Re: Issue while converting float to time and inserting to table

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.

Enthusiast

Re: Issue while converting float to time and inserting to table

Hi All,

Would anyone would eb able to help me on the above query.

Thanks in advance

Senior Apprentice

Re: Issue while converting float to time and inserting to table

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;
Junior Supporter

Re: Issue while converting float to time and inserting to table

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.

Enthusiast

Re: Issue while converting float to time and inserting to table

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;
Senior Apprentice

Re: Issue while converting float to time and inserting to table

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
Enthusiast

Re: Issue while converting float to time and inserting to table

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

Enthusiast

Re: Issue while converting float to time and inserting to table

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;