Re: tool: convert classic oracle sql to ansi?

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 30 Dec 2008 10:03:47 -0800
Message-ID: <1230681063.954791@bubbleator.drizzle.com>

kooroorinya_at_gmail.com wrote:

> On Dec 20, 6:33 am, DA Morgan <damor..._at_psoug.org> wrote:

>> kooroori..._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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -

>>

>> - Show quoted text -

> 
> Have a few days leave and come back to this?
> Thanks for the lambasting of my SQL skills Daniel. I don't really see
> the inference you draw. Then you post some rather simple SQL using in-
> line views, UNIONS outerjoins and WITH clauses - inferring i would not
> be able to understand them. In fact to me your first query seems so
> much shorter, simpler and easy to understand as:
> 
> SELECT a.num_rows, index_name
>   FROM user_tables a
>   LEFT OUTER JOIN user_indexes b USING (table_name);
> 
> Perhaps you need some SQL training in simplifing your SQL and saving
> your employees money - but since consultants seem to change by the
> hour :)
> 
> I also don't advocate re-work (converting existing queries) unless
> there is some need - such as a performance problem.
> 
> rm

Not lambasting your skills at all.

Anyone that has been in this industry for more than a few years should be as comfortable with:

if BooleanExpression then

   StatementIfTrue
else

   StatementIfFalse;

as they are with:

if BooleanExpression then

   StatementIfTrue;
else

   StatementIfFalse;
end if;

and numerous other variations. Surely you can easily read both of these

You may be the world's greatest coder. I don't know because I don't know you. What I do know is that the amount of flexibility required to read both traditional Oracle and ANSI Standard joins is minimal. And after teaching both for nine years to students I have a very good idea of how easy it is for people, who want to, to learn both.

I didn't rate you ... you rated yourself.

-- 
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 Tue Dec 30 2008 - 12:03:47 CST