Re: tool: convert classic oracle sql to ansi?

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 19 Dec 2008 12:33:00 -0800
Message-ID: <1229718779.419206@bubbleator.drizzle.com>

kooroorinya_at_gmail.com wrote:

> On Dec 18, 6:27 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> steph wrote:
>>> On 16 Dez., 21:56, ddf <orat..._at_msn.com> wrote:
>>>> On Dec 16, 11:18 am, steph <stepha..._at_yahoo.de> wrote:
>>>>> Hi Group,
>>>>> I do this manually from time to time - but maybe there's a better
>>>>> way ...
>>>>> Does anybody know of a way, tool or whatever to convert classic oracle
>>>>> sql-statements to ansi-sql and vice versa? Maybe the database is able
>>>>> to do so?
>>>>> Thanks,
>>>>> Stephan
>>>> "Classic" Oracle SQL? Hmmm ...
>>>> Selecte *
>>>> From Ye Olde Dual;
>>>> Or are you asking for a 'tool' to 'translate' Oracle join syntax (such
>>>> as outer joins) into the ANSI equivalent?
>>>> I know of no such tool, but, I've been wrong before.
>>>> David Fitzjarrell
>>> Yes, correct: a tool to convert oracle join syntax to ansi join syntax
>>> - that's what I'm looking for!
>> It would take less time to write your own with perl than it has taken
>> to amuse everyone here by asking the question. The vast majority of
>> us, knowing Oracle, consider the ANSI joins a waste of perfectly good
>> keystrokes.
>>
>> Lets see:
>> SELECT *
>> FROM t1,t2
>> WHERE t1.col1 = t2.col2;
>>
>> Replace the comma with ' INNER JOIN ' (add ten keystrokes)
>> Replace WHERE with ON (subtract three keystrokes)
>> and it looks like this:
>>
>> SELECT *
>> FROM t1 INNER JOIN t2
>> ON t1.col1 = t2.col2;
>>
>> And that is worth your time and your employer's money?
>> And one is clearer than the other?
>> I'd love to hear the explanation.
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -

> 
> I think it is clearer as it better separate joins from selection
> criteria 

And this is important to a trained professional for what reason? If you can't read:

WHERE t1.x = t2.x

you are in the wrong profession.

and find it easier to spot missing joins which a few > developers [and myself] tend to do using the old syntax with complex > queries

Which indicates to me that your training needs to be improved not that every query needs to be rewritten which is far more expensive especially when you add in a full testing cycle.

• killing dev with a cross join in the process. So for me we > save time/$$ by less errors and rework. For simple queries it is a bit > of a trade off.

This isn't about you this is about your employer. And one week of training, at most a few thousand dollars, is a lot cheaper than rewriting and retesting working code.

If a little thing like classic Oracle vs ANSI joins has you in a twist what are you going to do when you see something like this?

SELECT num_rows, index_name
FROM (SELECT table_name, num_rows FROM user_tables) a,

      (SELECT index_name, table_name FROM user_indexes) b WHERE a.table_name = b.table_name(+);

or this:

WITH qb1 AS

   (SELECT inst_id FROM gv$session),

       qb2 AS
   (SELECT unique inst_id FROM qb1
    UNION ALL
    SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;

or this:

SELECT srvr_id
FROM (
   SELECT srvr_id, SUM(cnt) SUMCNT
   FROM (

     SELECT DISTINCT srvr_id, 1 AS CNT
     FROM servers
     UNION ALL
     SELECT DISTINCT srvr_id, 1
     FROM serv_inst)

   GROUP BY srvr_id)
WHERE sumcnt = 2;

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Fri Dec 19 2008 - 14:33:00 CST