Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
kstroupe
Contributor

SQL to Oracle DB

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

1 Solution

Accepted Solutions
Employee
Employee

Re: SQL to Oracle DB

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

7 Replies

Re: SQL to Oracle DB

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


talk is cheap, supply exceeds demand
kstroupe
Contributor

Re: SQL to Oracle DB

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

SQL Developer does NOT use the odbc driver.


talk is cheap, supply exceeds demand
Employee
Employee

Re: SQL to Oracle DB

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

Employee
Employee

Re: SQL to Oracle DB

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

kstroupe
Contributor

Re: SQL to Oracle DB

Thank you Clever yes I replaced the select * with

EDW_STAGE.STG_F_BASE_CAPACITY_PLAN.CAPACITY_ID

This ran the SQL.

Employee
Employee

Re: SQL to Oracle DB

Good to read that

Community Browser