You want things possibly on one row to expand to several rows (e.g. if one row contains both logon and logoff). Then I cannot see any other option than using union. But you can do something very similar to what Norman writes that I think makes the statement easier to read (though it might just be my preference):

WITH TMP_LOGON (STAFF_ID, TIMED, CLIENT, MYDATE) AS
(SELECT MOD( LN.STAFF_ID, 1000)
, SUBSTRING ( CAST (LN.LOGON AS TIME) FROM 1 FOR 8 )
, R.TITLE
, LN.LOGON
FROM STAFF_LOG LN
LEFT JOIN ROOMSTAT R ON MOD( LN.LOCATED, 64 ) = R.TERMINAL
),
TMP_CALLCLEAR (STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE) AS
(SELECT MOD( T.STAFF_ID, 1000)
, SUBSTRING ( CAST (T.TRANSACT AS TIME) FROM 1 FOR 8 )
, CASE WHEN T.ROOM > 80 THEN 'Called' ELSE 'Cleared' END
, T.TICKET_NO
, C.SURNAME || ',' || C.FORENAME
, T.TRANSACT
FROM TRANSACTIONS T
JOIN TICKET T2 ON T.TICKET_ID = T2.TICKET_ID
JOIN CALLER C ON T2.CALLER_ID = C.CALLER_ID
),
TMP_LOGOFF (STAFF_ID, TIMED, MYDATE) AS
(SELECT MOD( LO.STAFF_ID, 1000)
, SUBSTRING ( CAST (LO.LOGOFF AS TIME) FROM 1 FOR 8 )
, LO.LOGOFF
FROM STAFF_LOG LO
),
TMP_UNION (STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE) AS
(SELECT STAFF_ID, TIMED, 'Logon', 'AT', CLIENT, MYDATE
FROM TMP_LOGON
UNION
SELECT STAFF_ID, TIMED, ACTION, TICKET, CLIENT, MYDATE
FROM TMP_CALLCLEAR
UNION
SELECT STAFF_ID, TIMED, 'Logoff', 'OFF', '-----', MYDATE
FROM TMP_LOGOFF)

SELECT S1.SURNAME || ',' || S1.FORENAME AS NAME, T.TIMED, T.ACTION, T.TICKET, T.CLIENT
FROM TMP_UNION T
JOIN STAFF S ON T.STAFF_ID = S.STAFF_ID
WHERE T.MYDATE BETWEEN :FROM_DATE AND :FROM_DATE + 1
AND S.TEAM = 1
ORDER BY 1,2

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Lester Caine
Sent: 4. desember 2009 13:39
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: UNION capabilities

tomkrej wrote:

> Hi, did You think something like this??
>
> select col1, col2, 'TAB1' from tab1
> union
> select col3, col4, 'TAB2' from tab2

No a lot more complex ;)
Converting staff_id to staff name

Current query is below, but has three separate joins to STAFF and what I was
wondering is if there was a way to do that onces 'at the end' .... on a staff_id
column from the main union.
The WHERE is also a similar duplication?

SELECT S1.SURNAME || ',' || S1.FORENAME AS NAME
, SUBSTRING ( CAST (LN.LOGON AS TIME) FROM 1 FOR 8 ) AS Timed
, 'Logon' AS ACTION
, 'AT' AS TICKET
, ( SELECT TITLE FROM ROOMSTAT WHERE TERMINAL = MOD( LN.LOCATED, 64 ) ) AS CLIENT
FROM STAFF_LOG LN
JOIN STAFF S1 ON S1.STAFF_ID = MOD( LN.STAFF_ID, 1000)
WHERE LN.LOGON BETWEEN :FROM_DATE AND :FROM_DATE + 1
AND S1.TEAM = 1
UNION
SELECT S.SURNAME || ',' || S.FORENAME AS NAME
, SUBSTRING ( CAST (T.TRANSACT AS TIME) FROM 1 FOR 8 ) AS Timed
, CASE WHEN T.ROOM > 80 THEN 'Called'
ELSE 'Cleared' END AS ACTION
, T.TICKET_NO AS TICKET
, C.SURNAME || ',' || C.FORENAME AS CLIENT
FROM TRANSACTIONS T
JOIN STAFF S ON S.STAFF_ID = MOD( T.STAFF_ID, 1000)
JOIN CALLER C ON C.CALLER_ID = ( SELECT CALLER_ID FROM TICKET WHERE TICKET_ID =
T.TICKET_ID )
WHERE T.TRANSACT BETWEEN :FROM_DATE AND :FROM_DATE + 1
AND S.TEAM = 1
UNION
SELECT S2.SURNAME || ',' || S2.FORENAME AS NAME
, SUBSTRING ( CAST (LO.LOGOFF AS TIME) FROM 1 FOR 8 ) AS Timed
, 'Logoff' AS ACTION
, 'OFF' AS TICKET
, '-----' AS CLIENT
FROM STAFF_LOG LO
JOIN STAFF S2 ON S2.STAFF_ID = MOD( LO.STAFF_ID, 1000)
WHERE LO.LOGOFF BETWEEN :FROM_DATE AND :FROM_DATE + 1
AND S2.TEAM = 1
ORDER BY 1,2

--
Lester Caine - G8HFL