SQL to Oracle DB

[kstroupe]

Hello,

I have a query which runs just fine in other SQL tools outside of QLIK. When I run the FULL SQL through QLIK to the Oracle DB no data is returned in the QVD. If parsed out the part where I think QLIK is having an issue and I receive an error message in QLIK but no error message in other SQL Tools outside of QLIK. There error in QLIK is ODBC read failed. It occurs when I add the INNER JOIN from EDW_STAGE.STG_F_BASE_CAPACITY_PLAN to DT_PROD_ORDER_FROZEN by is Any help is appreciated.

The below script works and returns one row in QLIK. When I try to use the commented part of this I receive an ODBC read error.

TEST:

select * from
//EDW_STAGE.STG_F_BASE_CAPACITY_PLAN
//INNER JOIN
(
select
x.prodn_ordr_no,
y.d_part_master_id,
y.d_wbs_element_id,
y.actual_finish_dt,
y.tot_ordr_qty,
y.capacity_id,
y.prodn_ordr_type_cd
from
((
select
EDW.F_BASE_PRODUCTION_ORDER.prodn_ordr_no,
max(EDW.F_BASE_PRODUCTION_ORDER.ordr_last_updt_dttm) as maxdate
from
EDW.F_BASE_PRODUCTION_ORDER where
EDW.F_BASE_PRODUCTION_ORDER.prodn_ordr_no = '000024854815'
group by EDW.F_BASE_PRODUCTION_ORDER.prodn_ordr_no)x
left join
(
select
edw.f_base_production_order.prodn_ordr_no,
edw.f_base_production_order.d_part_master_id,
edw.f_base_production_order.d_wbs_element_id,
edw.f_base_production_order.actual_finish_dt,
edw.f_base_production_order.tot_ordr_qty,
edw.f_base_production_order.capacity_id,
edw.f_base_production_order.ordr_last_updt_dttm,
edw.f_base_production_order.prodn_ordr_type_cd
from edw.f_base_production_order ) y
on x.prodn_ordr_no = y.prodn_ordr_no and x.maxdate = y.ordr_last_updt_dttm
) where y.actual_finish_dt is null
) DT_PROD_ORDER_FROZEN
//ON (EDW_STAGE.STG_F_BASE_CAPACITY_PLAN.CAPACITY_ID=DT_PROD_ORDER_FROZEN.CAPACITY_ID)
;

Thanks

[Gysbert_Wassenaar]

Whatever the problem is, it is not caused by Qlikview. Qlikview merely passes the sql statement to the odbc driver. Possibly the odbc driver cannot handle what the oracle dbms returns. Perhaps a SQL_LONGVARBINARY column: http://community.qlik.com/message/12234#12234

[kstroupe]

Thank you for your comment, however I am using the same ODBC driver with SQL Developer to this database with no errors.  If QLIK is simply passing the SQL I would assume I would get no errors too.

Thanks

[Gysbert_Wassenaar]

SQL Developer does NOT use the odbc driver.

[Clever_Anjos · Accepted solution]

Please check if you have two columns with same name, this cause a ODBC general error

[Clever_Anjos]

As good practice always list all columns you´re retrieving, don´t use "select *"

[kstroupe]

Thank you Clever yes I replaced the select * with

EDW_STAGE.STG_F_BASE_CAPACITY_PLAN.CAPACITY_ID

This ran the SQL.

[Clever_Anjos]

Good to read that