Re: tool: convert classic oracle sql to ansi?
Date: Mon, 29 Dec 2008 21:09:39 -0800 (PST)
Message-ID: <6ecff0d1-ebd6-4b07-beb1-85e9716246fc@w1g2000prm.googlegroups.com>
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.
>> >> FROM t1,t2
> >> Lets see:
> >> SELECT *
> >> WHERE t1.col1 = t2.col2;
>> >> Replace WHERE with ON (subtract three keystrokes)
> >> Replace the comma with ' INNER JOIN ' (add ten keystrokes)
> >> and it looks like this:
>> >> FROM t1 INNER JOIN t2
> >> SELECT *
> >> ON t1.col1 = t2.col2;
>> >> damor..._at_x.washington.edu (replace x with u to respond)
> >> 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
> >> 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 inline 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 Received on Mon Dec 29 2008 - 23:09:39 CST