-
On the Fly Table Name and Columns
I need help for the following query:
-----------------------------------------
It is about finding rank of student on the basis of Total Marks, Marks Of "Important Subject", and Registration Number:
Table StudentMarks:
------------------------
RegNo Name Sub1 Sub2 Sub3
101 Stud1 30 40 50
103 Stud3 40 30 50
102 Stud2 30 20 20
Ranking
---------
1. Sum of marks
2. If Sum is same then on the basis of the Marks Obtain in the Important Subject
3. I the above two are same then the student having least registration number
The query will be
Select RegNo, Name, RowNum as Rank
From ( Select RegNo, Name
From StudentMarks
order by (sub1 + sub2 + sub3) DESC
, Sub1 desc /* Sub1 is the "Important Subject"
, RegNo Asc
);
Question:
Now the problem is I want to provide Table Name, and Subject Column names On the Fly i.e. that should be as an argument to the query.
is it possible.
Note: The number of subjects and the position of the "Important subject" may differ.
Thanks
Last edited by mehulrajani; 02-24-2004 at 08:01 AM.
-
Use dynamic SQL.
Create a procedure which accepts table_name and subject(s) as input. Then build a query sting and use execute immediate option to execute the query and fetch results.
-
Thanks,
I know that it can be solved by dynamic sql. Can you me the code please.
Thanks
-
Hi
Look at the documentation for
1)DBMS_SQL
2)execute immediate
regards
Hrishy
-
Originally posted by mehulrajani
Thanks,
I know that it can be solved by dynamic sql. Can you me the code please.
Thanks
Hmm...
regards
Hrishy
-
Thanks
Thanks for the help
With Regards
Mehul
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|