Hi Arno,

> > - is there anywhere I can download documentation on the Firebird
> > keywords and how to use them eg LEFT, IN etc..
>
> Look at : http://www.ibphoenix.com/

Yeah, I looked there before posting, but couldn't find any details
on the method syntax etc. Can you point me any closer, please?

> > - in mySQL I simulated a crosstab query (eg from MS Access) by
> > getting the column names, and then creating a query like this to
> > return the report table in one swoop:
> >
> > SELECT 'Total', SUM(IF(Kiosks.Name='desktop',

Metrics.Copies,

> > 0)) AS 'desktop', SUM(IF(Kiosks.Name='redcoat3', Metrics.Copies,

0))

> > AS 'redcoat3', SUM(Metrics.Copies) as Total FROM Kiosks INNER

JOIN

> > Metrics ON Kiosks.ID=Metrics.Kiosk_Id WHERE Time IS NOT NULL AND
> > Copies IS NOT NULL
> >
> > Is this possible in Firebird, and if not, can anyone suggest

another

> > method?
>
> As already somebody said you can use a stored procedure.
>
> In Firebird 1.5 you can also use the CASE expression :
>
> SELECT
> 'Total',
> SUM(CASE WHEN Kiosks.Name='desktop'
> THEN Metrics.Copies ELSE 0 END) AS 'desktop',
> SUM(CASE WHEN Kiosks.Name='redcoat3'
> THEN Metrics.Copies ELSE 0 END) AS 'redcoat3',
> SUM(Metrics.Copies) as Total
> FROM
> Kiosks
> INNER JOIN Metrics ON (Kiosks.ID=Metrics.Kiosk_Id)
> WHERE
> Time IS NOT NULL AND Copies IS NOT NULL

Thanks! I'll give it a try - looks like it will work.

Cheers,

David