0

I have a report in SSRS2017 in the SQL Side i have a parameter filter as it can contain multiple values its set as a "in(@SALES_ORDER)" value. I have 2 derrived tables joined to my main table, 1 on the sales order number and the other on a production order number, when run I get the noted "SQL excecution error. I cannot work out why I get the error, I beleive its on the sales order number the Data type for the sales order number in the "COOIS" and UNIT_NO DERR tables are "BIGINT"

I have tried to "CAST ( FIELD NAME AS INT32 ) in each instance I can see the @SALES_ORDER. but still get the error

The code is as below, I am not a qualified coder and have only used the SSRS tool ( Sorry ) :

SELECT        MAX(WOL_COOIS_1.BSC_START) AS Start_date, WOL_COOIS_1.LEADGORDER, WOL_COOIS_1.PROJ_NO_8, CAST(WOL_COOIS_1.SALES_ORD AS INT32) AS Expr1, WOL_COOIS_1.DUMP_DATE, 
                         UNIT_No_DERRIVED_TABLE.PURCHASE_ORDER_NUMBER, UNIT_No_DERRIVED_TABLE.PROJ_DESCN, SO_PLANNED_HOURS_DERRIVED_TABLE.SOPT_STANDARDVALUE
FROM            WOL_COOIS AS WOL_COOIS_1 LEFT OUTER JOIN
                             (SELECT        TOP (100) PERCENT WOL_COOIS.SALES_ORD AS SOPT_SALES_ORD, SUM(WOL_COOIS_OPS.STD_VALUE_3 * WOL_COOIS_OPS.OP_QTY) AS SOPT_STANDARDVALUE
                               FROM            WOL_COOIS_OPS LEFT OUTER JOIN
                                                         WOL_COOIS ON WOL_COOIS_OPS.[ORDER] = WOL_COOIS.[ORDER]
                               GROUP BY WOL_COOIS.SALES_ORD
                               HAVING         (WOL_COOIS.SALES_ORD IN (@SALES_ORDER))
                               ORDER BY SOPT_STANDARDVALUE DESC) AS SO_PLANNED_HOURS_DERRIVED_TABLE ON WOL_COOIS_1.SALES_ORD = SO_PLANNED_HOURS_DERRIVED_TABLE.SOPT_SALES_ORD LEFT OUTER JOIN
                             (SELECT DISTINCT TOP (100) PERCENT WOL_BANK.ORDER_NO, WOL_BANK.PURCHASE_ORDER_NUMBER, WOL_PROJECT_INFO.PROJ_DESCN
                               FROM            WOL_BANK LEFT OUTER JOIN
                                                         WOL_PROJECT_INFO ON WOL_BANK.PROJ_NO_8 = WOL_PROJECT_INFO.PLUPS
                               WHERE        (WOL_BANK.ORDER_NO > 2900000000)
                               ORDER BY WOL_BANK.ORDER_NO) AS UNIT_No_DERRIVED_TABLE ON WOL_COOIS_1.SALES_ORD = UNIT_No_DERRIVED_TABLE.ORDER_NO
GROUP BY WOL_COOIS_1.LEADGORDER, WOL_COOIS_1.PROJ_NO_8, CAST(WOL_COOIS_1.SALES_ORD AS INT32), WOL_COOIS_1.DUMP_DATE, UNIT_No_DERRIVED_TABLE.PURCHASE_ORDER_NUMBER, 
                         UNIT_No_DERRIVED_TABLE.PROJ_DESCN, SO_PLANNED_HOURS_DERRIVED_TABLE.SOPT_STANDARDVALUE
HAVING        (CAST(WOL_COOIS_1.SALES_ORD AS INT32) IN (@SALES_ORDER))
jarlh
  • 42,561
  • 8
  • 45
  • 63
Tony K
  • 1
  • 1
  • 4
  • I have also removed the 2 derrived tables and still get the error so the error must be driven y "WOL_COOIS_!) table when using the "IN" command in the parameter. TK – Tony K Apr 01 '19 at 11:24
  • Are you sure your Sales Ord number isn't actually varchar? It it's anything like your Order_No field (2,900,000,000), it's too big for INT 32 (2,147,483,647). If not, you might still be able to make your parameter TEXT and CAST the SALES_ORD number AS varchar. – Hannover Fist Apr 01 '19 at 15:46
  • Thank you very much, as I was stuck I moved on to another report, that now has the same issue, I cast both firelds as varchar and it works. thank you so very much :) Tony – Tony K Apr 02 '19 at 14:22

0 Answers0