SQL doesn't work - syntax or teradata issue?

Database
Enthusiast

SQL doesn't work - syntax or teradata issue?

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;

2 REPLIES
Junior Contributor

Re: SQL doesn't work - syntax or teradata issue?

Hi Kevin,
don't ask me why but Teradata didn't support that kind of ANSI Scalar Subqueries before TD13.

But even if it was supported, it would run quite slow (probably on any DBMS).

Didn't you try the query i posted some days ago?
I'm shure it returns the expected result set, if not this might be fixed.

http://developer.teradata.com/forum/database/tricky-grouping-question#comment-475

Btw, the Teradata version is returned by "select * from dbc.dbcinfo;"

Dieter
Enthusiast

Re: SQL doesn't work - syntax or teradata issue?

Hi Dieter,

Thanks for the info on the subqueries. I thought it might be something like that but I couldn't find any documentation. I didn't realize you had posted something on my previous thread. Thanks for the pointer.

-Kevin