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
-----------------------------
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk//
Firebird - http://www.firebirdsql.org/index.php