Home » SQL & PL/SQL » SQL & PL/SQL » T-Sql to Pl/Sql (Oracle11g)
T-Sql to Pl/Sql [message #569541] Mon, 29 October 2012 06:08 Go to next message
urko666
Messages: 2
Registered: October 2012
Location: Slovenia
Junior Member
Hello. I need some help with transformation from T-Sql to Pl/Sql for the folowing queries.

--1st querry

select cast ((A.AA/B.BB) AS DECIMAL(10,2)) as "Frequency of absences per employee in 2012"
from (select SUM (value) as AA from pp_calc WHERE VP=23 and year(getdate())=2012) A,
(select COUNT (no) as BB from users) B


--2st querry

select x.Userno, x.Firstname, x.Lastname, cast (x.number /(y.number+x.number)*100 AS DECIMAL(10,2))as "% of Absence"
from
(
SELECT c.userno, u.FIRSTNAME, u.lastname, sum (c.value)/60 as number
FROM pp_calc c, users u
WHERE u.NO=c.USERNO and c.VP=22 and year(getdate())=2012
group by c.userno, u.FIRSTNAME, u.lastname
) x
join
(
SELECT c.userno, u.FIRSTNAME, u.lastname, sum (c.value)/60 as number
FROM pp_calc c, users u
WHERE u.NO=c.USERNO and c.VP=0 and year(getdate())=2012
group by c.userno, u.FIRSTNAME, u.lastname
)y on x.userno = y.userno
group by x.USERNO, x.Firstname, x.Lastname, cast (x.number /(y.number+x.number)*100 AS DECIMAL(10,2))
order by cast (x.number /(y.number+x.number)*100 AS DECIMAL(10,2)) desc
Re: T-Sql to Pl/Sql [message #569543 is a reply to message #569541] Mon, 29 October 2012 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try it in Oracle?

Regards
Michel
Re: T-Sql to Pl/Sql [message #569557 is a reply to message #569543] Mon, 29 October 2012 09:02 Go to previous messageGo to next message
urko666
Messages: 2
Registered: October 2012
Location: Slovenia
Junior Member
Yes, I removed t-sql functions from first query:

select (A.AA/B.BB) as 111
from (select SUM (value) as AA from pp_calc WHERE VP=23 A,
(select COUNT (no) as BB from users) B


and got: ORA-00923: FROM keyword not found where expected.



Re: T-Sql to Pl/Sql [message #569558 is a reply to message #569557] Mon, 29 October 2012 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 22691
Registered: January 2009
Senior Member
it appears the "(" do not match ")"

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: T-Sql to Pl/Sql [message #569560 is a reply to message #569557] Mon, 29 October 2012 09:16 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A ) is missing between VP=23 and A.

Regards
Michel
Previous Topic: XML encoding to UTF-8 charset - Oracle 9i
Next Topic: Query where condition has 4 rows in table but result is zero rows
Goto Forum:
  


Current Time: Fri Aug 22 22:57:09 CDT 2014

Total time taken to generate the page: 0.05311 seconds