nenu_devudni Posted April 22, 2015 Author Report Posted April 22, 2015 so 1 day minus kavalante.. just add -1 to the query..that's it.. bhayya ... tat works . .error em ratledu .. but naku ade date filed ki ..current date vache la undali .. for example ... aa csv file lo chala dates untay .. bt naku current date ante eroju date ki vachina data ee pull cheyali ..
prassanna Posted April 22, 2015 Report Posted April 22, 2015 where date field equals sysdate.. make sure formats are correct..
nenu_devudni Posted April 22, 2015 Author Report Posted April 22, 2015 lunch pothuna..ochinaka estha.. edo chepav bhayya ..aradam kaledu .. vachaka chepava $^^E
prassanna Posted April 22, 2015 Report Posted April 22, 2015 edo chepav bhayya ..aradam kaledu .. vachaka chepava $^^E ipd seppu em kavalo..
CheGuevara Posted April 22, 2015 Report Posted April 22, 2015 bhayya ... tat works . .error em ratledu .. but naku ade date filed ki ..current date vache la undali .. for example ... aa csv file lo chala dates untay .. bt naku current date ante eroju date ki vachina data ee pull cheyali .. Naaku ardamina dani prakaram, neeku aa csv file lo aa date field lo unna date current date aite aa data matrame kavali adena? Then use spidereddys logic in thr where class as shown below: to_date(substr('Tue Aug 26 14:14:44 CDT 2014',5,3)||'-'||substr('Tue Aug 26 14:14:44 CDT 2014',9,2)||'-'||substr('Tue Aug 26 14:14:44 CDT 2014',25,4),'MM-DD-YYYY') = trunc(sysdate)
prassanna Posted April 22, 2015 Report Posted April 22, 2015 select to_date(substr(uwtsmdm_ext_read_time,5,3)||'-'||substr(uwtsmdm_ext_read_time,9,2)||'-'||substr(uwtsmdm_ext_read_time,25,4),'MM-DD-YYYY') from uwtsmdm_el_ext where uwtsmdm_ext_read_time = trunc(sysdate);
prassanna Posted April 22, 2015 Report Posted April 22, 2015 Naaku ardamina dani prakaram, neeku aa csv file lo aa date field lo unna date current date aite aa data matrame kavali adena? Then use spidereddys logic in thr where class as shown below: to_date(substr('Tue Aug 26 14:14:44 CDT 2014',5,3)||'-'||substr('Tue Aug 26 14:14:44 CDT 2014',9,2)||'-'||substr('Tue Aug 26 14:14:44 CDT 2014',25,4),'MM-DD-YYYY') = trunc(sysdate) agreed..
nenu_devudni Posted April 22, 2015 Author Report Posted April 22, 2015 ipd seppu em kavalo.. 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'), to_date(substr(uwtsmdm_ext_read_time,5,3)||'-'||substr(uwtsmdm_ext_read_time,9,2)||'-'||substr(uwtsmdm_ext_read_time,25,4),'MM-DD-YYYY'), 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,3)||'-'||substr(uwtsmdm_ext_read_time,9,2)||'-'||substr(uwtsmdm_ext_read_time,25,4),'MM-DD-YYYY') - 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'; edi na loader script lo insert bhayya .. meru ichina condition ni use chesanu bane undi .. but date okate display chestundi .. Hours fetch avatledu .. and ade date filed uwtsmdm_ext_read_time should fetch current date data .. not all the dates .. and uwtsmee_alert_time datatype kuda varchar lo ne undi ..
prassanna Posted April 22, 2015 Report Posted April 22, 2015 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'), to_date(substr(uwtsmdm_ext_read_time,5,3)||'-'||substr(uwtsmdm_ext_read_time,9,2)||'-'||substr(uwtsmdm_ext_read_time,25,4),'MM-DD-YYYY'), 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,3)||'-'||substr(uwtsmdm_ext_read_time,9,2)||'-'||substr(uwtsmdm_ext_read_time,25,4),'MM-DD-YYYY') - 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'; edi na loader script lo insert bhayya .. meru ichina condition ni use chesanu bane undi .. but date okate display chestundi .. Hours fetch avatledu .. and ade date filed uwtsmdm_ext_read_time should fetch current date data .. not all the dates .. aa mukka mundhe cheppal mari.. em le..just take the hour part also..substr(uwtsmdm_ext_read_time(12,2)) and add to the query.. to get hour part..
Yuvatha Posted April 22, 2015 Report Posted April 22, 2015 aa mukka mundhe cheppal mari.. em le..just take the hour part also..substr(uwtsmdm_ext_read_time(12,2)) and add to the query.. to get hour part.. ssis ocha
nenu_devudni Posted April 22, 2015 Author Report Posted April 22, 2015 aa mukka mundhe cheppal mari.. em le..just take the hour part also..substr(uwtsmdm_ext_read_time(12,2)) and add to the query.. to get hour part.. to_date(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:MM:SS') edi chesa bhayya .. error ostundi , format code appears twice ani
prassanna Posted April 22, 2015 Report Posted April 22, 2015 to_date(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:MM:SS') edi chesa bhayya .. error ostundi , format code appears twice ani nen 2 ante nu 8 eskunav..error rammante raadha?
Recommended Posts