SQL doesn't work - syntax or teradata issue

[kdbenson]

Hi,

I have a query that was suggested to me on an ANSI-SQL forum. When I tried it in Teradata (V2R5 or R6) it didn't run. The error I received was "3706: Syntax error: Expected something between '(' and the 'SELECT' keyword." I can't find any problem with the syntax so I was wondering if there is something specific to Teradata causing this to fail or if I am just missing something. The query is supposed to be ANSI 99 compliant. Any help you could provide would be appreciated. The SQL and table DDL are below.

BTW, is there a command/function to return the version of teradata?

Thanks,
Kevin

CREATE TABLE falls(patient_id INTEGER, unit VARCHAR(25), start_time TIMESTAMP, end_time TIMESTAMP)
INSERT INTO falls VALUES  (1234,'3C',TIMESTAMP '2009-05-29 09:00:00',TIMESTAMP '2009-05-29 19:00:00')
INSERT INTO falls VALUES  (1234,'TCU',TIMESTAMP '2009-05-28 08:00:00',TIMESTAMP '2009-05-29 09:00:00')
INSERT INTO falls VALUES  (1234,'3C',TIMESTAMP '2009-05-25 03:00:00',TIMESTAMP '2009-05-28 08:00:00')
INSERT INTO falls VALUES  (1234,'3C',TIMESTAMP '2009-05-23 07:00:00',TIMESTAMP '2009-05-25 03:00:00')
INSERT INTO falls VALUES  (1234,'3C',TIMESTAMP '2009-05-16 05:00:00',TIMESTAMP '2009-05-23 07:00:00')
INSERT INTO falls VALUES  (1234,'ICU',TIMESTAMP '2009-05-07 18:00:00',TIMESTAMP '2009-05-16 05:00:00')
INSERT INTO falls VALUES  (1234,'3FE',TIMESTAMP '2009-05-05 14:00:00',TIMESTAMP '2009-05-07 18:00:00')
INSERT INTO falls VALUES  (1234,'3FE',TIMESTAMP '2009-05-05 13:00:00',TIMESTAMP '2009-05-05 14:00:00')
INSERT INTO falls VALUES  (1234,'3C',TIMESTAMP '2009-05-02 12:00:00',TIMESTAMP '2009-05-05 13:00:00')


SELECT DISTINCT patient_id, unit,
       COALESCE((SELECT MAX(fls2.end_time)
                 FROM falls AS fls2
                 WHERE fls2.end_time <= fls1.start_time
                 AND fls2.patient_id = fls1.patient_id
                 AND fls2.unit <> fls1.unit),
                (SELECT MIN(fls2.start_time)
                 FROM falls AS fls2
                 WHERE fls2.patient_id = fls1.patient_id
                 AND   fls2.unit = fls1.unit)) AS s_time,
       COALESCE((SELECT MIN(fls2.start_time)
                 FROM falls AS fls2
                 WHERE fls2.start_time >= fls1.start_time
                 AND fls2.patient_id = fls1.patient_id
                 AND fls2.unit <> fls1.unit),
                (SELECT MAX(fls2.end_time)
                 FROM falls AS fls2
                 WHERE fls2.patient_id = fls1.patient_id
                 AND   fls2.unit = fls1.unit)) AS e_time
FROM falls fls1
ORDER BY patient_id, s_time, e_time;