HI all,
We have products with historic prices against them and each price has a date, I need to display the price based on the max price date. I’ve got the max price date showing correctly using the below
=Max([Price Date] In([P Code];[P Desc];[Price Date]))
but cant get [Gross Cost] to display the price I need, its either showing all of the historic costs or giving a multivalve error. Does anybody know what the correct syntax is required for this please?
I only want to see 2 rows:
I’ve lost touch with WebI for a while but I’d do it with a “HAVING” clause in the SQL.
SELECT CustomerID, CustomerName, Price Date
FROM Customers
HAVING Gross Cost = MAX(Gross Cost)
Or this one, but you have to use an “n” that will include all ties for first place.
SELECT CustomerID, CustomerName, Price Date
FROM Customers
ORDER BY Gross Cost DESC
FETCH FIRST n ROWS ONLY
Depending on your DB…
– DB2
SELECT *
FROM table
FETCH first N rows only
– Informix
SELECT first N *
FROM table
– Microsoft SQL Server and Access
SELECT top N *
FROM table
– MySQL and PostgreSQL
SELECT *
FROM table limit N
– Oracle
SELECT *
FROM (SELECT * FROM table ORDER BY Gross Cost DESC)
WHERE rownum <= N
why are you switching from in() to foreach() ?
if there is a MULTIVALUE, try sum…, avg…, min… or max([Gross Cost])
something like =Max([Price Date] in{input context} in{output context}) might help