Re: tool: convert classic oracle sql to ansi?
Date: Tue, 30 Dec 2008 20:07:55 -0800 (PST)
Message-ID: <7bf53078-1d9f-4b1e-9cfe-29a7ca475222@g1g2000pra.googlegroups.com>
On Dec 31, 4:03 am, DA Morgan <damor..._at_psoug.org> wrote:
> kooroori..._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-Hidequoted 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
>> >> every query needs to be rewritten which is far more expensive especially
> >>> 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
> >> when you add in a full testing cycle.
>
> >> - killing dev with a cross join in the process. So for me we
>> >> training, at most a few thousand dollars, is a lot cheaper than
> >>> 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
> >> 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 index_name, table_name FROM user_indexes) b
> >> SELECT num_rows, index_name
> >> FROM (SELECT table_name, num_rows FROM user_tables) a,
> >> WHERE a.table_name = b.table_name(+);
>
> >> or this:
>> >> FROM qb1, qb2
> >> 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 */ *
> >> WHERE qb1.inst_id = qb2.inst_id;
>
> >> or this:
>> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> 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
> >> Puget Sound Oracle Users Groupwww.psoug.org-Hide quoted text -
>
> >> - Show quoted text -
>> > be able to understand them. In fact to me your first query seems so
> > 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
> > much shorter, simpler and easy to understand as:
>> > FROM user_tables a
> > SELECT a.num_rows, index_name
> > LEFT OUTER JOIN user_indexes b USING (table_name);
>> > your employees money - but since consultants seem to change by the
> > Perhaps you need some SQL training in simplifing your SQL and saving
> > 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -
Perhaps "lambasting" is a little strong but we appear to disagree on the meaning of your words "Which indicates to me that your training needs to be improved ..." and "If a little thing like classic Oracle vs ANSI joins has you in a twist ..." which, to me, is you attempting to berate my skills. Somewhat similar sematic arguments as to SQL which, as with English sentences, can (often) be written in many ways to achieve the same end result perhaps with subtle differences (meaning and clarity in English; perhaps performance or clarity in SQL). You are correct, after a short time you can easily enough read either "Oracle" or "ANSI" SQL and it becomes a personal or company (standards) preference.
One of the main reasons I started to prefer ANSI (aside from developers having learnt it and not "Oracle" SQL) is for clarity of joins, especially outer joins. I agree with Kevin Meade ( orafaq.com ) that outer joins are one of the most error prone areas and I have had to spend time sorting out such queries that "don't return the right results". Particularly doing a full outer join in ANSI syntax is much clearer than with Oracle.
Enough of this thread, it really comes down to personal preference, I feel there really little extra/less effort either way.
Have a Happy New year Daniel
rm Received on Tue Dec 30 2008 - 22:07:55 CST