select 1.x from t1
I made a funny typo today 
SQL> select * from t1;
| X |
|---|
| 0 |
SQL> select 1.x from t1;
| X |
|---|
| 1 |
Should not I get ORA-904 invalid identifier 
?
Put your code in <code> and </code> tags
I made a funny typo today
SQL> select * from t1;
| X |
|---|
| 0 |
SQL> select 1.x from t1;
| X |
|---|
| 1 |
Should not I get ORA-904 invalid identifier ?
Put your code in <code> and </code> tags
January 11th, 2008 at 13:34
No, for the same reason that
select ‘a’x from dual
would not fail.
select 1.
from dual;
works - 1. is a valid literal.
select ‘literal’name
from dual
is valid (whitespace is many times optional)… hence
select 1.x from dual
is.
ops$tkyte%ORA10GR2> select*from dual;
D
-
X
January 11th, 2008 at 14:31
of course, thanks for the explanation !
January 14th, 2008 at 06:45
[…] Can you write a working SQL statement without using any whitespace? Filed under: Cool stuff, Oracle, SQL — tanelp @ 12:42 pm I read this post by Laurent Schneider yesterday. In the comment section Tom Kyte already explained what the issue was about, but I’ll expand this explanation a little. […]
January 14th, 2008 at 17:36
So, what does
SQL > select 1.dummy from “DUAL”"1″;
select 1.dummy from “DUAL”"1″
*
ERROR at line 1:
ORA-03001: unimplemented feature
tells us ?
January 14th, 2008 at 22:39
Can you guys explain why the following statement works?
select SYSDATE@! from dual;
SYSDATE@!
———
14-JAN-08
SQL> select SYSDATE@!-4 from dual;
SYSDATE@!
———
10-JAN-08
January 15th, 2008 at 10:56
@matthias
Probably in Oracle 17, “DUAL”"1″ will be a table called DUAL”1, in Oracle 11 and before, it is not possible to have a double quote in a table name
@ed
No idea !
January 15th, 2008 at 13:47
[…] Last week I saw the blog entry “select 1.x from t1” from Laurent concerning white spaces in select statements and Tom Kyte’s answer with a short explanation. Tanel Poder wrote a blog entry “Can you write a working SQL statement without using any whitespace?” too. […]
January 15th, 2008 at 19:13
For what it’s worth
SQL> select sysdate from dual;
SYSDATE
———
15-JAN-08
SQL> select sysdate@ from dual;
select sysdate@ from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> select sysdate@! from dual;
SYSDATE@!
———
15-JAN-08
SQL> select sysdate@1 from dual;
select sysdate@1 from dual
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
And I don’t understand anything any more!
January 16th, 2008 at 01:25
Currently I don’t have a database at hand, so I can really test it, but regarding:
select 1.dummy from “DUAL””1″;
Counting that the use of double quotas is a standard thing while for instance looking into the syntax of a dumpfile, generated by “exp”. I would expect that “1″ is regarded as a database link alias or such. With SQL*Net V1 this was the place to use the connection descriptor starting with the protocol (t for tcpip, d for dec, n for names - if I remember correctly). Therefore maybe the ORA-03001: unimplemented feature.
Laurent are you sure you can’t use double quotes in a table name? Didn’t we wander these paths before…?
January 16th, 2008 at 16:03
Another little oddity. If you’re setting event 10053 and run
select sysdate@! from dual;
All you get is an empty tracefile (on 10.2.0.3) — seems the optimizer is choking on it ?
Perhaps the “!” character has a special meaning (other than executing commands in your shell on *nix systems) ?
Stefan
January 16th, 2008 at 23:18
Maybe we should have a separate discussion on sysdate@! thing
It was found when sysdate was used with db link.
I.e. SQL> select sysdate from dual@db_link;
produces the following SQL in remote database:
SELECT SYSDATE@! FROM “DUAL” “A1″
Ed
January 17th, 2008 at 21:52
Sure there is an entertaining (and time consuming) explanation for each and every oddity, but how many developers really write a query without whitespace? I’m not aware of any modern programming language where whitespace is not a fundamental part of the syntax. Ditto for identifiers in quotes. Those should be deprecated (although was there a case when anything has been deprecated in SQL?)
January 17th, 2008 at 23:14
Come on, Mikito Harakiri, wasting our time is not only a side effect but the *sense* of dealing with SQL, programming languages and all the rest, isn’t t ?
January 18th, 2008 at 09:15
I wrote about no space query before :
select*from”EMP”where’SCOTT’=”ENAME”and”DEPTNO”=20;
My opinion is still that spaces helps readability. Personaly I am quite greedy with spaces, so I would not write COS ( 0 ) but COS(0).
About wasting your time, I am a big fan of solving math problems with SQL
January 18th, 2008 at 09:33
Laurent,
thanks for that great link !
Σωκράτης