| Substitution in a select statement [message #8387] |
Thu, 14 August 2003 12:39  |
JOHN
Messages: 182
Registered: April 1998
|
Senior Member |
|
|
What I have is two columns of information that I need to compare in a select statement and return a seperate value.
Example Columns; emp, empno, empfreq, empfrequnit, empdate.
I need to select all of the columns based on the empdate, but when empfreq = 1 and empfrequnit = WEEKS I need to return the value WEEKLY or if empfreq = 3 and empfrequnit = MONTHS I need to return the value MONTHLY, and etc. There are a limited number of distinct variables that will not change, but I need perform the comparison and value return on each selected row.
I've been playing with it, but nothing I try seems to fit the bill. Using variables and if-then-elses seem to be what I want, but for the life of me, I can't work it out.
This is simply a select statement and while I could perform the conversion in another program I'd prefer to do it in the initial select only.
Any help greatly appreciated.
|
|
|
|
| Re: Substitution in a select statement [message #8388 is a reply to message #8387] |
Thu, 14 August 2003 13:09  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Since you didn't provide any version information, I will assume you are on 8.1.6 or later.
select emp, empno, empfreq, empfrequnit, empdate,
case
when empfreq = 1 and empfrequnit = 'WEEKS' then 'WEEKLY'
when empfreq = 3 and empfrequnit = 'MONTHS' then 'MONTHLY'
end calc_column_name
from emp
where empdate ...; |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
