I would like to change a sysdate parameter in the App.config file. I am passing a parameter (:ADHERENCEDAY) to my query in the app.config file. I am getting an error message at the cmdAdherence.ExecuteReader() line. "Oracle.ManagedDataAccess.Client.OracleException: 'ORA-01867: the interval is invalid. Does anyone know the proper way to do this?"
App.Config
<add key ="ADHERENCEDAY" value = "1"/>
<add key ="QUERY_ADHERENCE_TABLE" value ="Select ADHERENCE_ID,CALENDAR_ID,TIME_TABLE_VERSION_ID,ROUTE_ID,ROUTE_DIRECTION_ID,PATTERN_ID,GEO_NODE_ID,OPERATOR_ID,RUN_ID,WORK_PIECE_ID,VEHICLE_ID,BLOCK_ID,TRIP_ID,SERVICE_TYPE_ID,TRANSIT_DIVISION_ID,TIME_POINT_ID,SCHED_ADHERE_WAIVER_ID,REVENUE_ID,TIME_OF_DAY_ID,VEHICLE_BASE_ID,IS_LAYOVER,BLOCK_STOP_ORDER,MESSAGE_TIME,SCHEDULED_TIME,SCHED_DIST_FROM_LAST_GEO_NODE,ADHERENCE,ACTUAL_ARRIVAL_TIME,ACTUAL_DEPARTURE_TIME,SCHEDULED_TIME_OFFSET,ODOMETER,VALIDITY,EARLY_COUNT,ONTIME_COUNT,LATE_COUNT,MISSING_COUNT,ADJUSTED_EARLY_COUNT,ADJUSTED_ONTIME_COUNT,ADJUSTED_LATE_COUNT,ADJUSTED_MISSING_COUNT,EARLY_WAIVED_TP,LATE_WAIVED_TP,LAYOVER_EARLY_ALLOWED,LAYOVER_LATE_ALLOWED,EARLY_WAIVED_PT,OVERLOAD_ID,IS_BATCHSTORAGE,IS_VEHICLE_STOPPED,IS_DOOR_OPENED,FIRST_LOC_STOP_TIME,FIRST_DOOR_OPEN_TIME,CLOSEST_LOC_TIME,LAST_DOOR_CLOSE_TIME,LAST_LOC_START_TIME,PATTERN_GEO_NODE_SEQ,MISSING_WAIVED_TP,SCHEDULED_TRAVEL_TIME,ACTUAL_TRAVEL_TIME,RECOVERY_TIME,ROUTE_STOP_SEQUENCE,TRIP_EDGE,CROSSING_TYPE_ID,TIME_POINT_INTERVAL_ID,GEO_NODE_INTERVAL_ID
From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ':ADHERENCEDAY' day, 'YYYYMMDD'))"/>
string strQueryRows = ConfigurationManager.AppSettings["QUERY_ADHERENCE_TABLE"].ToString();
OracleCommand cmdAdherence = new OracleCommand(strQueryRows,con);
cmdAdherence.Parameters.Add(new OracleParameter("ADHERENCEDAY", Convert.ToInt32(ConfigurationManager.AppSettings["ADHERENCEDAY"])));
OracleDataReader readAdherence = cmdAdherence.ExecuteReader();
What I have tried:
I have tried the following options.
From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ':ADHERENCEDAY' day, 'YYYYMMDD'))"/>
<pre>From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL 'ADHERENCEDAY' day, 'YYYYMMDD'))"/>
<pre>From TMDM.Adherence where CALENDAR_ID >= ( 1||to_Char( sysdate - INTERVAL ADHERENCEDAY day, 'YYYYMMDD'))"/>