Home » SQL & PL/SQL » SQL & PL/SQL » Oracle SubQuery
Oracle SubQuery [message #340756] Thu, 14 August 2008 02:49 Go to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
I have one query that has been converted from microsoft access to oracle, which is this one:

SELECT LMS_CLASSES.CLASS_DATE, LMS_CLASSES.ClASS_NAME, LMS_CLASSES.UNITS, COUNT(LMS_STUDENT_CLASS.STAFF_ID)
AS "CountOfStudentID"
FROM LMS_CLASSES INNER JOIN LMS_STUDENT_CLASS ON LMS_CLASSES.CLASS_ID=LMS_STUDENT_CLASS.CLASS_ID WHERE ((LMS_CLASSES.YEAR=2008))
GROUP BY LMS_CLASSES.CLASS_DATE, LMS_CLASSES.CLASS_NAME, LMS_CLASSES.UNITS;

I want to convert this query to oracle as well:

SELECT Sum([june08_class_totHours].[Units]*[june08_class_tothours].[CountOfStudentID]) AS TOT_HOURS
FROM june08_class_tothours;

but the problem is that there is no table called 'june08_class_totHours'. so I need to use the first query and make a subquery.

SELECT SUM (ABC.UNITS * ABC.CountOfStudentID) AS TOT_HOURS FROM
(SELECT LMS_CLASSES.CLASS_DATE, LMS_CLASSES.ClASS_NAME, LMS_CLASSES.UNITS,
COUNT(LMS_STUDENT_CLASS.STAFF_ID) AS "CountOfStudentID"
FROM LMS_CLASSES INNER JOIN LMS_STUDENT_CLASS ON LMS_CLASSES.CLASS_ID=LMS_STUDENT_CLASS.CLASS_ID
WHERE (((LMS_CLASSES.YEAR)=2008))
GROUP BY LMS_CLASSES.CLASS_DATE, LMS_CLASSES.CLASS_NAME, LMS_CLASSES.UNITS AS ABC;

But it doesnt work. I appreciate if you could help me to solve the problem Smile
Re: Oracle SubQuery [message #340758 is a reply to message #340756] Thu, 14 August 2008 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What error do you get?
Re: Oracle SubQuery [message #340759 is a reply to message #340758] Thu, 14 August 2008 03:13 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Please format the post.

These kind of posts should be ./fa/3415/0/

Regards,
Rajat
Re: Oracle SubQuery [message #340760 is a reply to message #340756] Thu, 14 August 2008 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Oracle SubQuery [message #340862 is a reply to message #340756] Thu, 14 August 2008 12:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
"As" is only used with column aliases, not table aliases and you need to enclose the sub-query in parentheses. If you count the number of left parentheses and the number of right parentheses, they should match. Also, if you enclose something in double quotes, like "CountOfStudentID" when you first create or declare it, it makes it case sensitive so you must enclose it in double quotes anywhere that it is referenced. The following code is untested since you did not provide create table or insert statements for sample data.

SELECT SUM (ABC.UNITS * ABC."CountOfStudentID") AS TOT_HOURS 
FROM   (SELECT LMS_CLASSES.CLASS_DATE, LMS_CLASSES.ClASS_NAME, LMS_CLASSES.UNITS,
               COUNT (LMS_STUDENT_CLASS.STAFF_ID) AS "CountOfStudentID"
        FROM   LMS_CLASSES INNER JOIN LMS_STUDENT_CLASS ON LMS_CLASSES.CLASS_ID=LMS_STUDENT_CLASS.CLASS_ID
        WHERE  LMS_CLASSES.YEAR = 2008
        GROUP  BY LMS_CLASSES.CLASS_DATE, LMS_CLASSES.CLASS_NAME, LMS_CLASSES.UNITS) ABC;

Re: Oracle SubQuery [message #340907 is a reply to message #340862] Thu, 14 August 2008 20:10 Go to previous messageGo to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi all,

sorry for the bad formatted SQL, I tried to use the SQL formatter but it did not format all my statements.(sorry I`m new to this forum and I should have read the guidelines before!)

Appreciate your all replies, I solved my problem.
Re: Oracle SubQuery [message #340928 is a reply to message #340907] Fri, 15 August 2008 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And it is a good and appreciable practice to post and explain the solution you found for future readers.

Regards
Michel
Re: Oracle SubQuery [message #340936 is a reply to message #340928] Fri, 15 August 2008 01:30 Go to previous messageGo to next message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
Hi,
sure Smile, here is my statement, I just named them, I also gave a name to the inner select statement.

Thanks for all replies. Smile

SELECT SUM(Units * CountOfStudentId) Tot_Hours
FROM (SELECT lms_Classes.Class_Date,
lms_Classes.Class_Name,
lms_Classes.Units,
COUNT(lms_Student_Class.Staff_Id) CountOfStudentId
FROM lms_Classes
INNER JOIN lms_Student_Class
ON lms_Classes.Class_Id = lms_Student_Class.Class_Id
WHERE ((lms_Classes.YEAR = 2008))
GROUP BY lms_Classes.Class_Date,
lms_Classes.Class_Name,
lms_Classes.Units) abc


(I actually use the SQL formatter to make the indention for my statements but when I get the preview the indentions ar egone Sad, then I`m sorry if it looks messy)
Re: Oracle SubQuery [message #340937 is a reply to message #340936] Fri, 15 August 2008 01:35 Go to previous message
meisam
Messages: 14
Registered: August 2008
Location: malaysia
Junior Member
my statement is just like Barbara`s one. thank you Barbara, while I was working on it, I really did not notice you have sent me the codes, I did the same way you have explained above.

SELECT SUM(Units * CountOfStudentId) Tot_Hours
FROM (SELECT lms_Classes.Class_Date,
             lms_Classes.Class_Name,
             lms_Classes.Units,
             COUNT(lms_Student_Class.Staff_Id) CountOfStudentId
     FROM    lms_Classes
             INNER JOIN lms_Student_Class
              ON lms_Classes.Class_Id = lms_Student_Class.Class_Id
    WHERE   ((lms_Classes.YEAR = 2008))
    GROUP BY lms_Classes.Class_Date,
             lms_Classes.Class_Name,
             lms_Classes.Units) abc 


this one looks better.
I just put it in between the code tags Smile

[Updated on: Fri, 15 August 2008 01:42]

Report message to a moderator

Previous Topic: Calender for particular year including US holiday names
Next Topic: ORA-29283: invalid file operation
Goto Forum:
  


Current Time: Sat Dec 03 16:20:19 CST 2016

Total time taken to generate the page: 0.18071 seconds