Hi All
i have the following table for creating and inserting i included only two fileds as these are the fields that i'm getting errors on.
WITH sample_data AS
(
SELECT '50709' AS start_date, '2400' AS start_time FROM dual UNION ALL
SELECT '082510', '0000' FROM dual
)
SELECT *
FROM sample_data
;
DATA:
START_DATE START_TIME
50709 2400
082510 0000
And now when i run the query against the table:
SELECT direction, car_count_pos, truck_count_pos,
(car_count_pos + 2 * (truck_count_pos)) AS adjusted, car_count_neg,
truck_count_neg,
(car_count_neg + 2 * (truck_count_neg)) AS adjusted_neg,
TO_DATE (start_date, 'MMDDYY') AS start_date,
TO_DATE (end_date, 'MMDDYY') AS end_date,
ROUND (TO_CHAR (TO_DATE (LPAD (start_time, 4, '0'), 'HH24MI'),
'HH24.MI'
)
) AS start_time,
start_time,
(TO_DATE (end_date, 'MMDDYY') - TO_DATE (start_date, 'MMDDYY')
) AS days
FROM (SELECT (CASE
WHEN t1.direction = '1'
THEN ( COUNT (t1.bin_1_data)
+ COUNT (t1.bin_2_data)
+ COUNT (t1.bin_3_data) * 0.981 * 1.019
)
ELSE NULL
END
) AS car_count_pos,
t1.direction,
(CASE
WHEN t1.direction = '1'
THEN ( COUNT (t1.bin_4_data)
+ COUNT (t1.bin_5_data)
+ COUNT (t1.bin_6_data)
+ COUNT (t1.bin_7_data)
+ COUNT (t1.bin_8_data)
+ COUNT (t1.bin_9_data)
+ COUNT (t1.bin_10_data)
+ COUNT (t1.bin_11_data)
+ COUNT (t1.bin_12_data)
+ COUNT (t1.bin_13_data)
+ COUNT (t1.bin_14_data)
+ COUNT (t1.bin_15_data) * 0.981 * 1.019
)
ELSE NULL
END
) AS truck_count_pos,
(CASE
WHEN t1.direction = '3'
THEN ( COUNT (t1.bin_1_data)
+ COUNT (t1.bin_2_data)
+ COUNT (t1.bin_3_data) * 0.981 * 1.019
)
ELSE NULL
END
) AS car_count_neg,
(CASE
WHEN t1.direction = '3'
THEN ( COUNT (t1.bin_4_data)
+ COUNT (t1.bin_5_data)
+ COUNT (t1.bin_6_data)
+ COUNT (t1.bin_7_data)
+ COUNT (t1.bin_8_data)
+ COUNT (t1.bin_9_data)
+ COUNT (t1.bin_10_data)
+ COUNT (t1.bin_11_data)
+ COUNT (t1.bin_12_data)
+ COUNT (t1.bin_13_data)
+ COUNT (t1.bin_14_data)
+ COUNT (t1.bin_15_data) * 0.981 * 1.019
)
ELSE NULL
END
) AS truck_count_neg,
t2.start_date, t2.start_time, t2.end_date, t2.end_time
FROM bin_data t1, traffic_sample t2
WHERE t1.traffic_sample_id = t2.traffic_sample_id
GROUP BY t1.direction,
t2.start_date,
t2.start_time,
t2.end_date,
t2.end_time)
WHERE direction IN ('1', '3')
GROUP BY TO_DATE (start_date, 'MMDDYY'),
direction,
car_count_pos,
truck_count_pos,
(car_count_pos + 2 * (truck_count_pos)),
truck_count_neg,
(car_count_neg + 2 * (truck_count_neg)),
TO_DATE (end_date, 'MMDDYY'),
ROUND (TO_CHAR (TO_DATE (LPAD (start_time, 4, '0'), 'HH24MI'),
'HH24.MI'
)
),
start_time,
car_count_neg
I'm getting the error
ORA-1850: Hour must be between 0 and 23
But as you see there is a value 2400 in the table is there any way to get around this? please need help.
Thanks
Edited by: thinkingeye on Aug 26, 2010 8:42 AM