This topic contains 1 reply, has 2 voices, and was last updated by 
-
Posts
-
Hi I’m looking at trying to query fold. I have read that this will speed things up for us.
The Qry below is from EXCEL and filters only selected dates. But when I try to convert the SQL statement to M in excel it doesn’t like the “WHERE” function. I am connecting a few tables from our SQL SERVER to run this query.
SELECT dbo_tblSale.sale_link, dbo_tblSale.loc_code AS [LOC#], dbo_tblSale.Date AS [TRANS DATE], dbo_tblSale.Time, dbo_tblSale.trans_no AS [TRANS#], dbo_tblSale.customer_code AS [CUST#], dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no AS [SKU#], dbo_tblSaleItem.description, Min(dbo_tblSaleDiscount.disc_code) AS [DISCOUNT CODE], dbo_tblSaleItem.qty AS [UNITS SOLD], dbo_tblSaleItem.ext_cost AS COST, [unit_ext_price]-[unit_ext_best_price] AS DISCOUNT, dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price AS [NET RETAIL], dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note AS [SALE NOTE], dbo_tblSaleItem.note AS [SALE ITEM NOTE]
FROM dbo_tblSaleDiscount RIGHT JOIN (dbo_tblSale INNER JOIN (dbo_tblSaleItem INNER JOIN dbo_tblSaleCommission ON (dbo_tblSaleItem.line_no = dbo_tblSaleCommission.line_no) AND (dbo_tblSaleItem.sale_link = dbo_tblSaleCommission.sale_link)) ON dbo_tblSale.sale_link = dbo_tblSaleItem.sale_link) ON (dbo_tblSaleDiscount.line_no = dbo_tblSaleItem.line_no) AND (dbo_tblSaleDiscount.sale_link = dbo_tblSaleItem.sale_link)
GROUP BY dbo_tblSale.sale_link, dbo_tblSale.loc_code, dbo_tblSale.Date, dbo_tblSale.Time, dbo_tblSale.trans_no, dbo_tblSale.customer_code, dbo_tblSaleCommission.salesperson, dbo_tblSaleItem.sku_no, dbo_tblSaleItem.description, dbo_tblSaleItem.qty, dbo_tblSaleItem.ext_cost, [unit_ext_price]-[unit_ext_best_price], dbo_tblSaleItem.unit_ext_price, dbo_tblSaleItem.unit_ext_best_price, dbo_tblSaleItem.line_no, dbo_tblSale.trans_type, dbo_tblSale.void_code, dbo_tblSale.suspended, dbo_tblSaleItem.affect_inv, dbo_tblSaleItem.affect_total, dbo_tblSale.note, dbo_tblSaleItem.note
HAVING (((dbo_tblSale.Date)>#1/31/2016#));sjhc,
I assume you have tested your query by running it directly against the tables of your database to check for syntax errors and that it also produces the results you expect.
That done, the joins in your query are too complex for query folding to figure out. Take a look at the readable SQL query at the bottom of this response.
For a FROM clause this complex, it is a best practice to create a SQL view for all of your code down to the line above your HAVING clause, then query the SQL view in Power Query and see how Query folding handles the filter that is now included in the HAVING clause.
Tom
SELECT dbo_tblSale.sale_link , dbo_tblSale.loc_code AS [LOC#] , dbo_tblSale.Date AS [TRANS DATE] , dbo_tblSale.Time , dbo_tblSale.trans_no AS [TRANS#] , dbo_tblSale.customer_code AS [CUST#] , dbo_tblSaleCommission.salesperson , dbo_tblSaleItem.sku_no AS [SKU#] , dbo_tblSaleItem.description , Min(dbo_tblSaleDiscount.disc_code) AS [DISCOUNT CODE] , dbo_tblSaleItem.qty AS [UNITS SOLD] , dbo_tblSaleItem.ext_cost AS COST , [unit_ext_price]-[unit_ext_best_price] AS DISCOUNT , dbo_tblSaleItem.unit_ext_price , dbo_tblSaleItem.unit_ext_best_price AS [NET RETAIL] , dbo_tblSaleItem.line_no, dbo_tblSale.trans_type , dbo_tblSale.void_code , dbo_tblSale.suspended , dbo_tblSaleItem.affect_inv , dbo_tblSaleItem.affect_total , dbo_tblSale.note AS [SALE NOTE] , dbo_tblSaleItem.note AS [SALE ITEM NOTE] FROM dbo_tblSaleDiscount RIGHT JOIN (dbo_tblSale INNER JOIN (dbo_tblSaleItem INNER JOIN dbo_tblSaleCommission ON (dbo_tblSaleItem.line_no = dbo_tblSaleCommission.line_no) AND (dbo_tblSaleItem.sale_link = dbo_tblSaleCommission.sale_link)) ON dbo_tblSale.sale_link = dbo_tblSaleItem.sale_link) ON (dbo_tblSaleDiscount.line_no = dbo_tblSaleItem.line_no) AND (dbo_tblSaleDiscount.sale_link = dbo_tblSaleItem.sale_link) GROUP BY dbo_tblSale.sale_link , dbo_tblSale.loc_code , dbo_tblSale.Date, dbo_tblSale.Time , dbo_tblSale.trans_no , dbo_tblSale.customer_code , dbo_tblSaleCommission.salesperson , dbo_tblSaleItem.sku_no , dbo_tblSaleItem.description , dbo_tblSaleItem.qty , dbo_tblSaleItem.ext_cost , [unit_ext_price]-[unit_ext_best_price] , dbo_tblSaleItem.unit_ext_price , dbo_tblSaleItem.unit_ext_best_price , dbo_tblSaleItem.line_no , dbo_tblSale.trans_type , dbo_tblSale.void_code , dbo_tblSale.suspended , dbo_tblSaleItem.affect_inv , dbo_tblSaleItem.affect_total , dbo_tblSale.note, dbo_tblSaleItem.note HAVING ( ( (dbo_tblSale.Date) > #1/31/2016# ) );
The forum ‘Power Pivot’ is closed to new topics and replies.