CheGuevara Posted April 22, 2015 Report Posted April 22, 2015 INSERT INTO UWTSMEE (uwtsmee_alert_time) SELECT TO_DATE(SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,16,2),'MM-DD-YYYY HH24:MI:SS') FROM UWTSMDM_EL_EXT bhayya na loader script lo .. manam fetch chesina date field lo . hours data insert avatledu .. jus date okate insert avtundi :3D_Smiles_38: ex # 02-JUL-2014 Use timestamp like this: INSERT INTO UWTSMEE (uwtsmee_alert_time) SELECT TO_timestamp(SUBSTR(uwtsmdm_ext_read_time,5,3)||'-'|| SUBSTR(uwtsmdm_ext_read_time,9,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,25,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,12,8),'MM-DD-YYYY HH24:MI:SS') FROM UWTSMDM_EL_EXT
nenu_devudni Posted April 22, 2015 Author Report Posted April 22, 2015 Use timestamp like this: INSERT INTO UWTSMEE (uwtsmee_alert_time) SELECT TO_timestamp(SUBSTR(uwtsmdm_ext_read_time,5,3)||'-'|| SUBSTR(uwtsmdm_ext_read_time,9,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,25,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,12,8),'MM-DD-YYYY HH24:MI:SS') FROM UWTSMDM_EL_EXT ala cheste ela ostundi bhayya date .. 20-FEB-14 04.32.40.000000000 AM
CheGuevara Posted April 22, 2015 Report Posted April 22, 2015 ala cheste ela ostundi bhayya date .. 20-FEB-14 04.32.40.000000000 AM Do one thing... To_date evi use cheyyakunda ela try chey: NSERT INTO UWTSMEE (uwtsmee_alert_time) SELECT SUBSTR(uwtsmdm_ext_read_time,5,3)||'-'|| SUBSTR(uwtsmdm_ext_read_time,9,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,25,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,12,8) FROM UWTSMDM_EL_EXT
nenu_devudni Posted April 27, 2015 Author Report Posted April 27, 2015 Do one thing... To_date evi use cheyyakunda ela try chey: NSERT INTO UWTSMEE (uwtsmee_alert_time) SELECT SUBSTR(uwtsmdm_ext_read_time,5,3)||'-'|| SUBSTR(uwtsmdm_ext_read_time,9,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,25,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,12,8) FROM UWTSMDM_EL_EXT to_date tesesi .. Select query la run cheste date correct ga vastundi bhayya .. 02-24-2015 03:31:47 but na loader script lo nenu highlight chesina 3 conditions satisfy avatledu bhayya ...data emi load avatledu ... naku telisi aa date field comparision vale potundi .. kasta ekada thapu undo chusi chepuu bhayya .. bye1 INSERT INTO UWTSMEE (uwtsmee_minv_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_cnfg_code, uwtsmee_alert_time, uwtsmee_alert_msg, uwtsmee_alert_type, uwtsmee_value, uwtsmee_state) SELECT SUBSTR(uwtsmdm_ext_meter_model,6,11), SUBSTR(uwtsmdm_ext_meter_model,1,5), 'EL', uwtsmdm_ext_cnfg_code, (SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'| SUBSTR(uwtsmdm_ext_read_time,16,2)), uwtsmdm_ext_alert_msg, uwtsmdm_ext_alert_type, uwtsmdm_ext_value, uwtsmdm_ext_state FROM UWTSMDM_EL_EXT OUTq, ucbsvco, ucrserv WHERE NOT EXISTS (SELECT 1 FROM UWTSMEE WHERE uwtsmee_model||uwtsmee_minv_code = OUTQ.uwtsmdm_ext_meter_model AND uwtsmee_asvc_code = 'EL' AND uwtsmee_cnfg_code = uwtsmdm_ext_cnfg_code AND uwtsmee_alert_msg = uwtsmdm_ext_alert_msg AND uwtsmee_alert_type = uwtsmdm_ext_alert_type) and ucbsvco_prem_code = ucrserv_prem_code and ucbsvco_date_created >= TO_DATE(SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,16,2),'MM/DD/YYYY HH24:MI:SS') - 1 and SUBSTR(uwtsmdm_ext_meter_model,6) = ucrserv_invn_code and ucrserv_styp_code like '%ELC' and uwtsmdm_ext_alert_msg = 'Tilt Warning' and uwtsmdm_ext_alert_type = 'Tamper Alert'; and TO_DATE(SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,16,2),'MM/DD/YYYY HH24:MI:SS') = trunc(sysdate);
CheGuevara Posted April 27, 2015 Report Posted April 27, 2015 to_date tesesi .. Select query la run cheste date correct ga vastundi bhayya .. 02-24-2015 03:31:47 but na loader script lo nenu highlight chesina 3 conditions satisfy avatledu bhayya ...data emi load avatledu ... naku telisi aa date field comparision vale potundi .. kasta ekada thapu undo chusi chepuu bhayya .. bye1 INSERT INTO UWTSMEE (uwtsmee_minv_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_cnfg_code, uwtsmee_alert_time, uwtsmee_alert_msg, uwtsmee_alert_type, uwtsmee_value, uwtsmee_state) SELECT SUBSTR(uwtsmdm_ext_meter_model,6,11), SUBSTR(uwtsmdm_ext_meter_model,1,5), 'EL', uwtsmdm_ext_cnfg_code, (SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'| SUBSTR(uwtsmdm_ext_read_time,16,2)), uwtsmdm_ext_alert_msg, uwtsmdm_ext_alert_type, uwtsmdm_ext_value, uwtsmdm_ext_state FROM UWTSMDM_EL_EXT OUTq, ucbsvco, ucrserv WHERE NOT EXISTS (SELECT 1 FROM UWTSMEE WHERE uwtsmee_model||uwtsmee_minv_code = OUTQ.uwtsmdm_ext_meter_model AND uwtsmee_asvc_code = 'EL' AND uwtsmee_cnfg_code = uwtsmdm_ext_cnfg_code AND uwtsmee_alert_msg = uwtsmdm_ext_alert_msg AND uwtsmee_alert_type = uwtsmdm_ext_alert_type) and ucbsvco_prem_code = ucrserv_prem_code and ucbsvco_date_created >= TO_DATE(SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,16,2),'MM/DD/YYYY HH24:MI:SS') - 1 and SUBSTR(uwtsmdm_ext_meter_model,6) = ucrserv_invn_code and ucrserv_styp_code like '%ELC' and uwtsmdm_ext_alert_msg = 'Tilt Warning' and uwtsmdm_ext_alert_type = 'Tamper Alert'; and TO_DATE(SUBSTR(uwtsmdm_ext_read_time,5,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,7,2)||'-'|| SUBSTR(uwtsmdm_ext_read_time,1,4)||' '|| SUBSTR(uwtsmdm_ext_read_time,10,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,13,2)||':'|| SUBSTR(uwtsmdm_ext_read_time,16,2),'MM/DD/YYYY HH24:MI:SS') = trunc(sysdate); E field lo date ela undo oka example evvu ucbsvco_date_created
nenu_devudni Posted April 28, 2015 Author Report Posted April 28, 2015 E field lo date ela undo oka example evvu ucbsvco_date_created ipudu date form edi ichadu bhayya 20140702 04:00:00 na loader script lo not exists rayi manadu konni conditions ki .. okasari check chesi cheppu bhayya correct ee na .. INSERT INTO UWTSMEE (uwtsmee_minv_code, uwtsmee_model, uwtsmee_asvc_code, uwtsmee_cnfg_code, uwtsmee_alert_time, uwtsmee_alert_msg, uwtsmee_alert_type, uwtsmee_value, uwtsmee_state) SELECT SUBSTR(uwtsmdm_ext_meter_model,6,11), SUBSTR(uwtsmdm_ext_meter_model,1,5), 'EL', uwtsmdm_ext_cnfg_code, TO_DATE(uwtsmdm_ext_read_time,'YYYYMMDD HH24:MI:SS'), uwtsmdm_ext_alert_msg, uwtsmdm_ext_alert_type, uwtsmdm_ext_value, uwtsmdm_ext_state FROM UWTSMDM_EL_EXT OUTq, ucbsvco, ucrserv WHERE NOT EXISTS (SELECT 1 FROM UWTSMEE WHERE uwtsmee_model||uwtsmee_minv_code = OUTQ.uwtsmdm_ext_meter_model AND uwtsmee_asvc_code = 'EL' AND uwtsmee_cnfg_code = uwtsmdm_ext_cnfg_code AND uwtsmee_alert_msg = uwtsmdm_ext_alert_msg AND ucbsvco_prem_code = ucrserv_prem_code) AND NOT EXISTS (SELECT 1 FROM ucbsvco where ucbsvco_activity_date >= TO_DATE(uwtsmdm_ext_read_time,'YYYYMMDD HH24:MI:SS') - 1) AND NOT EXISTS (SELECT 1 FROM uwtsmee where uwtsmee_alert_type = uwtsmdm_ext_alert_type) and SUBSTR(uwtsmdm_ext_meter_model,6) = ucrserv_invn_code and ucrserv_styp_code like '%ELC' and uwtsmdm_ext_alert_msg = 'Tilt Warning' and uwtsmdm_ext_alert_type = 'Tamper Alert' and TO_DATE(uwtsmdm_ext_read_time,'YYYYMMDD HH24:MI:SS') = trunc(sysdate);
nenu_devudni Posted July 20, 2015 Author Report Posted July 20, 2015 Okasari aa filed lo date format cheppu with an example from file
Recommended Posts