30 SQL Developer Tips in 30 Days, Day 1: Help with WHERE Clauses

thatjeffsmith SQL Developer 14 Comments

Tell Others About This Story:

For the month of September I’m challenging myself to perform two different feats of strength:

  1. Do 5,000 push-ups in the month of September
  2. Post a SQL Developer Tip or Trick every day for the next 30 days

I’ve noticed my posts are starting to go out once or twice a week, and I like to average three. So hopefully this mini-bootcamp of blog posts will me back on schedule.

So, without further ado, Tip #1:

How to Get Help with your WHERE Clauses

It’s all about the cursor.

If you type ‘WHERE’ and invoke the helper, via Ctrl+Spacebar:

Here are some code samples from the Oracle Docs

Here are some code samples from the Oracle Docs

If you type ‘WHERE ‘ and invoke the helper:

May we JOIN you?

May we JOIN you?

Note the space after the WHERE when invoking the helper.

Since I’ll be blogging a tip or trick every day for the next 30 days, most of these will be shorter and easier for you to digest, I hope!

Tell Others About This Story:

Comments 14

  1. I have confirmed in version 4.1.0.19 that this trick only works with uppercase alias’. Is this issue being address in future versions? I never use uppercase alilas’ in my standard sql formatting.

    1. thatjeffsmith Post
      Author
    1. thatjeffsmith Post
      Author
  2. This feature almost never works for me. There seems to be some bug related to the table aliases and case sensitivity. I tried to replicate your example with other tables and, it works consistently if the first table has an alias of exactly “a” and the second has an alias of “B”. Change the B to lowercase and it breaks. In general, “a” seems to be the only lowercase or mixed-case alias that it will accept for me and otherwise it only works if both aliases are purely uppercase. So I can change the aliases to TAB1 and TAB2 respectively and it works, but tab1 and tab2 break it (as well as Tab1 and Tab2).

    I’m not quoting anything, so nothing about this query should be case-sensitive. I’m running the 64bit executable on Windows 7 and connected to a Oracle 11g instance.

    1. thatjeffsmith Post
      Author

      You’re right the case of the alias(es) should have nothing to do with it working or not. Can you send me a query that works with one set of aliases and another that does not so I can test side-by-side here?

      1. create table TMP_TAB_ONE( id number, seq number, tab_one_val number);
        create table TMP_TAB_TWO( id number, seq number, tab_two_val number);

        –works
        select *
        from TMP_TAB_ONE TAB1, TMP_TAB_TWO TAB2
        where
        ;

        –doesn’t work
        select *
        from TMP_TAB_ONE tab1, TMP_TAB_TWO TAB2
        where
        ;

        1. thatjeffsmith Post
          Author
  3. Hi Jeff,

    Looking forward to your 30 tips in 30 days. Shorter tips are great for time-crunched people.
    Is there a Mac version for this?
    When I type control + spacebar on my Mac keyboard, nothing happens.

    Thanks!

    1. thatjeffsmith Post
      Author
    2. On my mac it was mapped to both control-space and command-option-C by default.

      When I first tried control-space it wasn’t working, and then I realized I had been disconnected from the DB i was working in… oops.

Leave a Reply

Your email address will not be published. Required fields are marked *