Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: To find difference

    I have a table like,

    NAME SPEND cond
    -----------------------------
    XXX 50.10 aaa
    XXX 10.10 bbb
    YYY 20 aaa
    YYY 5 bbb
    ZZZ 35 aaa
    ZZZ 15 bbb


    I have a condition for each name,
    if Cond='aaa' then i need a field added to the resultset with Spend value as Cond1
    if cond='bbb' then i need a field added to the resultset with Spend value as Cond2



    NAME cond1 cond2 diff
    ----------------------------------
    XXX 50.10 10.10 40
    YYY 20 5 15
    ZZZ 35 15 20

    I tried with this,

    select name, cond1, cond2, diff = cond1-cond2
    from (
    select name, cond1 = max(case when cond = 'aaa' then spend else 0 end),
    cond2 = max(case when cond = 'bbb' then spend else 0 end),
    from tbl
    group by Name
    ) a

    But i m getting,

    ORA-00923: FROM keyword not found where expected
    00923. 00000 - "FROM keyword not found where expected"
    *Cause:

    Would appreciate if anyone could help.

    Thanks

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    diff = cond1-cond2
    Is invalid syntax
    What is that supposed to do?

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Hi,

    looks like a classical PIVOT query, which is demonstrated e.g. here: http://www.orafaq.com/wiki/PIVOT

    According to SQL you posted, you really should learn the SELECT statement syntax. It is described in SQL reference book, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/ It is incorrect to alias a column with those equal signs. How did you come to this?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    "diff", as an alias, can not be used that way. It comes behind a column (or an expression), such as
    Code:
    select name, cond1 - cond2 as diff
    from ...
    The same goes for cond1 and cond2 aliases.

  5. #5
    Join Date
    Nov 2010
    Posts
    3

    Unhappy To find difference

    Now modified my query like,

    select name, cond1, cond2, cond1-cond2 as diff
    from (
    select name, max(case when cond = 'aaa' then spend else 0 end as cond1),
    max(case when cond = 'bbb' then spend else 0 end as cond2),
    from tbl
    group by Name
    ) a

    i m getting,

    ORA-00911: invalid character
    00911. 00000 - "invalid character"
    *Cause: identifiers may not start with any ASCII character other than
    letters and numbers. $#_ are also allowed after the first
    character. Identifiers enclosed by doublequotes may contain
    any character other than a doublequote. Alternative quotes
    (q'#...#') cannot use spaces, tabs, or carriage returns as
    delimiters. For all other contexts, consult the SQL Language
    Reference Manual.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Do you have by any chance double quotes around aaa ("aaa") instead of single quotes?
    Can you post the exact statement that you run (using copy & paste)?

    And please make sure you format your code using [code] tags!

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    It would help if you posted the whole error message including line and column numbers (depends on tool you are using). Just a guess - after copy/paste, some special character remained in text, for example hard space, special apostrophe symbol or something similar. Try to modify the query in position indicated by the error message (delete/add some characters) or re-write the query from scratch.

  8. #8
    Join Date
    Nov 2010
    Posts
    3
    Thanks to all.. I got an answer... I just removed '{}' instead i use '()' .

    Seems it a format error..


  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Where did you find { ?!? You never posted it before (in any of two pieces of code you provided).

    We'd notice the error if you posted actual SQL*Plus session (copy/paste). Posting inaccurate information makes it difficult to debug your programs so - try to remember for the next time - copy/paste is what you should provide.

Posting Permissions

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