SQL to reference another column's value

Question

I would like to know how to reference the values from a column in my query for use in a adjacent column having a different column name. As you can see, I just don't see that it is necessary to place the case statement shown below twice in my code.

select acct, name, address,
case when pterm||' '||ptermc = '0' then date(digits(matdt7)) 
    when pterm||' '||ptermc = '1 D' then curdate() 
    when pterm||' '||ptermc = '1 M' then date(digits(prevd7))+30 day
    when pterm||' '||ptermc = '3 M' then date(digits(prevd7))+90 day
    when pterm||' '||ptermc = '6 M' then date(digits(prevd7))+180 day
    when pterm||' '||ptermc = '12 M' then date(digits(prevd7))+365 day
    else null end as "Next Repricing Date",

I would like the section below to just insert the values from the case section above.

phone, value from Next Repricing Date AS "balloon date"
from my.table

The reason it's not necessary is because the data's redundant and shouldn't be returned twice from the database. Adjust the code receiving the results of the query appropriately to use the one column for multiple purposes. — Joe Stefanelli
– Joe Stefanelli, Commented Jul 3, 2012 at 16:19
By the way, have you tried rewriting your CASE expression like this: case pterm||' '||ptermc when '0' then date(digits(matdt7)) when '1 D' then curdate() when '1 M' then …? That would let you avoid repeating the same expression multiple times, which would make your query simpler-looking and potentially more readable. — Andriy M
– Andriy M, Commented Jul 4, 2012 at 19:03

Richthofen · Accepted Answer · 2012-07-03 16:19:55Z

1

It isn't possible to reference an aliased column in the same select statement. You could use a subquery as so

SELECT q.ColA as "Next Repricing", q.ColA as "Balloon Date" FROM
(select acct, name, address,
case when pterm||' '||ptermc = '0' then date(digits(matdt7)) 
when pterm||' '||ptermc = '1 D' then curdate() 
when pterm||' '||ptermc = '1 M' then date(digits(prevd7))+30 day
when pterm||' '||ptermc = '3 M' then date(digits(prevd7))+90 day
when pterm||' '||ptermc = '6 M' then date(digits(prevd7))+180 day
when pterm||' '||ptermc = '12 M' then date(digits(prevd7))+365 day
else null end as ColA, ...) As q

Another option would be to write your own User Defined function if the case statement is a common transform.

answered Jul 3, 2012 at 16:19

Richthofen

2,07619 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adam Over a year ago

A nice alternative to a subquery is a common table expression (CTE).

Collectives™ on Stack Overflow

SQL to reference another column's value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related