Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL doesn't work - syntax or teradata issue

SQL doesn't work - syntax or teradata issue

SQL doesn't work - syntax or teradata issue


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?



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;

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close