CODEWITHSUNDEEP
sqloracle-databasenumbers
DEVLOGIN
DEVLOGIN

Reputation: 87

Oracle SQL invalid number on toad

I have made this query but when I execute it, I got error about "invalid number". But in SQL Developer for Oracle, there is no error; I got the result that I want but in Toad I got 'Invalid Number' . 

DECLARE v_rep number;

BEGIN
   EXECUTE IMMEDIATE
     'SELECT to_number(REPLACE(max(substr(to_char(r_timestamp_arr,''HH24:MI''),1,2) ||
        ltrim(to_char(round(to_number(Substr(to_char(r_timestamp_arr, ''HH24:MI''),4,2)) /
        60,2),''.00''))), ''.'', '',''))  -
        to_number(REPLACE(MIN(substr(to_char(r_timestamp_arr,''HH24:MI''),1,2) ||
        ltrim(to_char(round(to_number(Substr(to_char(r_timestamp_arr, ''HH24:MI''),4,2)) / 
        60,2),''.00''))), ''.'', '','')) 
     FROM TV_MAX
     WHERE TV_UID = ''7a87e8e4861a4d0aae65da1a7248b256''' 
     INTO v_rep;
     END ;

Upvotes: 0

Views: 411

Answers (2)

MT0
MT0

Reputation: 167962

You don't need EXECUTE IMMEDIATE and don't need to use strings:

Oracle Setup:

CREATE TABLE tv_max ( tv_uid, r_timestamp_arr ) AS
SELECT '7a87e8e4861a4d0aae65da1a7248b256', DATE '2019-12-27' + INTERVAL '00:00' HOUR TO MINUTE FROM DUAL UNION ALL
SELECT '7a87e8e4861a4d0aae65da1a7248b256', DATE '2019-12-27' + INTERVAL '01:30' HOUR TO MINUTE FROM DUAL;

Query 1:

If you want to ignore the date component of the date & time:

DECLARE
  v_rep NUMBER;
BEGIN
  SELECT ( MAX( r_timestamp_arr - TRUNC( r_timestamp_arr ) )
         - MIN( r_timestamp_arr - TRUNC( r_timestamp_arr ) )
         ) * 24
  INTO   v_rep
  FROM   tv_max
  WHERE  TV_UID = '7a87e8e4861a4d0aae65da1a7248b256';

  DBMS_OUTPUT.PUT_LINE( v_rep );
END;
/

Query 2:

If you want the min/max respecting the date component then the query can be even simpler:

DECLARE
  v_rep NUMBER;
BEGIN
  SELECT ( MAX( r_timestamp_arr ) - MIN( r_timestamp_arr ) ) * 24
  INTO   v_rep
  FROM   tv_max
  WHERE  TV_UID = '7a87e8e4861a4d0aae65da1a7248b256';

  DBMS_OUTPUT.PUT_LINE( v_rep );
END;
/

Output:

For the test data, both output:

1.5

db<>fiddle here

Upvotes: 3

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Looks like You want to know the difference between max and min hour (including minutes, excluding seconds), date part truncated. So take truncated times, subtract as dates, you will get result in days, multiply by 24, result will be in hours. Query does not depend on NLS settings:

select 24 * (to_date(max(to_char(r_timestamp_arr, 'hh24:mi')), 'hh24:mi') 
           - to_date(min(to_char(r_timestamp_arr, 'hh24:mi')), 'hh24:mi')) as diff
  from tv_max
  where tv_uid = '7a87e8e4861a4d0aae65da1a7248b256'

dbfiddle

Upvotes: 1

Related Questions