Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a SQL statement that makes use of a derived table that I want to use in my load script. The derived table is discountPercentage.
The excerpt that is giving me an error is as follows:
LEFT OUTER JOIN
(SELECT ItemDiscount.ItemID,
ItemDiscount.surcharge
FROM ItemDiscount
WHERE ItemDiscount.percentage >= 10
GROUP BY ItemDiscount.ItemID) AS discountPercentage ON Item.ItemId = discountPercentage.ItemId
When loading the script I get the following error message:
ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: Incorrect syntax near the keyword 'AS'.
I would have expected a different error because the bold part is not in the group by and not in an aggregate function
Could you post the entire statement (sql side and qlik)?
LEFT OUTER JOIN
(SELECT ItemDiscount.ItemID,
ItemDiscount.surcharge
FROM ItemDiscount
WHERE ItemDiscount.percentage >= 10
GROUP BY ItemDiscount.ItemID) AS discountPercentage ON Item.ItemId = discountPercentage.ItemId
Ciao Massimo
Thank you for your response. The entire query is quite complex hence I 'simplified' it for the sake of this forum. I have now extracted a larger piece of this query where you can see that the 'surcharge' field is not required in the GROUP BY. Below is the extracted query from my QLIKVIEW Script file (I have again simplified it somewhat):
StoreTrx:
SQL (SELECT Store.branchId as StoreId,
[transaction].transactionId as TrxId,
[transaction].tradeDate as TradeDate,
SUM(orderItem.quantity) as Qty,
SUM((orderItem.quantity * orderItem.price) * CASE WHEN discountPercentage.percentage IS NULL THEN 1 ELSE (discountPercentage.percentage) END) as Price,
FROM [transaction] WITH (INDEX ([IX_transaction]))
INNER JOIN [orderItem] WITH (INDEX ([IX_orderItem])) ON [transaction].transactionId = orderItem.transactionId
INNER JOIN Store ON orderItem.ItemId = Store.objectId
LEFT OUTER JOIN
(SELECT ItemDiscount.ItemId,
EXP(SUM(LOG (CASE
WHEN ItemDiscount.surcharge = 0
THEN (CASE
WHEN ItemDiscount.percentage >= 1
THEN 0.000000000001
ELSE 1 - ItemDiscount.percentage END)
ELSE (1 + ItemDiscount.percentage) END))) as percentage
FROM ItemDiscount
WHERE ItemDiscount.percentage >= 0
GROUP BY ItemDiscount.ItemId) AS discountPercentage ON orderItem.ItemId = discountPercentage.ItemId
WHERE (tradeDate >= '$(vStartTradeDate)' AND tradeDate <= '$(vEndTradeDate)')
GROUP BY Store.branchId, [transaction].tradeDate, [transaction].transactionId
)
STORE [StoreTrx] INTO '..\Data\StoreTrx.qvd' (QVD);
DROP TABLE StoreTrx;