Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Select A Subset Of Data From The Dataset Returned By A Union Query

    hi all

    Any day, another question. I love you guys.

    I want to select a subset of data from a dataset returned by either another subquery or a union.

    e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.

    select * from
    (
    select col1, col2, col3 from table1
    union
    select col1, col2, col3 from table 2
    )
    where col1 = 'blah'

    in essence oracle treats the data returned by the subquery as a table that it would select from.


    how would i do the same in mssql?

    thank you


    James

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    exactly the same way, with 1 additional character after closed parenthesis:

    select * from
    (
    select col1, col2, col3 from table1
    union
    select col1, col2, col3 from table 2
    ) z
    where col1 = 'blah'

  3. #3
    Join Date
    Aug 2003
    Posts
    111
    Thanx for that

    but it doesn't seem to work
    what does the z mean?

    I tried the following where date and trans_date are datetime, and received and item_total are numbers.

    select transaction_date, amount from
    (
    select date transaction_date, received amount from topcat.MMTRANS$
    union
    select trans_date transaction_date, item_total amount from topcat.class_transaction
    ) z


    i get this error

    Server: Msg 8114, Level 16, State 5, Line 1
    Error converting data type varchar to float.


    cheers
    James

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    could you also point me to some resources that explains this type of query. I don't know what this type of query is called so i haven't been successful searching for examples of it online.

    Cheers
    James

  5. #5
    Join Date
    Sep 2003
    Posts
    522
    select transaction_date, amount from
    (
    select transaction_date=[date] , amount = received from topcat.MMTRANS$
    union
    select trans_date, item_total from topcat.class_transaction
    ) z


    "z" is an alias for derived table (that's how it's called)

    table aliases can also be specified like "(select ... from ...) as z"

    column aliases can be specified like "column_name as column_alias" or "column_alias = column_name"

  6. #6
    Join Date
    Aug 2003
    Posts
    111
    thanx for the explaination
    much appreciated.

    James

Posting Permissions

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