7 Replies Latest reply: Mar 19, 2015 2:34 PM by Clever Anjos RSS

    SQL to Oracle DB

    Kim Stroupe

      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