« positive infinity
flashback archive table »

select*from”EMP”where’SCOTT’=”ENAME”…

What is wrong with this query?


select*from"EMP"where'SCOTT'="ENAME"and"DEPTNO"=20;
     EMPNO ENAME      JOB              MGR HIREDATE     
---------- ---------- --------- ---------- ---------
      7788 SCOTT      ANALYST         7566 13-JUL-87

It is a zero-space query 8-)

You could write it as


select
    *
from
    "EMP"
where
    'SCOTT'="ENAME"
    and
    "DEPTNO"=20;

personnaly, I would write it as


select *
from emp
where ename='SCOTT'
  and deptno=20;

Formatting is very important, it makes your code nice to read and indentation make the blocks visualable.

Auto-formatting is also fine, but I like to decide myself if the line is too long, or if I want to have FROM and EMP on the same line.

Have a look at the free online SQL Formatter SQLinForm

This entry was posted on Friday, August 31st, 2007 at 12:54 and is filed under oracle, sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.
Post to : del.icio.us - Digg it - Furl - reddit - Spurl

Put your code in <code> and </code> tags

9 Responses to “select*from”EMP”where’SCOTT’=”ENAME”…”

  1. Hector Gabriel Ulloa Ligarius Says:
    August 31st, 2007 at 14:36

    Hi Laurent

    Nice post, but , who will write SQL of that way? a lot letters and spaces…

    I believe that no…

    Thank you again Laurent by yours nice and accurate post

    Regards
    Hector Gabriel Ulloa Ligarius
    http://ligarius.wordpress.com

  2. Jeffrey Kemp Says:
    September 3rd, 2007 at 06:01

    Not nearly as obfuscated as C code can get, I must say; when I read it initially I was unable to see what was “wrong” with the statement, thinking it was just a html formatting problem…

    I find auto-formatting better than hand-formatting because it maintains

  3. Jeffrey Kemp Says:
    September 3rd, 2007 at 06:02

    …consistency across the application.

    :)

  4. Sidhu Says:
    September 3rd, 2007 at 09:11

    Nice Link.

    But it doesnt tell anything about the syntax errors. The one by orafaq http://orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl points out even syntax errors, if any.

    Sidhu

  5. Laurent Schneider Says:
    September 3rd, 2007 at 10:42

    well, it points out syntax error, but for which version? there are plenty of valid queries that are unaccepted because of advanced syntaxes like KEEP, VERSION, MODEL, PARTITION, IS A SET, etc…

  6. Sidhu Says:
    September 3rd, 2007 at 16:06

    No no, nothing that complex…I just typed some stupid text where orafaq formatter gave the syntax error but this one doesn’t like

    select * from emp where select * from dept

    Sidhu

  7. Sven’s Technik-Blog » Blog Archive » Code formatieren (SQL, PL/SQL) Says:
    October 19th, 2007 at 17:46

    […] In Laurent Schneider’s Blog habe ich nun eine Alternative gefunden: Den Online SQL Formater SQLinForm. […]

  8. AketiJyuuzou Says:
    March 15th, 2008 at 01:08

    On SQLPlus, I sometimes use
    select*from session_privs;
    select*from session_roles;

    We can omit Space 2 times 8-)
    These are a little useful ;-)

  9. Laurent Schneider Says:
    March 15th, 2008 at 08:04

    it is a bit like in DOS
    cd\

Leave a Reply

Use <code> and </code> to post code