topic Re: Using a SQL Derived Table in a Load script in QlikView

Using a SQL Derived Table in a Load script

Wed, 30 Jul 2014 11:14:26 GMT

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'.

Re: Using a SQL Derived Table in a Load script

maxgro — Wed, 30 Jul 2014 15:02:27 GMT

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

Re: Using a SQL Derived Table in a Load script

Wed, 30 Jul 2014 16:07:59 GMT

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;