SQL command not properly ended in Python OracleSQL-CodePudding

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;

CodePudding user response：

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')