I’m having some trouble with SQL code that works when I run it in DBeaver, but when I execute it with cx_Oracle it comes up with the error of:
cx_Oracle.DatabaseError: ORA-00933: SQL command not properly ended
The python code is good, but I’m not much of a SQL programmer, so maybe someone can look to see if there is any obvious coding errors. It’s just weird that the code works in DBeaver but not with cx_Oracle.
Here is the code:
WITH MDVC_LP_HEADER_DATA AS (select distinct trunc(lh.start_time) as "Consumption Date", md.client_co_account_id as "NMI", md.MR_MDVC_NUMBER as "Meter", lh.mdvc_id, lh.header_id, lh.lp_attribute_id, lh.entry_date, lh.rec_status, cm.register_id as "Register", nvl(st.crm_register_id, st.register_id) as "Register_ID", st.stream_id as "Stream" from WACSMDMS.mdvc_lp_header lh, WACSMDMS.metering_device md, WACSMDMS.lp_attribute_config c, WACSMDMS.ozwave_register_channel_map cm, WACSMDMS.nem12_subs_members st where 1=1 and md.mdvc_id = lh.mdvc_id and lh.entry_date between to_date('26-JUL-2021') + 1 and to_date('2-AUG-2021')+1 and lh.rec_status in ('VFD', 'VDD') and lh.lp_attribute_id = c.lp_attribute_id and cm.channel_id = c.channel_id and st.nmi = md.client_co_account_id and st.meter_number = md.MR_MDVC_NUMBER and st.register_id = cm.register_id and st.stream_id != 'X' and st.subscriber_acr = 'EVERGY' ), mdvc_lp_dta as ( select distinct lh."Consumption Date" as "Consumption Date", lh."NMI" as "NMI", lh."Meter" as "Meter", lh.mdvc_id as "MDVC_ID", lh.lp_attribute_id, lh.rec_status, lp.comments as "Reason Description", lp.DATA_ID, lp.STATUS, lp.reason_code, lh."Register", lh."Stream" , lh."Register_ID" from WACSMDMS.mdvc_lp_data lp, WACSMDMS.MDVC_LP_HEADER_DATA lh where 1=1 and lh.mdvc_id = lp.mdvc_id and lh.lp_attribute_id = lp.lp_attribute_id and lh.header_id = lp.header_id and lp.end_time > lh.entry_date -1 and lp.end_time <= lh.entry_date ), base as (select lp."Consumption Date", lp."NMI", lp."Meter", lp."MDVC_ID", lp."Reason Description", lp.DATA_ID, lp.STATUS, lp.reason_code, NVL(a.GUI_DISPLAY_DESC,a.description) subs_type, CASE WHEN orcd.reason_code IS NOT NULL THEN orcd.reason_code||' ('||orcd.reason_description|| ')' ELSE NULL END AS "Reason Code", CASE WHEN lp.DATA_ID = 1 AND lp.STATUS IS NOT NULL THEN 'Substituted' ELSE CASE WHEN lp.DATA_ID = 0 THEN 'Actual' ELSE NVL(LDS.GUI_DISPLAY_DESC,LDS.DESCRIPTION) END END AS read_data_id, NVL(a.GUI_DISPLAY_DESC,a.description) AS read_data_status, lp.rec_status as rec_status2, lds.dataid_status_ind, a.dataid_status_ind dataid_status_ind2, lp."Register", lp."Stream", lp."Register_ID" from WACSMDMS.mdvc_lp_dta lp, WACSMDMS.lp_data_status a, WACSMDMS.lp_data_status lds, WACSMDMS.ozwave_reason_code_dim orcd where 1=1 and a.mr_gateway_id IS NULL AND a.dataid_status_ind(+) = 'S' AND a.file_data_status_id(+) = lp.status AND lp.data_id = lds.file_data_status_id (+) AND lds.mr_gateway_id (+) IS NULL AND lds.dataid_status_ind (+) = 'D' and lp.reason_code =orcd.reason_code (+) ) select "Consumption Date","NMI","Meter","Register","MDVC_ID", ( case when subs_type is null and read_data_id = 'Churn Substitution' and read_data_status is null then 'Type 19 Zero' else subs_type end ) as "Substitution Type", "Reason Code","Reason Description",read_data_id as "Data Quality", "Stream", "Register_ID" from base where 1=1 and ( ( base.read_data_id = 'Churn Substitution' and base.read_data_status is null ) or ( base.rec_status2 = 'VDD' and base.subs_type is not null and base.read_data_id <> 'Final Substitution' )) order by 1,2,3,4;
Query itself looks OK (as you said, it works in DBeaver). Maybe it is that Python doesn’t “like” closing statement terminator (semi-colon at the very end of the query) – try to remove it.
Apart from that, I’d suggest you not to rely on Oracle’s guessing date format. Instead of
TO_DATE ('26-JUL-2021'), use
TO_DATE ('26-JUL-2021', 'DD-MON-YYYY') (i.e. always provide appropriate format mask). Note that
MON can be tricky if database doesn’t speak English (for example, it would fail in my database which speaks Croatian) so – it is safer to use e.g.
TO_DATE ('26.07.2021', 'DD.MM.YYYY')