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

    Unanswered: Incorrect syntax - ASE 12.5

    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 ')'.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    I've run your statement on a 12.5.4 ASE without any errors.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    May 2011
    Posts
    19
    hi aflorin27,

    Thank very much for your reply.

    I still get incorrect syntax error.
    I think ASE 12.5 version don't support this syntax,
    just like the left join have to use *= instead of "left join"

    I can't find the solution, do you know any other way to do the same query?

    thank.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    select a.pid, sum(a.Amount1), sum(a.Amount2)
    from
    (
    select pid, Amount1, 0 as Amount2
    from TableA
    union all
    select pid, 0 as Amount1, Amount2
    from TableB
    ) a
    group by a.pid;
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    May 2011
    Posts
    19
    Hi aflorin27,

    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

Posting Permissions

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