Results 1 to 4 of 4
  1. #1
    Join Date
    May 2011
    Posts
    19

    Unanswered: how to join two subquery?

    I am using Sybase ASE 12.5
    I have two table as follow

    TableA
    PID, Amount1
    1, 10
    1, 20
    2, 10
    2, 10
    3, 30

    TableB
    PID, Amount2
    1, 20
    1, 50
    2, 30
    2, 20

    SELECT one.pid
    , one.Amount1OfSum
    , two.Amount2OfSum
    FROM ( SELECT pid
    , SUM(Amount1) AS Amount1OfSum
    FROM TableA
    GROUP
    BY pid ) AS one,
    ( SELECT pid
    , SUM(Amount2) AS Amount2OfSum
    FROM TableB
    GROUP
    BY pid ) AS two
    where two.pid *= one.pid

    What I want the output is
    PID, Amount1, Amount2
    1, 30, 70
    2, 20, 50
    3, 30,

    Incorrect syntax near the keyword 'select'.
    Incorrect syntax near ')'.

    Thank.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The "*=" is not SQL syntax. You want to have a LEFT OUTER JOIN or FULL OUTER JOIN with a join predicate of "one.pid = two.pid".
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    May 2011
    Posts
    19
    Hi stolze,

    Thank very much for your reply.

    I finally got the answer.
    Sybase ASE 12.5.0.3 version is not support "derivedtable", so it is not the syntex problem.

    I have to do it by using temp table or sp

    Thank

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Well, aside from the "*=" stuff, your syntax is fine and acceptable standard SQL. It seems that your product deviates from the standard, however.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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