I am trying to filter information in the container before feeding into the report, and return IC Item Pricing where the DPRICETYPE is 1 (to prevent two row of data for every item and having to add yet another filter into my already complex report filters).
I learnt a new trick by following the helpful advice in http://www.sageintelligencecommunity.com/forum/topics/simple-sql-expression .
- I created a simple container with only the ICPRIC & ICPRICP tables
- then created a simple report with only the [ICPRICP].[UNITPRICE] field
- filtered by DPRICETYPE.
- switched to output mode
- copied the sql output.
- pasted into the expression in my original (more complex) container.
SELECT [ICPRICP].[UNITPRICE]
FROM (([ICPRIC] INNER JOIN [ICPRICP]
ON [ICPRIC].[ITEMNO] = [ICPRICP].[ITEMNO] AND [ICPRIC].[PRICELIST] = [ICPRICP].[PRICELIST])
INNER JOIN [ICITEM]
ON [ICPRIC].[ITEMNO] = [ICITEM].[ITEMNO])
WHERE ([ICPRICP].[DPRICETYPE] = 1)
Error is [Microsoft][ODBC SQL Server Driver][SQL Server] Incorrect syntax near the keyword "SELECT'.
At this point, I am only applying one filter and I can't even get that to work.
Can anyone guide me on correct syntax??
Ultimately I want two expressions:
- Give me the unitprice where type = 1 and pricelist = "retail"
- Give me the unitprice where type = 1 and pricelist = "whsale"
so that the two columns come is side-by-side next each item on my report.
