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: 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

SQL command not properly ended in Python OracleSQL

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;

Answer

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

Advertisement

Answer