OraFAQ Forum: SQL & PL/SQL » Converting SQL syntax

Home » SQL & PL/SQL » SQL & PL/SQL » Converting SQL syntax (Oracle 11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Converting SQL syntax [message #615666]

Fri, 06 June 2014 11:36

rchenna
Messages: 25
Registered: February 2008

Junior Member

Hi All,

I am new to ANSI SQL syntax. I always used Oracle specific syntax. I need to covert some of the Oracle complex queries into ANSI SQL syntax. I am giving one SQL here. Can somebody help me converting this into ANSI SQL

SELECT ed.last_name || ', ' || ed.first_name AS Employee,
       d.desc_short,
       pf.perf_rating_amount AS rating,
       ROUND((NVL(fs.salary_actual, 0) * cr.cur_exchange_rt), 0) AS current_salary,
       ROUND((NVL(eb.bonus_amount, 0) * cr.cur_exchange_rt), 0) AS disc_var_pay,
  FROM fr_employees h, fr_emp_details ed, fr_departments d, fr_emp_performances ep, 
       fr_perf_ratings pf, fr_emp_salaries fs, fr_currency_rates cr, fr_periods p, 
       fr_emp_bonuses eb
 WHERE h.person_seq_id = ed.person_seq_id
   AND ed.dept_seq_id = d.dept_seq_id
   AND ed.person_seq_id = ep.person_seq_id (+)
   AND ed.period_seq_id = ep.period_seq_id (+)
   AND ep.perf_rating_seq_id_1 = pf.perf_rating_seq_id(+)
   AND ed.person_seq_id = fs.person_seq_id (+)
   AND ed.period_seq_id = fs.period_seq_id (+)
   AND cr.from_curr_cd = ed.currency_cd_host
   AND cr.to_curr_cd = 'USD'
   AND ed.person_seq_id = eb.person_seq_id (+)
   AND ed.period_seq_id = eb.period_seq_id (+)
   AND ed.period_seq_id = p.period_seq_id
   AND cr.period_seq_id = p.period_seq_id
   AND p.period_status = 'CURRENT'
   AND ( NVL(ed.excluded_flag,'N') = 'N')
   AND ( NVL(ed.security_restricted,'N') = 'N')
   AND h.attribute1 = '1149 - Propulsion'
   AND h.attribute2 is null
   AND h.attribute3 is null
   AND h.attribute4 is null
   AND h.attribute5 is null
   AND h.attribute6 is null
   AND h.attribute7 is null
   AND h.attribute8 is null
   AND h.attribute9 is null
   AND h.attribute10 is null
 ORDER BY UPPER(Employee) ASC

*BlackSwan added {code} tags. Please do so yourself in the future. http://www.orafaq.com/forum/t/174502/102589/

[Updated on: Fri, 06 June 2014 11:40] by Moderator

Report message to a moderator

Re: Converting SQL syntax [message #615667 is a reply to message #615666]

Fri, 06 June 2014 11:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

I believe, myself and many others in this forum can do your task in one go. However, it would be good for you as a learning objective if you post what you have tried so far. Did you look for ANSI syntax? Please post what you tried so far.

Report message to a moderator

Re: Converting SQL syntax [message #615673 is a reply to message #615667]

Fri, 06 June 2014 13:19

rchenna
Messages: 25
Registered: February 2008

Junior Member

I have referred couple of examples on google but could not find any appropriate SQL that matches my requirement. There are two problems.

1. I could not find any example that shows one table (fr_emp_details) joins with multiple others tables (fr_periods, fr_emp_performances, fr_emp_salaries, fr_emp_bonuses) with multiple join conditions.
2. Some of them in this list are outer joins.

I have to make changes to around 60 SQL queries. I would rather spend time on converting other SQL queries once I somebody help me for this query.

Report message to a moderator

Re: Converting SQL syntax [message #615674 is a reply to message #615673]

Fri, 06 June 2014 13:20

rchenna
Messages: 25
Registered: February 2008

Junior Member

Thank you BlackSwan. While creating this topic, I formatted the query but did not follow the procedure. In future I will follow the link provided by you.

Report message to a moderator

Re: Converting SQL syntax [message #615675 is a reply to message #615674]

Fri, 06 June 2014 13:22

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I have to make changes to around 60 SQL queries.
WHY?
If they are working now, what is gained by changing them?

BTW - the posted SELECT is not valid syntax!

SELECT ed.last_name 
       || ', ' 
       || ed.first_name                                            AS Employee, 
       d.desc_short, 
       pf.perf_rating_amount                                       AS rating, 
       Round(( Nvl(fs.salary_actual, 0) * cr.cur_exchange_rt ), 0) AS 
       current_salary, 
       Round(( Nvl(eb.bonus_amount, 0) * cr.cur_exchange_rt ), 0)  AS 
       disc_var_pay 
FROM   fr_employees h, 
       fr_emp_details ed, 
       fr_departments d, 
       fr_emp_performances ep, 
       fr_perf_ratings pf, 
       fr_emp_salaries fs, 
       fr_currency_rates cr, 
       fr_periods p, 
       fr_emp_bonuses eb 
WHERE  h.person_seq_id = ed.person_seq_id 
       AND ed.dept_seq_id = d.dept_seq_id 
       AND ed.person_seq_id = ep.person_seq_id (+) 
       AND ed.period_seq_id = ep.period_seq_id (+) 
       AND ep.perf_rating_seq_id_1 = pf.perf_rating_seq_id(+) 
       AND ed.person_seq_id = fs.person_seq_id (+) 
       AND ed.period_seq_id = fs.period_seq_id (+) 
       AND cr.from_curr_cd = ed.currency_cd_host 
       AND cr.to_curr_cd = 'USD' 
       AND ed.person_seq_id = eb.person_seq_id (+) 
       AND ed.period_seq_id = eb.period_seq_id (+) 
       AND ed.period_seq_id = p.period_seq_id 
       AND cr.period_seq_id = p.period_seq_id 
       AND p.period_status = 'CURRENT' 
       AND ( Nvl(ed.excluded_flag, 'N') = 'N' ) 
       AND ( Nvl(ed.security_restricted, 'N') = 'N' ) 
       AND h.attribute1 = '1149 - Propulsion' 
       AND h.attribute2 IS NULL 
       AND h.attribute3 IS NULL 
       AND h.attribute4 IS NULL 
       AND h.attribute5 IS NULL 
       AND h.attribute6 IS NULL 
       AND h.attribute7 IS NULL 
       AND h.attribute8 IS NULL 
       AND h.attribute9 IS NULL 
       AND h.attribute10 IS NULL 
ORDER  BY Upper(employee) ASC

[Updated on: Fri, 06 June 2014 13:34]

Report message to a moderator

Re: Converting SQL syntax [message #615676 is a reply to message #615675]

Fri, 06 June 2014 14:06

rchenna
Messages: 25
Registered: February 2008

Junior Member

We are integrating Actuate reporting tool and it is easy if the query is in ANSI SQL syntax.

My apologies for the additional comma in the query. I had to remove some of the columns to look simple.

SELECT ed.last_name || ', ' || ed.first_name AS Employee,
       d.desc_short,
       pf.perf_rating_amount AS rating,
       ROUND((NVL(fs.salary_actual, 0) * cr.cur_exchange_rt), 0) AS current_salary,
       ROUND((NVL(eb.bonus_amount, 0) * cr.cur_exchange_rt), 0) AS disc_var_pay
  FROM fr_employees h, fr_emp_details ed, fr_departments d, fr_emp_performances ep, 
       fr_perf_ratings pf, fr_emp_salaries fs, fr_currency_rates cr, fr_periods p, 
       fr_emp_bonuses eb
 WHERE h.person_seq_id = ed.person_seq_id
   AND ed.dept_seq_id = d.dept_seq_id
   AND ed.person_seq_id = ep.person_seq_id (+)
   AND ed.period_seq_id = ep.period_seq_id (+)
   AND ep.perf_rating_seq_id_1 = pf.perf_rating_seq_id(+)
   AND ed.person_seq_id = fs.person_seq_id (+)
   AND ed.period_seq_id = fs.period_seq_id (+)
   AND cr.from_curr_cd = ed.currency_cd_host
   AND cr.to_curr_cd = 'USD'
   AND ed.person_seq_id = eb.person_seq_id (+)
   AND ed.period_seq_id = eb.period_seq_id (+)
   AND ed.period_seq_id = p.period_seq_id
   AND cr.period_seq_id = p.period_seq_id
   AND p.period_status = 'CURRENT'
   AND ( NVL(ed.excluded_flag,'N') = 'N')
   AND ( NVL(ed.security_restricted,'N') = 'N')
   AND h.attribute1 = '1149 - Propulsion'
   AND h.attribute2 is null
   AND h.attribute3 is null
   AND h.attribute4 is null
   AND h.attribute5 is null
   AND h.attribute6 is null
   AND h.attribute7 is null
   AND h.attribute8 is null
   AND h.attribute9 is null
   AND h.attribute10 is null
 ORDER BY UPPER(Employee) ASC

Report message to a moderator

Re: Converting SQL syntax [message #615677 is a reply to message #615673]

Fri, 06 June 2014 14:09

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

rchenna wrote on Fri, 06 June 2014 19:19

I have referred couple of examples on google but could not find any appropriate SQL that matches my requirement. There are two problems.

1. I could not find any example that shows one table (fr_emp_details) joins with multiple others tables (fr_periods, fr_emp_performances, fr_emp_salaries, fr_emp_bonuses) with multiple join conditions.
2. Some of them in this list are outer joins.

I have to make changes to around 60 SQL queries. I would rather spend time on converting other SQL queries once I somebody help me for this query.

You couldn't find an example? Well, OK:

select * from emp join dept using (deptno) left outer join bonus using (ename);

Report message to a moderator

Previous Topic:	single query by which data can fetched from table which name generated from a query
Next Topic:	pipeline?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 18:39:45 CDT 2024