I am attempting to make the following query for a crystal report using my oracle database. I have replaced the oracle variables with string literals for testing in SQL Developer.
Here is the code with no changes (probably not useful):
SELECT
CASE
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 0 AND 360
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') - 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 360 AND 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT > 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 18/24
END AS SHIFT_START,
CASE
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 0 AND 360
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 360 AND 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 18/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT > 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 30/24
END AS SHIFT_END,
VW_RS_AVLPLUS.REPORT_DATE,
VW_RS_AVLPLUS.REPORT_TIME,
VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT,
VW_RS_AVLPLUS.REPORT_DOW,
VW_RS_AVLPLUS.TIMESTAMP,
VW_RS_AVLPLUS.VEHICLE_STATUS,
VW_RS_AVLPLUS.RADIO_STATUS,
VW_RS_AVLPLUS.NETWORK_STATUS,
VW_RS_AVLPLUS.VEHICLE_NAME,
VW_RS_AVLPLUS.BEAT_NAME,
VW_RS_AVLPLUS.DRIVER_NAME,
VW_RS_AVLPLUS.PROXIMITY,
VW_RS_AVLPLUS.SPEED,
VW_RS_AVLPLUS.IS_ROADRANGER,
VW_RS_AVLPLUS.IS_SIRV,
VW_RS_AVLPLUS.VEHICLETYPE_NAME,
VW_RS_AVLPLUS.VEHICLE_PHONE,
VW_RS_AVLPLUS.VEHICLE_RADIO_NAME,
VW_RS_AVLPLUS.RESPONDER_AGENCY_ID,
VW_RS_AVLPLUS.RESPONDER_AGENCY_SHORT_NAME,
VW_RS_AVLPLUS.RESPONDER_AGENCY_LONG_NAME,
VW_RS_AVLPLUS.VEHICLESTATUS_CLASSIFICATION,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_INSERVICE,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_ASSISTING,
VW_RS_AVLPLUS.VEHICLESTATUS_CANBE_DISPATCHED,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_BILLABLE,
VW_RS_AVLPLUS.REFERENCE_LOCATION,
VW_RS_AVLPLUS.REF_LOCATION_LONG_NAME,
VW_RS_AVLPLUS.ROADWAY_SHORT_NAME,
VW_RS_AVLPLUS.EVENT,
VW_RS_AVLPLUS.EVENT_ID,
VW_RS_AVLPLUS.REPORT_EVENT_DATE,
VW_RS_AVLPLUS.REPORT_EVENT_TIME,
VW_RS_AVLPLUS.REPORT_EVENT_MIDNIGHT_MNT,
VW_RS_AVLPLUS.REPORT_EVENT_DOW,
VW_RS_AVLPLUS.EVENTTYPE_ID,
VW_RS_AVLPLUS.EVENTTYPE_SHORT_NAME,
VW_RS_AVLPLUS.EVENTTYPE_LONG_NAME,
VW_RS_AVLPLUS.EVENTTYPE_IS_PERFMEASD,
VW_RS_AVLPLUS.EVENTTYPE_IS_INCIDENT,
VW_RS_AVLPLUS.EVENTTYPE_IS_AMBER,
VW_RS_AVLPLUS.LATITUDE,
VW_RS_AVLPLUS.LONGITUDE,
VW_RS_AVLPLUS.TRACK,
VW_RS_AVLPLUS.REPORT_DATE_PREV,
VW_RS_AVLPLUS.PREV_IS_BILLABLE,
VW_RS_AVLPLUS.REPORT_DATE_NEXT,
VW_RS_AVLPLUS.NEXT_IS_BILLABLE
FROM VW_RS_AVLPLUS
WHERE
--Is road ranger
VW_RS_AVLPLUS.IS_ROADRANGER = 1
AND
--Event type filter
(
CASE
WHEN VW_RS_AVLPLUS.EVENTTYPE_ID NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN VW_RS_AVLPLUS.EVENTTYPE_ID = '{?pf_ep_eventtype_id}'
THEN 1
ELSE 0
END
ELSE 1
END) = 1
AND
--Day of week filter
(
CASE
WHEN '{?pf_dt_dow_sun}' = '1'
OR '{?pf_dt_dow_mon}' = '2'
OR '{?pf_dt_dow_tue}' = '3'
OR '{?pf_dt_dow_wed}' = '4'
OR '{?pf_dt_dow_thu}' = '5'
OR '{?pf_dt_dow_fri}' = '6'
OR '{?pf_dt_dow_sat}' = '7'
THEN
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DOW IN ('{?pf_dt_dow_sun}','{?pf_dt_dow_mon}','{?pf_dt_dow_tue}','{?pf_dt_dow_wed}','{?pf_dt_dow_thu}','{?pf_dt_dow_fri}', '{?pf_dt_dow_sat}'))
THEN 1
ELSE 0
END
ELSE 1
END) = 1
AND
--Date Filter
(
CASE
WHEN UPPER('{?pf_dt_from_datetime}') NOT IN (NULL, ' ', 'PF_NA')
AND UPPER('{?pf_dt_to_datetime}') NOT IN (NULL, ' ', 'PF_NA')
THEN (
CASE
WHEN ('{?pf_dt_per_day_basis_checked}' = '1')
THEN
-- Filter by date and time of day ranges (separately).
(
CASE
WHEN ((VW_RS_AVLPLUS.REPORT_DATE BETWEEN DATE( TO_DATE('{?pf_dt_from_datetime}', 'MM/DD/YYYY HH24:MI')) AND ADDTIME(DATE( TO_DATE('{?pf_dt_to_datetime}', 'MM/DD/YYYY HH24:MI')),'23:59:59.999999'))
AND (VW_RS_AVLPLUS.REPORT_TIME BETWEEN TIME( TO_DATE('{?pf_dt_from_datetime}', 'MM/DD/YYYY HH24:MI')) AND TIME( TO_DATE('{?pf_dt_to_datetime}', 'MM/DD/YYYY HH24:MI'))))
THEN 1
ELSE 0
END)
ELSE
-- Datetime range.
(
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DATE BETWEEN TO_DATE('{?pf_dt_from_datetime}', 'MM/DD/YYYY HH24:MI') AND TO_DATE('{?pf_dt_to_datetime}', 'MM/DD/YYYY HH24:MI'))
THEN 1
ELSE 0
END)
END)
WHEN (UPPER('{?pf_dt_from_datetime}') NOT IN (NULL, ' ', 'PF_NA'))
THEN
-- Single day.
(
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DATE BETWEEN DATE( TO_DATE('{?pf_dt_from_datetime}', 'MM/DD/YYYY HH24:MI')) AND ADDTIME( DATE( TO_DATE('{?pf_dt_from_datetime}', 'MM/DD/YYYY HH24:MI')),'23:59:59.999999'))
THEN 1
ELSE 0
END)
ELSE 1
END) = 1
AND
--Event ID Filter
(
CASE
WHEN UPPER('{?pf_er_from_event_id}') NOT IN (NULL, ' ', 'PF_NA')
AND UPPER('{?pf_er_to_event_id}') NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN (VW_RS_AVLPLUS.EVENT_ID BETWEEN ToNumber('{?pf_er_from_event_id}') AND ToNumber('{?pf_er_to_event_id}')
THEN 1
ELSE 0
END
WHEN UPPER('{?pf_er_from_event_id}') NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN (VW_RS_AVLPLUS.EVENT_ID = ToNumber('{?pf_er_from_event_id}'))
THEN 1
ELSE 0
END
ELSE 1
END) = 1
ORDER BY Report_Date ASC
Here is the code that I am running in SQL Developer with crystal variables replaced:
SELECT
CASE
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 0 AND 360
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') - 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 360 AND 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT > 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 18/24
END AS SHIFT_START,
CASE
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 0 AND 360
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 6/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT BETWEEN 360 AND 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 18/24
WHEN VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT > 1080
THEN TO_DATE(TO_CHAR(VW_RS_AVLPLUS.REPORT_DATE, 'mm/dd/yyyy')
|| ' 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 30/24
END AS SHIFT_END,
VW_RS_AVLPLUS.REPORT_DATE,
VW_RS_AVLPLUS.REPORT_TIME,
VW_RS_AVLPLUS.REPORT_MIDNIGHT_MNT,
VW_RS_AVLPLUS.REPORT_DOW,
VW_RS_AVLPLUS.TIMESTAMP,
VW_RS_AVLPLUS.VEHICLE_STATUS,
VW_RS_AVLPLUS.RADIO_STATUS,
VW_RS_AVLPLUS.NETWORK_STATUS,
VW_RS_AVLPLUS.VEHICLE_NAME,
VW_RS_AVLPLUS.BEAT_NAME,
VW_RS_AVLPLUS.DRIVER_NAME,
VW_RS_AVLPLUS.PROXIMITY,
VW_RS_AVLPLUS.SPEED,
VW_RS_AVLPLUS.IS_ROADRANGER,
VW_RS_AVLPLUS.IS_SIRV,
VW_RS_AVLPLUS.VEHICLETYPE_NAME,
VW_RS_AVLPLUS.VEHICLE_PHONE,
VW_RS_AVLPLUS.VEHICLE_RADIO_NAME,
VW_RS_AVLPLUS.RESPONDER_AGENCY_ID,
VW_RS_AVLPLUS.RESPONDER_AGENCY_SHORT_NAME,
VW_RS_AVLPLUS.RESPONDER_AGENCY_LONG_NAME,
VW_RS_AVLPLUS.VEHICLESTATUS_CLASSIFICATION,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_INSERVICE,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_ASSISTING,
VW_RS_AVLPLUS.VEHICLESTATUS_CANBE_DISPATCHED,
VW_RS_AVLPLUS.VEHICLESTATUS_IS_BILLABLE,
VW_RS_AVLPLUS.REFERENCE_LOCATION,
VW_RS_AVLPLUS.REF_LOCATION_LONG_NAME,
VW_RS_AVLPLUS.ROADWAY_SHORT_NAME,
VW_RS_AVLPLUS.EVENT,
VW_RS_AVLPLUS.EVENT_ID,
VW_RS_AVLPLUS.REPORT_EVENT_DATE,
VW_RS_AVLPLUS.REPORT_EVENT_TIME,
VW_RS_AVLPLUS.REPORT_EVENT_MIDNIGHT_MNT,
VW_RS_AVLPLUS.REPORT_EVENT_DOW,
VW_RS_AVLPLUS.EVENTTYPE_ID,
VW_RS_AVLPLUS.EVENTTYPE_SHORT_NAME,
VW_RS_AVLPLUS.EVENTTYPE_LONG_NAME,
VW_RS_AVLPLUS.EVENTTYPE_IS_PERFMEASD,
VW_RS_AVLPLUS.EVENTTYPE_IS_INCIDENT,
VW_RS_AVLPLUS.EVENTTYPE_IS_AMBER,
VW_RS_AVLPLUS.LATITUDE,
VW_RS_AVLPLUS.LONGITUDE,
VW_RS_AVLPLUS.TRACK,
VW_RS_AVLPLUS.REPORT_DATE_PREV,
VW_RS_AVLPLUS.PREV_IS_BILLABLE,
VW_RS_AVLPLUS.REPORT_DATE_NEXT,
VW_RS_AVLPLUS.NEXT_IS_BILLABLE
FROM VW_RS_AVLPLUS
WHERE
--Is road ranger
VW_RS_AVLPLUS.IS_ROADRANGER = 1
AND
--Event type filter
(
CASE
WHEN VW_RS_AVLPLUS.EVENTTYPE_ID NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN VW_RS_AVLPLUS.EVENTTYPE_ID = '1'
THEN 1
ELSE 0
END
ELSE 1
END) = 1
AND
--Day of week filter
(
CASE
WHEN ' ' = '1'
OR ' ' = '2'
OR ' ' = '3'
OR ' ' = '4'
OR ' ' = '5'
OR ' ' = '6'
OR '7' = '7'
THEN
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DOW IN (' ',' ',' ',' ',' ',' ', '7'))
THEN 1
ELSE 0
END
ELSE 1
END) = 1
AND
--Date Filter
(
CASE
WHEN UPPER('01/01/2010 12:00') NOT IN (NULL, ' ', 'PF_NA')
AND UPPER('01/01/2010 16:00') NOT IN (NULL, ' ', 'PF_NA')
THEN (
CASE
WHEN ('1' = '1')
THEN
-- Filter by date and time of day ranges (separately).
(
CASE
WHEN ((VW_RS_AVLPLUS.REPORT_DATE BETWEEN DATE( TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI')) AND ADDTIME(DATE( TO_DATE('01/01/2010 16:00', 'MM/DD/YYYY HH24:MI')),'23:59:59.999999'))
AND (VW_RS_AVLPLUS.REPORT_TIME BETWEEN TIME( TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI')) AND TIME( TO_DATE('01/01/2010 16:00', 'MM/DD/YYYY HH24:MI'))))
THEN 1
ELSE 0
END)
ELSE
-- Datetime range.
(
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DATE BETWEEN TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI') AND TO_DATE('01/01/2010 16:00', 'MM/DD/YYYY HH24:MI'))
THEN 1
ELSE 0
END)
END)
WHEN (UPPER('01/01/2010 12:00') NOT IN (NULL, ' ', 'PF_NA'))
THEN
-- Single day.
(
CASE
WHEN (VW_RS_AVLPLUS.REPORT_DATE BETWEEN DATE( TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI')) AND ADDTIME( DATE( TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI')),'23:59:59.999999'))
THEN 1
ELSE 0
END)
ELSE 1
END) = 1
AND
--Event ID Filter
(
CASE
WHEN UPPER('0') NOT IN (NULL, ' ', 'PF_NA')
AND UPPER('1000') NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN (VW_RS_AVLPLUS.EVENT_ID BETWEEN ToNumber('0') AND ToNumber('1000')
THEN 1
ELSE 0
END
WHEN UPPER('0') NOT IN (NULL, ' ', 'PF_NA')
THEN
CASE
WHEN (VW_RS_AVLPLUS.EVENT_ID = ToNumber('0'))
THEN 1
ELSE 0
END
ELSE 1
END) = 1
ORDER BY Report_Date ASC
When I run this query on my DB I get ORA-00936: missing expression on line 121 column 45 which is right after VW_RS_AVLPLUS.REPORT_DATE on the following line.
WHEN ((VW_RS_AVLPLUS.REPORT_DATE BETWEEN DATE( TO_DATE('01/01/2010 12:00', 'MM/DD/YYYY HH24:MI')) AND ADDTIME(DATE( TO_DATE('01/01/2010 16:00', 'MM/DD/YYYY HH24:MI')),'23:59:59.999999'))
Please help me identify my error and any others that may be hiding.
As a helpful note, in my database it seems that not all PL/SQL constructs are available to me. I'm not sure why, but I'm fairly sure I have avoided those constructs that do not work with my DB.
Oracle DBMS Info:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
"CORE 11.2.0.1.0 Production"
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production