Dependent CASE function of another CASE function in a SELECT statement

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • EORTIZ
    New Member
    • Sep 2007
    • 22

    #1

    Dependent CASE function of another CASE function in a SELECT statement

    I would like to know the way that I can write a CASE function in a select statement that uses another variable that is created in the same SELECT statement using another CASE function. This is exacly what I am trying to do but obviusly gives me an error because is incorrect:

    SELECT
    CSV_SRV_QTY,
    NET_PAID,
    case when NET_PAID=0 THEN 0 ELSE CSV_SRV_QTY END AS ADJUNITS1,
    CASE WHEN NET_PAID<0 AND ADJUNITS1>0 THEN -(ADJUNITS1) ELSE ADJUNITS1 END AS ADJUNITS2
    FROM
    CSV_MSTR
    ;

    ADJUNITS1 runs fine but when add the second variable (ADJUNITS2) it produces an error.
  • EORTIZ
    New Member
    • Sep 2007
    • 22

    #2
    NEVER MIND, HERE IS THE ANSWER. MAYBE SOMEONE IS INTERESTED
    [CODE=oracle]
    select units, total_paid, adjunits1,
    CASE WHEN total_paid<0 AND ADJUNITS1>0 THEN -(ADJUNITS1) ELSE ADJUNITS1 END AS ADJUNITS2 from
    (SELECT
    CSV_MSTR.CSV_SR V_QTY as units,
    CSV_MSTR.NET_PA ID as total_paid,
    case when NET_PAID=0 THEN 0 ELSE CSV_SRV_QTY END AS ADJUNITS1

    FROM
    CSV_MSTR )[/CODE]
    Last edited by debasisdas; Mar 17 '08, 05:02 AM. Reason: added code=oracle tags

    Comment

    Working...