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';
Then you first need to make sure that the stored procedure executes correctly and does return results when called like that by a user with the credentials you specify in the connection you use in Qlikview. Perhaps it's a credentials problem, perhaps it's a driver problem and the oledb or odbc driver can't handle the records set that's returned.
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.
When looking at comparable tools, crystal reports via OLEDB and ODBC, this proc ran absolutely fine and results were shown. This was a Qlik specific response not recognising that data was being returned.
I have included the answer to help anyone else that writes SQL procs to feed Qlik Sense, I understand that the proscribed Qlik route would be to have a QVD layer instead but that doesn't work for everyone.
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.