Using a SQL Derived Table in a Load script

[#1]

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

[maxgro]

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 

[#3]

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;