Unexpected syntax error 3706 when using CASE IN with timestamp values

Database
Enthusiast

Unexpected syntax error 3706 when using CASE IN with timestamp values

Hello,

I am sometimes encountering a syntax error (3706) when using CASE with an IN clause, see (1) below.  The error is happening with timestamp columns and sometimes with varchar's (when the individual varchar values in the IN are concatenated, ie. col1 || col2).  Any info on this?

Thanks!

(1)  -- THIS GAVE A SYNTAX ERROR (expected something between 'timestamp1' and ',')

CASE WHEN timestamp IN (timestamp1, timestamp2, timestamp3)

THEN 1 ELSE 0 END  

(2)  -- THIS GAVE NO ERROR, BUT IS NOT A VERY BENEFICIAL USE OF "IN" CLAUSE

CASE WHEN timestamp IN (timestamp1)

THEN 1 ELSE 0 END  

(3)  my solution:

CASE WHEN timestamp = timestamp1

OR timestamp = timestamp2

OR timestamp = timestamp3

THEN 1 ELSE 0 END

6 REPLIES
Supporter

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

It would be nice if you would share the message text as well - at least I don't know all the error codes out of my mind and it takes time to check the manuals ;-)...

are timestamp1,...., timestamp3 values or columnnames?

Junior Supporter

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

Greg:

Your solution (3) is equivalent to what you're after.

Besides that:

 BTEQ -- Enter your SQL request or BTEQ command:

SELECT

CASE WHEN CURRENT_TIMESTAMP(0)

       IN ( '2012-02-10 09:29:40' ,

            '2012-02-10 09:29:41' ,

            '2012-02-10 09:29:39' )

        THEN 1

     ELSE 0 END

;

 *** Query completed. One row found. One column returned.

 *** Total elapsed time was 1 second.

<CASE  expression>

------------------

                 0

HTH.

Cheers.

Carlos.

Enthusiast

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

Hi Ulrich, Carlos,

Thanks for your replies.  They are column names, not hard-coded literals.  All of the columns involved are TIMESTAMP(6).  Below are the actual statements and results when running in BTEQ.  The first two queries give syntax errors, but of different flavors depending on whether an alias was used.   The following 3 queries are using just one of the three timestamp columns and they succeed with no error, so it seems nothing is wrong with the data itself.  Thanks!

 *** Logon successfully completed.

 *** Teradata Database Release is 13.10.02.16

 *** Teradata Database Version is 13.10.02.16

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 5 seconds.

+---------+---------+---------+---------+---------+---------+---------+----

  SELECT CASE WHEN PE.ADT_ARRIVAL_TIME IN

  (PE.HOSP_ADMSN_TIME,PE.INP_ADM_DATE,PE.APPT_TIME)

  THEN 1 ELSE 0 END

  FROM ADM_TABLE AS PE;

  (PE.HOSP_ADMSN_TIME,PE.INP_ADM_DATE,PE.APPT_TIME)

                       $

 *** Failure 3706 Syntax error: expected something between the word 'HOSP_AD

 MSN_TIME' and ','.

                Statement# 1, Info =65

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

  SELECT CASE WHEN ADT_ARRIVAL_TIME IN

  (HOSP_ADMSN_TIME,INP_ADM_DATE,APPT_TIME)

  THEN 1 ELSE 0 END

  FROM ADM_TABLE;

  (HOSP_ADMSN_TIME,INP_ADM_DATE,APPT_TIME)

                   $

 *** Failure 3706 Syntax error: Expecting TEMPORAL_DATE OR TEMPORAL_TIMESTAM

 P.

                Statement# 1, Info =58

 *** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----

  SELECT TOP 3 CASE WHEN ADT_ARRIVAL_TIME IN

  (HOSP_ADMSN_TIME)

  THEN 1 ELSE 0 END

  FROM ADM_TABLE;

 *** Query completed. 3 rows found. One column returned.

 *** Total elapsed time was 1 second.

<CASE  expression>

------------------

                 0

                 0

                 0

+---------+---------+---------+---------+---------+---------+---------+----

  SELECT TOP 3 CASE WHEN ADT_ARRIVAL_TIME IN

  (INP_ADM_DATE)

  THEN 1 ELSE 0 END

  FROM ADM_TABLE;

 *** Query completed. 3 rows found. One column returned.

 *** Total elapsed time was 1 second.

<CASE  expression>

------------------

                 0

                 0

                 0

+---------+---------+---------+---------+---------+---------+---------+----

  SELECT TOP 3 CASE WHEN ADT_ARRIVAL_TIME IN

  (APPT_TIME)

  THEN 1 ELSE 0 END

  FROM ADM_TABLE;

 *** Query completed. 3 rows found. One column returned.

 *** Total elapsed time was 1 second.

<CASE  expression>

------------------

                 0

                 0

                 0

+---------+---------+---------+---------+---------+---------+---------+----

Supporter

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

Hi,

I was intuitvely thinking that 

CASE WHEN timestamp IN (timestamp1, timestamp2, timestamp3) THEN 1 ELSE 0 END  

will work only with constants and not with columns - at least I never saw it and never used it.

CASE WHEN timestamp = timestamp1

OR timestamp = timestamp2

OR timestamp = timestamp3

THEN 1 ELSE 0 END

would have been my choice.

Seached a bit in the documentation and found the following:


This seems to be inline with my feeling - in has to be followed by a list of constants or a subquery.

Enthusiast

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

Hi Ulrich,

Thanks for your response and for spending the time to investigate.  The clip from the documentation is not displaying.  Can you perhaps provide a Doc Title and page number?  or URL?

Have a nice weekend!

-Greg

Supporter

Re: Unexpected syntax error 3706 when using CASE IN with timestamp values

Sorry for that - was visible in my browser after I posted it but can't see it now either.

Check the SQL Reference - Functions and Operators.

I searched for NOT IN and found a syntax diagram for ANY / ALL explanation.

Don't have access to the doc right now to give you a page number.