Hi All,

I am using the following SQL statement

"SELECT POSDESCRIPTION, "
"CASE WHEN (TradeDate = '2011/05/01') THEN COALESCE(QtySold, 0) ELSE 0 END
as Col1,"
"CASE WHEN (TradeDate = '2011/05/02') THEN COALESCE(QtySold, 0) ELSE 0 END
as Col2,"
"CASE WHEN (TradeDate = '2011/05/03') THEN COALESCE(QtySold, 0) ELSE 0 END
as Col3 FROM ("
"SELECT "
"POSDESCRIPTION, TradeDate, SUM(QtySold) As QtySold "
"From SALEHISTORY "
"Where "
"TradeDate Between '2011/04/01' AND '2011/04/04'"
"GROUP BY 1, 2) as Sales ORDER BY 1"

But I am getting the following error message

Runtime error -2147467259
[ODBC Firebird Driver][Firebird]Dynamic SQL Error
Expression evaluation not supported

Is this because I am running it through code in VB6?

Does the above sql code need to be run with in the Database?

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Leyne, Sean
Sent: 19 May 2011 11:29 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] SQL statment Help

> This may be the wrong group but I need some advice on a SQL statement
>
> I have a database that tacks all sales by date and by product I would like

to

> run a function to display the products with a summary of the items per
>
> Day for example
>
> Item 01/05/11 02/05/11
> 03/05/11
>
> =====================================================
>
> VALUE ORANGE JUICE 5 9
> 10

If you are using the Firebird v2.1 or later you should be able to:

SELECT
Sales.Product_Desc,
CASE WHEN (Sales.ItemDate = '1 MAY 2011') THEN COALESCE( Sales.ItemCount,
0) ELSE 0 END as Col1,
CASE WHEN (Sales.ItemDate = '2 MAY 2011') THEN COALESCE( Sales.ItemCount,
0) ELSE 0 END as Col2,
CASE WHEN (Sales.ItemDate = '3 MAY 2011') THEN COALESCE( Sales.ItemCount,
0) ELSE 0 END as Col3
FROM (
SELECT
Product_Desc,
ItemDate,
SUM( ItemCount) as ItemCount
FROM ItemSales
WHERE
ItemDate BETWEEN '1 MAY 2011' AND '3 MAY 2011'
GROUP BY 1, 2
) as Sales
ORDER BY 1




------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links





-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1375 / Virus Database: 1509/3649 - Release Date: 05/20/11

-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 10.0.1375 / Virus Database: 1509/3649 - Release Date: 05/20/11