0

Need to convert this query for sql server. I tried few examples but they didn't work for me.

TRANSFORM Sum(CUST_ORDER_LINE.ORDER_QTY) AS SumOfORDER_QTY
SELECT CUST_ORDER_LINE.PART_ID
FROM CUSTOMER_ORDER LEFT JOIN CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID
WHERE (((CUST_ORDER_LINE.PART_ID) Is Not Null) AND ((CUSTOMER_ORDER.STATUS) In ('F','R')))
GROUP BY CUST_ORDER_LINE.PART_ID
PIVOT CUSTOMER_ORDER.STATUS
Anusha Subashini
  • 387
  • 5
  • 17
James Smith
  • 15
  • 2
  • SELECT * FROM ( SELECT CUST_ORDER_LINE.ORDER_QTY,CUST_ORDER_LINE.PART_ID,CUSTOMER_ORDER.STATUS as STATUS FROM CUSTOMER_ORDER INNER JOIN CUST_ORDER_LINE ON CUSTOMER_ORDER.ID = CUST_ORDER_LINE.CUST_ORDER_ID ) as s PIVOT ( SUM(CUST_ORDER_LINE.ORDER_QTY) FOR [STATUS] IN (F,R) ) as pvt ---I've tried this example but getting an error. – James Smith Apr 17 '19 at 18:22
  • Please post the error. – SS_DBA Apr 17 '19 at 19:08
  • The column prefix 'CUST_ORDER_LINE' does not match with a table name or alias name used in the query. – James Smith Apr 17 '19 at 19:23
  • Is there a table named `CUST_ORDER_LINE` or is it `CUSTOMER_ORDER_LINE`? Please post the table schemas. – SS_DBA Apr 17 '19 at 19:45
  • 2 tables used in this query. "CUST_ORDER_LINE " and "CUSTOMER_ORDER". There is no CUSTOMER_ORDER_LINE table. – James Smith Apr 17 '19 at 19:57
  • Remove the `CUST_ORDER_LINE.` from the `SUM()` function in the `PIVOT`. The `PIVOT` is referencing the select referenced as `s`. – SS_DBA Apr 17 '19 at 20:03

0 Answers0