At 10:44 PM 24/04/2003 +0000, you wrote:

>Hi. Am completely new to Firebird - having been using mySQL til now.
>
>Have a couple of questions:
>
>- is there anywhere I can download documentation on the Firebird
>keywords and how to use them eg LEFT, IN etc..

Go to www.ibphoenix.com, go to the Downloads|InterBase page, and get the
beta manuals for IB 6. The Language Ref is the one you want for the SQL
syntax. More recent new language elements and syntax changes are in the
Firebird 1 releasenotes, a PDF you can download at the Firebird site.

>- 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?

1. The word INNER is optional for inner joins. (Likewise OUTER is
optional for outer joins: you can use LEFT, RIGHT and FULL on their own).

2. The string 'Time' is a reserved keyword in FB, so it is not allowed as
an identifier unless it is BOTH defined and used in double quotes, e.g.
"Time" - in which case the identifier is case-sensitive.

3. Your conditional for the first column of the output isn't possible in
the current releases. The forthcoming 1.5 supports a CASE structure in
dynamic SQL. I'm not familiar with what your two conditional clauses above
would get you as output so I've done a bit of guesswork below...
Currently, you might get what you are after, by either
a) using a UNION and get potentially a 2-row output set
b) use a subquery on a re-entrant join to get a single-row output set

If neither of these fits with what you need then
c) write a selectable stored procedure to get this conditional
output. (Firebird lets you define SPs that generate output sets. Find the
FOR SELECT...INTO...DO...SUSPEND construct in the LangRef)...

4. In joins, you need to qualify all column refs, including those in
WHERE, ORDER BY and GROUP BY clauses. The cleanest way to do this is to
use table aliases in join queries (also required in subqueries).

So, apart from the conditional (not do-able):
a) union query gives 2-row output (as long as rows meeting criteria of both
SELECT statements are present):
SELECT
'Totaldesktop' as ColName,
SUM(m.Copies) as Total
FROM Kiosks k
JOIN Metrics m ON
k.ID=m.Kiosk_Id
WHERE k.Name='desktop'
AND m."Time" IS NOT NULL
AND m.Copies IS NOT NULL
UNION
SELECT
'Totalredcoat3' as ColName,
SUM(m.Copies) as Total
FROM Kiosks k1
JOIN Metrics m1 ON
k1.ID=m1.Kiosk_Id
WHERE k1.Name='redcoat3'
AND m1."Time" IS NOT NULL
AND m1.Copies IS NOT NULL

b) subquery on re-entrant join gives 1-row output which (because of the
inner join) will return nothing if no rows are present for one or the other
SELECT statement)

SELECT
'Totaldesktop' as ColName,
SUM(m.Copies) as Total,
'Totalredcoat3' as ColName1,
( SELECT
SUM(m.Copies)
FROM Kiosks k1
JOIN Metrics m1 ON
k1.ID=m1.Kiosk_Id
WHERE k1.Name='redcoat3'
AND m1."Time" IS NOT NULL
AND m1.Copies IS NOT NULL)
as Total1
FROM Kiosks k
JOIN Metrics m ON
k.ID=m.Kiosk_Id
WHERE k.Name='desktop'
AND m."Time" IS NOT NULL
AND m.Copies IS NOT NULL

I don't know if it's a solution but hopefully it will get some discussion
going. :->

heLen


heLen