On the Fly Table Name and Columns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: On the Fly Table Name and Columns

Hybrid View

  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unhappy 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.

  2. #2
    Join Date
    Feb 2004
    Posts
    77
    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.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Thanks,
    I know that it can be solved by dynamic sql. Can you me the code please.
    Thanks

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Look at the documentation for

    1)DBMS_SQL

    2)execute immediate

    regards
    Hrishy

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    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

  6. #6
    Join Date
    Feb 2004
    Posts
    4

    Smile 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
  •  


Click Here to Expand Forum to Full Width