Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2006
    Posts
    115

    Unanswered: sql a series of UNION

    hi guys,

    the following union sql cause syntax "The column prefix 'd' does not match with a table name or alias name used in the query ... "

    pls advise:

    select col_2 from (
    select col_2 from table1 where col_1=d.col_1
    union select col_2 from table2 where col_1=d.col_1 ) #tmp
    from table3 d

  2. #2
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    select col_2 from (
    select col_2 from table1 where col_1=d.col_1
    union select col_2 from table2 where col_1=d.col_1
    ) #tmp
    from table3 d
    The table-alias D is not visible in the bold subselect, causing the error to be displayed. If you want to use table3 in the subselect, you shouldd mention it in the union-clause.
    "union select c.col_2 from table2 c, table3 d where c.col_1 = d.col_1"


    What are you tryign to accomplish with the query?
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Jul 2006
    Posts
    115
    thx martijnvs, i want the sql devived table from union result can based on table3.col1.

    according to sybase manual, it seems not allow. any alternative?

    regards

  4. #4
    Join Date
    Jan 2004
    Posts
    545
    Provided Answers: 4
    You are looking for dynamic sql, and that should be possible. However, the subselect can only return 1 value, not more.

    You want col_2
    Code:
    select col_2
    from
    from a certain table, where you need the tablename to be determined dynamicly.
    Code:
    (
    select col_2 from table1 where col_1=d.col_1 
    union select col_2 from table2 where col_1=d.col_1 
    ) #tmp
    However, you already specified the table to select from below:
    Code:
    from table3 d
    So either you need to move from table3 d into the subselect, or lose the subselect entirely.
    I'm not crazy, I'm an aeroplane!

Posting Permissions

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