Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79

    Question Unanswered: Using aliases in calculations

    Hi

    I am trying to put together a large query that produces almost 130 columns, and some of those columns are just calculated values from columns that are just extracted data. For example, I am creating columns 1 and 2 by extracting data from a table, and column 3 is the sum of columns 1 and 2. To do this, I am giving columns 1 and 2 aliases, and attempting to calculate column 3 by referencing these aliases.

    When I try to compile a stored procedure that has this query in it, I get the error:

    PL/SQL: ORA-00904: "RRPAW": invalid identifier

    so I know that Oracle isn't going to let me use the aliases in the calculation. The 3 lines that are relevant to the error above are:

    sum(DECODE(pmms_region, 'West', rtl_refi_rate_am))/sum(DECODE(pmms_region, 'West', rtl_refi_qt)) rrraw,
    ...
    sum(DECODE(pmms_region, 'West', rtl_refi_pts_am))/sum(DECODE(pmms_region, 'West', rtl_refi_qt)) rrpaw,
    ...
    rrraw + (0.25 * rrpaw)

    Does anyone have a suggestion how I can do this without putting the complete lines of SQL into the line with the calculation?

    Thank you

    Michael

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Might an "extra" set of parenthesis prevent the error message?
    >sum(DECODE(pmms_region, 'West', rtl_refi_pts_am))/sum(DECODE(pmms_region, 'West', rtl_refi_qt)) rrpaw,

    (sum(DECODE(pmms_region, 'West', rtl_refi_pts_am))/sum(DECODE(pmms_region, 'West', rtl_refi_qt))) rrpaw,
    so the alias is applied to the resultant division operation?

  3. #3
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Using aliases in calculations

    select rraw, rrpaw, rrraw + (0.25 * rrpaw)
    from
    (
    Select sum(DECODE(pmms_region, 'West', rtl_refi_rate_am))/sum
    (DECODE(pmms_region, 'West', rtl_refi_qt)) rrraw,
    ...
    sum(DECODE(pmms_region, 'West', rtl_refi_pts_am))/sum(DECODE
    (pmms_region, 'West', rtl_refi_qt)) rrpaw,
    ... from ....
    )

    Hope this help you

  4. #4
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Unfortunately not. I tried parentheses in various places hoping that this would fix things. I even tried removing the line where the error occurred to see if an error still occurred, but the error simply moved to the next statement with an alias error.

  5. #5
    Join Date
    Feb 2004
    Location
    Charlotte, NC
    Posts
    79
    Using subqueries is going to be a huge performance hit on this stored procedure as it has 126 columns being produced, so I would like to look for a solution that avoided this. Thanks for the suggestion though.

Posting Permissions

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