Good day...
I am using the RazorSQL to connect the Derby DB.
I want to present as Cross Tab from the OrderTable.
The result I want is:
Location.......Item Code .........NewOrderQty........ ConfirmedOrderQty....
So I wrote the query in the RazorSQL:
SELECT
LOCATIONCODE, ITEMCODE,
SUM (CASE APP.BASTESTDOMAININVENTORY_ORDER_LINE.STATUS WHEN 'New' THEN APP.BASTESTDOMAININVENTORY_ORDER_LINE.QUANTITY ELSE 0 END) As NewOrderQty,
SUM (CASE APP.BASTESTDOMAININVENTORY_ORDER_LINE.STATUS WHEN 'Confirmed' THEN APP.BASTESTDOMAININVENTORY_ORDER_LINE.QUANTITY ELSE 0 END) As ConfirmedOrderQty
FROM
APP.BASTESTDOMAININVENTORY_LOCATIONPRODUCT
INNER JOIN APP.BASTESTDOMAININVENTORY_LOCATION ON APP.BASTESTDOMAININVENTORY_LOCATIONPRODUCT.MYLOCATIONID_RID=APP.BASTESTDOMAININVENTORY_LOCATION.ID
INNER JOIN APP.BASTESTDOMAININVENTORY_ITEM ON APP.BASTESTDOMAININVENTORY_LOCATIONPRODUCT.MYITEMID_RID=APP.BASTESTDOMAININVENTORY_ITEM.ID
RIGHT JOIN APP.BASTESTDOMAININVENTORY_ORDER_LINE ON APP.BASTESTDOMAININVENTORY_LOCATIONPRODUCT.ID=APP.BASTESTDOMAININVENTORY_ORDER_LINE.MYLOCATIONPRODUCTID_RID
GROUP BY
LOCATIONCODE, ITEMCODE
ORDER BY
LOCATIONCODE
But when i run the query, the error message show:
ERROR: Syntax error: Encountered "APP" at line 3, column 19.
Error
Code: 30000
This is the Line 3:
SUM (CASE APP.BASTESTDOMAININVENTORY_ORDER_LINE.STATUS WHEN 'New' THEN APP.BASTESTDOMAININVENTORY_ORDER_LINE.QUANTITY ELSE 0 END) As NewOrderQty
Please advice which part is wrong...
thanks..
