Home » SQL & PL/SQL » SQL & PL/SQL » Convert to ANSI from non-standard SQL (merged 7) 10g
Convert to ANSI from non-standard SQL (merged 7) 10g [message #415727] Wed, 29 July 2009 01:04 Go to next message
dv71005tx
Messages: 3
Registered: July 2009
Junior Member
Hello Expert,
I am trying to convert following Oracle SQL code to ANSI using Toad. I must say non-standard code is far too easy to understand but I have to convert it to ANSI somehow. Could anyone please help?

-- Creating flat Account table

select distinct
--Parent 1
fv01.description acp1_desc,
fv01.flex_value acp1_name,
fv01.flex_value_id acp1_flex_id,

--Parent 2
fv02.description acp2_desc,
fv02.flex_value acp2_name,
fv02.flex_value_id acp2_flex_id,

--Parent 3
fv03.description acp3_desc,
fv03.flex_value acp3_name,
fv03.flex_value_id acp3_flex_id,

-- Child Account code
fv04.description acchild_desc,
fv04.flex_value acchild_name,
fv04.flex_value_id acchild_flex_id

from
fnd_flex_value_sets fvst,

fnd_flex_value_norm_hierarchy fvh01,
fnd_flex_values_vl fv01,

fnd_flex_value_norm_hierarchy fvh02,
fnd_flex_values_vl fv02,

fnd_flex_value_norm_hierarchy fvh03,
fnd_flex_values_vl fv03,

fnd_flex_value_norm_hierarchy fvh04,
fnd_flex_values_vl fv04

where (fvst.flex_value_set_name='Account'
and fv01.flex_value='L0000')

and (fvh01.flex_value_set_id=fv02.flex_value_set_id(+)
and fv02.flex_value(+)>=fvh01.child_flex_value_low
and fv02.flex_value(+)<=fvh01.child_flex_value_high
and fvh01.child_flex_value_low=fvh02.parent_flex_value(+)

and fvh02.flex_value_set_id=fv03.flex_value_set_id(+)
and fv03.flex_value(+)>=fvh02.child_flex_value_low
and fv03.flex_value(+)<=fvh02.child_flex_value_high
and fvh02.child_flex_value_low=fvh03.parent_flex_value(+)

and fvh03.flex_value_set_id=fv04.flex_value_set_id(+)
and fv04.flex_value(+)>=fvh03.child_flex_value_low
and fv04.flex_value(+)<=fvh03.child_flex_value_high
and fvh03.child_flex_value_low=fvh04.parent_flex_value(+)


and fvst.flex_value_set_id=fv01.flex_value_set_id

and fv01.flex_value_set_id=fvh01.flex_value_set_id(+)
and fv01.flex_value=fvh01.parent_flex_value(+)
and fvh01.flex_value_set_id=fvh02.flex_value_set_id(+)
and fvh02.flex_value_set_id=fvh03.flex_value_set_id(+)
and fvh03.flex_value_set_id=fvh04.flex_value_set_id(+))
Re: Convert to ANSI from non-standard SQL (merged 7) 10g [message #415748 is a reply to message #415727] Wed, 29 July 2009 01:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think that spamming the forum with the same question will help you to get an answer.

When you are waiting for an answer, I recommend you to read OraFAQ Forum Guide, especially "How to get a quick answer to your question?", "Posting guidelines" and "How to format your post?" sections.

Regards
Michel
Re: Convert to ANSI from non-standard SQL (merged 7) 10g [message #415832 is a reply to message #415727] Wed, 29 July 2009 08:30 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
dv71005tx wrote on Wed, 29 July 2009 08:04
I must say non-standard code is far too easy to understand but I have to convert it to ANSI somehow.


It's just your opinion.

For example I repute standard ANSI join syntax much more clear than an unorganized complex where clause with everything mixed together.

Bye Alessandro
Re: Convert to ANSI from non-standard SQL (merged 7) 10g [message #415937 is a reply to message #415748] Wed, 29 July 2009 18:40 Go to previous message
dv71005tx
Messages: 3
Registered: July 2009
Junior Member
Michel Cadot,
What made you think that I was spamming???? The system went back to "This page can not be found" and I had to "back" and resubmit my question which might have had made double entries.
Don't get your foot into this if you can't answer question. Credibility of a "Senior Member" on a free weblog means nothing to the world!!!!
Previous Topic: how to write this mssql query in oracle
Next Topic: Beginner SQL Query Question
Goto Forum:
  


Current Time: Tue Dec 06 10:14:31 CST 2016

Total time taken to generate the page: 0.09538 seconds