New for version 3.1, currently available as a EA download, when defining the queries used to populate views, the modeler can generate JOINs using either Oracle’s or the standard ANSI compliant syntax.

Let’s step back a second.

When working with a VIEW defined in a relational model, the modeler has twp choices for defining the driving SQL statement that is used to populate the contents of the view. They can code it by hand – ideal when it’s a pretty simple SELECT * FROM with a few WHERE clauses, OR they can use the visual query builder and draw it up.

When going with the visual design method, JOIN clauses by default will use Oracle’s database syntax. We’re Oracle and we like our syntax. But, sometimes folks have silly (mostly a joke, mostly) rules in place that require as generic SQL syntax as possible. Maybe they find it easier to read, or maybe they think it will make a migration easier down the road. That’s not really important, however it is important to realize that the Modeler supports both methodologies.

You can access the visual query builder support directly from the View editor –

Click vs Type, will this battle never end?

For this example I have done a reverse-engineer of the SCOTT, HR, and SH schemas. One of the views here is HR.EMP_DETAILS_VIEW. Here’s the default SQL statement for that view:

[sql]
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e,
departments d,
JOBS j,
LOCATIONS l,
COUNTRIES c,
REGIONS r
WHERE e.department_id = d.department_id
AND d.location_id = l.LOCATION_ID
AND l.COUNTRY_ID = c.COUNTRY_ID
AND c.REGION_ID = r.REGION_ID
AND j.JOB_ID = e.job_id
[/sql]

With the click of a button..

Automagically translate Oracle joins to ANSI or vice-versa

With this ‘magic’ button, I’ve saved myself probably a few minutes. And that’s what I’m after – a tool that can save me time.

Magic buttons are rare, appreciate them when you find them

And here’s our new code
[sql]
SELECT e.employee_id,
e.job_id,
e.manager_id,
e.department_id,
d.location_id,
l.COUNTRY_ID,
e.first_name,
e.last_name,
e.salary,
e.commission_pct,
d.department_name,
j.JOB_TITLE,
l.CITY,
l.STATE_PROVINCE,
c.COUNTRY_NAME,
r.REGION_NAME
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
INNER JOIN JOBS j
ON j.JOB_ID = e.job_id
INNER JOIN LOCATIONS l
ON d.location_id = l.LOCATION_ID
INNER JOIN COUNTRIES c
ON l.COUNTRY_ID = c.COUNTRY_ID
INNER JOIN REGIONS r
ON c.REGION_ID = r.REGION_ID
[/sql]

Yes, it’s more VERBOSE, but some folks just prefer this method. And now you can choose a modeling tool that doesn’t make you choose just one implementation of SQL. And yes, you can go in the OTHER direction.

Bonus Tip: Validate your Query

Once you’ve defined your query, SQL Developer Data Modeler will allow you to test the query against a live database before saving it to the model. Being able to see the data that will come back when someone queries the view will give you a bit of a confidence boost, especially if you don’t trust these new-fangled ANSI JOIN statements. You can also inspect the Execution Plans. I’ve heard rumors that using ANSI vs Oracle join syntax can impact the plans the CBO optimizer will generate, but you should test this for yourself. Remember, I’m just a simple hacker at heart.

Testing is a dirty word, but it doesn't have to always be so hard.
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

5 Comments

  1. I have downloaded the latest version and it is working without any issue. Thank you very much.

  2. Hi Jeff,
    Please provide me the current tool version and software name. Is it sql developer or modeler?

    Navigation details to get to this screen would be great help.

    Thanks
    senthil

    • Version 4.0.3 is really old, so I’m not going to check that version. But it’s working as expected in the current version.

  3. It would be nice to be able to access this same feature within Query Builder available for a regular SQL worksheet.

    At the moment, I create a view, silly_vw, with the convulated sql (I wrote it ;), use sql*developer data modeller to import the view, then follow the above instructions to toggle between Oracle SQL and ANSI join syntax.

    The query builder on the SQL Worksheet appears to share a very similar dialogue box design with that of the query builder in Data Modeller minus the ‘Convert to ANSI Joins’ and ‘Convert to Oracle Joins’ “buttons”.

    It’s a feature that would likely benefit a lot of (PL/)SQL developers.

Reply To Senthil Cancel Reply