I tried but not works. The complet test below: import pyodbc
# connect
crsr = pyodbc.connect('DSN=impala1;', autocommit=True).cursor()
# unnamed parameters
params = ('C', 1000000000, '2023-01-01')
# short query
query = """SELECT *
FROM test1.test1 d
INNER JOIN
(SELECT *
FROM test1.test2 c1
WHERE dat_ini_val = (SELECT max(c2.dat_ini_val)
FROM test1.test2 c2
WHERE c2.sta = ?
AND c2.cod_cli=c1.cod_cli
AND c1.cod_fsc=c2.cod_fsc)) c
ON d.cod_cli = c.cod_cli
WHERE cast(D.cod_fsc as string) <> ''
AND d.prg_rig < ? AND d.dat_cre_flu >= ? AND d.TIP_FLU = 'F24';"""
try:
res = crsr.execute(query, params).fetchall() #<-- this works
print('Query simple DONE!!!')
except pyodbc.Error as e:
e.args = (e.args[1],)
print(e)
# long query
query = """SELECT *
FROM test1.test1 d
LEFT OUTER JOIN
(SELECT cli, fsc, nom_rag_soc, dat_ini_val, dat_ccz_inl, Dat_csz_efv, sta, prg_var, COD_CAU_CSZ_DL
FROM
(SELECT c2.cod_cli AS cli,
TRIM(CAST(c2.cod_fsc AS STRING)) AS fsc,
TRIM(CAST(c2.nom_rag_soc AS STRING)) AS nom_rag_soc,
c2.dat_ini_val,
c2.dat_ccz_inl,
c2.Dat_csz_efv,
c2.sta,
c2.prg_var,
c2.COD_CAU_CSZ_DL
,(CASE WHEN c2.Dat_csz_efv = '1900-01-01 00:00:00' THEN '2999-01-01' ELSE c2.Dat_csz_efv END) AS dta_cessata
,ROW_NUMBER () OVER (PARTITION BY cod_fsc ORDER BY (case when c2.Dat_csz_efv='1900-01-01 00:00:00' then '2999-01-01' ELSE c2.Dat_csz_efv END) desc) rn
FROM test1.test2 c2
WHERE c2.sta = ?
) A
) k
ON TRIM(CAST(d.COD_fsc AS STRING)) = k.fsc
WHERE d.prg_rig < ?
AND d.dat_cre_flu >= ?
AND d.TIP_FLU = 'F24';"""
try:
res = crsr.execute(query, params).fetchall() #<-- this does not work
print('Query complex DONE!!!')
except pyodbc.Error as e:
e.args = (e.args[1],)
print(e)
query = """
WITH prova as (SELECT c2.cod_cli AS cli,
TRIM(CAST(c2.cod_fsc AS STRING)) AS fsc,
TRIM(CAST(c2.nom_rag_soc AS STRING)) AS nom_rag_soc,
c2.dat_ini_val,
c2.dat_ccz_inl,
c2.Dat_csz_efv,
c2.sta,
c2.prg_var,
c2.COD_CAU_CSZ_DL
,(CASE WHEN c2.Dat_csz_efv = '1900-01-01 00:00:00' THEN '2999-01-01' ELSE c2.Dat_csz_efv END) AS dta_cessata
,ROW_NUMBER () OVER (PARTITION BY cod_fsc ORDER BY (case when c2.Dat_csz_efv='1900-01-01 00:00:00' then '2999-01-01' ELSE c2.Dat_csz_efv END) desc) rn
FROM test1.test2 c2
WHERE c2.sta = 'C'
)
SELECT *
FROM test1.test1 d
LEFT OUTER JOIN
(SELECT cli, fsc, nom_rag_soc, dat_ini_val, dat_ccz_inl, Dat_csz_efv, sta, prg_var, COD_CAU_CSZ_DL
FROM prova
) k
ON TRIM(CAST(d.COD_fsc AS STRING)) = k.fsc
WHERE d.prg_rig < ?
AND d.dat_cre_flu >= ?
AND d.TIP_FLU = 'F24';
"""
params = (1000000000, '2023-01-01')
try:
res = crsr.execute(query, params).fetchall() #<-- this does not work
print('Query with DONE!!!')
except pyodbc.Error as e:
e.args = (e.args[1],)
print(e) the simple query, with parameters, works, others do not
... View more
