Re: 9i: Check- constraint Upper(Column)


"Andreas Mosmann" <mosmann@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> schreef in
bericht news:1207208024.45@xxxxxxxxxxxxxxxxxxxxx
Maxim Demenko schrieb am 02.04.2008 in <47F3D26E.5040606@xxxxxxxxx>:

Just to comment a little bit - it is of course not an oracle bug, as
Shakespeare already shown, the brackets are *not* optional. The whole
syntax diagram for table index clause looks like
[ schema. ]table [ t_alias ]
(index_expr [ ASC | DESC ]
[, index_expr [ ASC | DESC ] ]...)
[ index_properties ]
( can be looked up
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_5010.htm#i2062403 )
,
so , what OP has done ( as in example above) - created function based
index on (upper(trim(CLOGINNAME))) for table MySchema.MyTable with an
*alias* trim, on this way the outer trim function will not do what OP
expected.
Interesting. But where or how can I use this *alias* "trim"? It is not to
be seen anywhere. So it sounds to me as *alias* is a nice word for a bug.
An error "missing brackets" would IMHO be more helpful.

Best regards
Thank you for your explaination
Maxim
Andreas Mosmann

--
wenn email, dann AndreasMosmann <bei> web <punkt> de

Actually, it is not an alias for the index, but an alias for the table. In
some situations, the table is referenced in the index definition (but I have
never seen this in practice). This is one from the books:

CREATE INDEX salary_func_i ON persons p
(TREAT(VALUE(p) AS part_time_emp_t).salary);

In this case, there is a table of part_time_emp_t (a user defined type) and
the index is built over the salary attribute of the type.
So VALUE(p) must be treated as the type part_time_emp_t and somehow you need
to reference the table in the index.

An error 'missing brackets' is not appropriate here, for the syntax is
correct. Maybe it would have been better to force a space in the syntax. In
that case your definition would not have met the syntax, and would have
caused less confusion.

Shakespeare




.

Relevant Pages

  • Re: Buggy SQL Syntax for External File Path in Access 2007
    ... Parentheses around the sub-select are turned into square brackets ... AS Alias is Jet SQL's syntax for derived tables. ... One option you might consider is setting your database to use ANSI ... 92 SQL, which will mean that you can use the standard AS Alias ...
    (microsoft.public.access.modulesdaovba)
  • Re: Buggy SQL Syntax for External File Path in Access 2007
    ... AS Alias is Jet SQL's syntax for derived tables. ... One option you might consider is setting your database to use ANSI ... if you're introducing the need for brackets (e.g., ...
    (microsoft.public.access.modulesdaovba)
  • Re: Square brackets?
    ... doesn't even see parentheses, but looks solely at the indentation. ... But by this reasoning the brackets are just needless syntactic ... as to whether that syntax even makes things any easier to read. ... The vast majority of beginners are ...
    (comp.lang.scheme)
  • Re: DCount Criteria - Easy Question!
    ... your syntax is more correct for use in a VBA module (though not quite ... the above syntax assumes the ClientID is a Number data type. ... brackets around rather than Forms! ...
    (microsoft.public.access.forms)
  • Re: Square brackets?
    ... the true Schemer ought to be able to see past a ... But by this reasoning the brackets are just needless syntactic ... as to whether that syntax even makes things any easier to read. ... The vast majority of beginners are ...
    (comp.lang.scheme)