Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2010
    Posts
    4

    Unanswered: derived table problem

    Hello everyone

    i would like to do a query like this

    select * from ( select Name from Tables where TablesId = 10 )

    what i want is, to get the table name from a database table and get all rows in that table.. this does not work..

    i tried this as well
    select * from ( select Name from Tables where TablesId = 10 ) as TT

    and this is returning the data from select Name from Tables where TablesId = 10 but that is not been used in the select * from query.

    Hope i have explaned my question.. please help.. thanks in advance.

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by style_sybase_user View Post
    select * from ( select Name from Tables where TablesId = 10 ) as TT

    and this is returning the data from select Name from Tables where TablesId = 10
    Correct

    Quote Originally Posted by style_sybase_user View Post
    but that is not been used in the select * from query.
    False

    I guess you need to explain better. Maybe with an example of data that reproduce your problem

  3. #3
    Join Date
    Sep 2010
    Posts
    4
    Hello Thanks for your reply

    what i mean here is,

    the db table "Tables" have two columns with the below given data

    Name TablesId
    Employee 10
    Teachers 20
    Engineer 30
    Doctor 40

    the data in Name column are differnt tables in the same DB.

    so when I do "select Name from Tables where TablesId = 10 "

    it will return "Employee"

    and I want to do "select * from Employee"

    i need the whole thing in one line with select * from ( select Name from Tables where TablesId = 10 )

    and if i do

    select * from ( select Name from Tables where TablesId = 20 )

    the query I want to run would be "select * from Teachers"

    in short, a blind substitution for table name with a query, any similar work arounds would also be appreciated.

    Hope this is clearer now.

  4. #4
    Join Date
    Jun 2010
    Posts
    51
    I think Execute Immediate is what you want.

    Not tested:
    Code:
    declare @tblName varchar(50)
    
    select @tblName = Name from Tables where TablesId = 10
    
    exec ("select * from "+@tblName)
    That should do it.

    Let us know how it goes.

  5. #5
    Join Date
    Sep 2010
    Posts
    4
    Thanks very much this is what i wanted..

    what to do if I want is to get the output and process in cursor

  6. #6
    Join Date
    Jun 2010
    Posts
    51
    Please tell us the requirement 1st. It is not a good idea to run cursor. Even dynamic SQLs are not good idea. It's preferable if you can avoid the cursor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •