5 Replies Latest reply: Mar 25, 2014 7:06 PM by Jeff Smith Sqldev Pm-Oracle RSS

    Query Builder in SQL Developer

    1871773

      Hello,

       

      As a way of intro, I am a power end-user that uses the SQL Developer to pull specific data using simple SQL statements (SELECT only).  As I start to pull more data, I need to join various tables.  I have been doing this by 'INNER JOIN' statements which works fine for a couple of tables, but I'd really like to use the Query Builder in order to build relationships by drag/drop.  However, every time I attempt to use the query builder I get the following error:

       

      Text is not a valid, single SELECT statement.
      Syntax Error at line 264, column 36


      FROM PO.PO_EMPLOYEE_HIERARCHIES_ALL
                                          ^^^

      Expected: ';','WHERE',identifier,
      Query Builder disabled.

       

      Any idea on what is causing this error and how I can fix it?  Many thanks in advance!

       

      FYI - I am using version 4.0.1.14

        • 1. Re: Query Builder in SQL Developer
          Jeff Smith Sqldev Pm-Oracle

          No idea, yet.

           

          I'm able to join 5 different tables by dragging and dropping from one column to another in the query builder.

           

          Are you getting the error message when you try to drag and drop the table into the query builder or when you try to drag the columns together to define the join?

          • 2. Re: Query Builder in SQL Developer
            1871773

            I get the error as soon as I switch over from the SQL worksheet.  For example, here are the steps I follow:

             

            1) Run query 1 from worksheet:

            SELECT EMPLOYEE_ID

            ,      SUPERIOR_ID

            ,      POSITION_STRUCTURE_ID

            ,      LAST_UPDATED_BY

            ,      LAST_UPDATE_DATE

            ,      CREATED_BY

            ,      CREATION_DATE

            ,      EMPLOYEE_POSITION_ID

            ,      SUPERIOR_POSITION_ID

            ,      SUPERIOR_LEVEL

            ,      LAST_UPDATE_LOGIN

            ,      REQUEST_ID

            ,      PROGRAM_APPLICATION_ID

            ,      PROGRAM_ID

            ,      PROGRAM_UPDATE_DATE

            ,      ORG_ID

            ,      BUSINESS_GROUP_ID

            FROM PO.PO_EMPLOYEE_HIERARCHIES_ALL;

             

            Results show up in the results pane below

             

            2) Add query 2 to worksheet:

             

            SELECT POSITION_ID

            ,      BUSINESS_GROUP_ID

            ,      JOB_ID

            FROM HR.PER_ALL_POSITIONS;

             

            Results show up in results pane as expected.

             

            3) Click 'Query Builder' to build relationships

             

            4) Receive Error Message as described earlier

             

            Is it because I am running two queries on the worksheet?

             

            My main objective is to be able to add tables and build relationships/queries via the Query Builder. Let me know if you think there is a different way to do this other than starting in the worksheet.

             

            Thanks for the quick reply!

            • 3. Re: Query Builder in SQL Developer
              Jeff Smith Sqldev Pm-Oracle

              yes, you can only have one query per worksheet to use the query builder

              • 4. Re: Query Builder in SQL Developer
                1871773

                Ok - thanks.  So how did you add five tables to the query builder?  Can you please refer me to some resources that will help me use the builder? I tried the 'Help' files but it didn't show me how to add tables...at least that I could follow.

                 

                Thanks for your help.

                • 5. Re: Query Builder in SQL Developer
                  Jeff Smith Sqldev Pm-Oracle

                  Just drag and drop them into the query builder space...or add them to your FROM clause in the worksheet and toggle back to the query builder