topic Re: SQL to Oracle DB in QlikView

SQL to Oracle DB

kstroupe — Thu, 19 Mar 2015 17:39:47 GMT

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

Re: SQL to Oracle DB

Gysbert_Wassenaar — Thu, 19 Mar 2015 17:48:05 GMT

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

Re: SQL to Oracle DB

kstroupe — Thu, 19 Mar 2015 18:00:14 GMT

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

Re: SQL to Oracle DB

Gysbert_Wassenaar — Thu, 19 Mar 2015 18:05:21 GMT

SQL Developer does NOT use the odbc driver.

Re: SQL to Oracle DB

Clever_Anjos — Thu, 19 Mar 2015 18:14:05 GMT

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

Re: SQL to Oracle DB

Clever_Anjos — Thu, 19 Mar 2015 18:20:14 GMT

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

Re: SQL to Oracle DB

kstroupe — Thu, 19 Mar 2015 18:32:57 GMT

Thank you Clever yes I replaced the select * with

EDW_STAGE.STG_F_BASE_CAPACITY_PLAN.CAPACITY_ID

This ran the SQL.

Re: SQL to Oracle DB

Clever_Anjos — Thu, 19 Mar 2015 18:34:18 GMT

Good to read that