All Forums Database
gpolanch 46 posts Joined 12/11
09 Feb 2012
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

ulrich 816 posts Joined 09/09
09 Feb 2012

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?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

CarlosAL 512 posts Joined 04/08
10 Feb 2012

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.

gpolanch 46 posts Joined 12/11
10 Feb 2012

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

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

 

 

 

 

ulrich 816 posts Joined 09/09
10 Feb 2012

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.

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

gpolanch 46 posts Joined 12/11
10 Feb 2012

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

 

ulrich 816 posts Joined 09/09
10 Feb 2012

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.

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.