Archives of the TeradataForum

Message Posted: Tue, 26 Apr 2005 @ 08:00:57 GMT


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Subj:		Re: Strange syntax on in clauses

From:		Dieter Noeth

ulrich arndt wrote:

somebody made me aware of a new (would be interesting when it was implemented) syntax on in-clauses - which can drive you crazy in case of typos.

When i read the topic i thought you were referring to the new non-ANSI extension :-)

col in (1 to 50, 55, 60, 65 to 77)

My first expectation was that

          > select (current_date - calendar_date) (format '99') (char(2)) as
          > char_field
          > from sys_calendar.calendar
          > where char_field in ('01' '02','03')
          > and (current_date - calendar_date) > 0
          > ;

would cause an error - for the missing ',' between '01' and '02'.

But GCC told the customer it is correct because it is ANSI compliant for an concatenation - at least one black between the '01' and '02' - no blanck would be evaluated as "01'02".

I must confess that i've not been aware of that, but i just checked ANSI specs and i'm sorry to inform you that GCC is right :-(

Two string literals, that are separated by any white space (space|tab|newline|...) are treated as a single literal string. It's a kind of implicit concatenation.

Which means the in-Clause is evaluated as ('0102','03) and in this respect the result is correct.

Is this type of concatenation limited to IN-Clauses?

No, it's ugly but true for any string literal.

Because

          > Select '01' '02'

is resulting in '01' with the column name of '02'?

No, because column names must be double quoted. If it works this way on your version, it's some creative modification of your query by QueryMan/ODBC.

Uncheck "Allow use of ODBC SQL Extensions in queries" in Option -> Query to see the expected result.

     Select '01' '02' ;

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.

     ('01'||'02')
     ------------
     0102


     select 'bla'
     'bla';

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.

     ('bla'||'bla')
     --------------
     blabla

And

          > select (current_date - calendar_date) (format '99') (char(2)) 'XX'
          > from sys_calendar.calendar
          > where ((current_date - calendar_date) (format '99') (char(2))) in ('01'
          > '02','03')
          > and (current_date - calendar_date) > 0
          > ;

in an error.

Because of the 'XX' as column name.

Can somebody bring some light into this? It's a bit strange from my pointo of view...

I agree that it's strange, but a correct implementation of Standard SQL...

Dieter


	<Prev	Next>		<<First	<Prev	Next>	Last>>

Attachments

Library

Quick Reference