8 Replies Latest reply: Jun 2, 2016 10:13 AM by Laxmaiah Chowdary RSS

    Loading from SQL Stored procedure not working

    Chris Johnson

      Trying to load data from a SQL Server stored procedure, this works fine for other procedures but does not want to work in this instance.

      Load *; Sql Exec [Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = '' ;

      I'm sure I'm missing something simple but I cannot see it.

      The final code I will be using will be something like the below.

      RawSysOrders: Load StockDescription As Product ,CustomerPoNumber As PO ,SalesOrder As PBL_Ref ,AcceptedDate ,ActualDeliveryDate As ActualShipDate ,Null() As Confirmation ,PlannedDeliverDate As ProposedShipDate ,Null() As RevisedShipDate1 ,Null() As RevisedShipDate2 ,Null() As RevisedShipDate3 ,OrderQty As Qty ,StockCode As Code ,Null() As Volume ,Job As Batch ,CustomerName As DeliveredTo ,CustomerName As SoldTo ,Null() As PaymentTerms ,ApplyMap('Map_SOPrices',SalesOrder&'_'&SalesOrderLine,0) As GBP ,0 As USD ,0 As Euro ,1 As USDRate ,1 As EuroRate ,0 As ShippingCost ,If(ActualDeliveryDate<=PlannedDeliverDate,'Yes','No') As ShipDateMet ,DaysDiff As DaysOVer ,DispatchComments As NotesProblems ,Year(EntrySystemDate) as File; where Year(EntrySystemDate)>=2016; Sql Exec [BlackBox].[Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = 'Qlik Sense > Product KPIs';
        • Re: Loading from SQL Stored procedure not working
          Gysbert Wassenaar

          Line 30:

          RawSysOrders: 
          Load 
                 StockDescription As Product 
                 ,CustomerPoNumber As PO 
                 ,SalesOrder As PBL_Ref 
                 ,AcceptedDate 
                 ,ActualDeliveryDate As ActualShipDate 
                 ,Null() As Confirmation 
                 ,PlannedDeliverDate As ProposedShipDate 
                 ,Null() As RevisedShipDate1 
                 ,Null() As RevisedShipDate2 
                 ,Null() As RevisedShipDate3 
                 ,OrderQty As Qty 
                 ,StockCode As Code 
                 ,Null() As Volume 
                 ,Job As Batch 
                 ,CustomerName As DeliveredTo 
                 ,CustomerName As SoldTo 
                 ,Null() As PaymentTerms 
                 ,ApplyMap('Map_SOPrices',SalesOrder&'_'&SalesOrderLine,0) As GBP 
                 ,0 As USD 
                 ,0 As Euro 
                 ,1 As USDRate 
                 ,1 As EuroRate 
                 ,0 As ShippingCost 
                 ,If(ActualDeliveryDate<=PlannedDeliverDate,'Yes','No') As ShipDateMet 
                 ,DaysDiff As DaysOVer 
                 ,DispatchComments As NotesProblems 
                 ,Year(EntrySystemDate) as File
                 ;  // <== There's a ; that shouldn't be there
          where 
                 Year(EntrySystemDate)>=2016
                 ; 
          Sql Exec [BlackBox].[Report].[UspResults_SalesOrderKPI] @Company = '10' , @RedTagType = 'Q' , @RedTagUse = 'Qlik Sense > Product KPIs';
          
          • Re: Loading from SQL Stored procedure not working
            Chris Johnson

            Reviewing the SQL code, I now believe I have resolved the issue.

             

            Qlik does not handle SQL row counts well, at the beginning of my sql query I had an insert causing a row count of 1 row being affected.

             

            In order to address this, I set "nocount on" at the beginning of the query, in addition I had to set "nocount off" before returning the data.

            • Re: Loading from SQL Stored procedure not working
              Laxmaiah Chowdary

              Hi,

               

              Thank you for the discussion!!.

               

              Could you please help in integrating oracle stored procedure data with QlikView.

               

              I have tried all the above changes.

               

              But I am not able to fetch Oracle stored procedure data into QlikView as well as insert records into Oracle table through stored procedure.

               

              I have posted the issue in forum atQlikView integration with Oracle Stored procedure FAILS in fetching data and inserting data to oracle table

               

              Could you please take a look at and advise for corrections.

               

              Thanks a lot in advance.

               

              Regards,

              Laxmaiah Chowdary