Page 1 of 2
Output of one Case statement to another
Posted: Tue May 28, 2013 9:58 am
by nnani
Hello All,
I have a query wherein there are multiple case statements in the select clause
The simpe SQL query works fine
Code: Select all
select pm_id,pm_name,
case when regexp_like(pm_name,'((ab)|(kfd)|(nm))')
then 'yes'
else 'out'
end as result
from tempdb.litab;
pm_id | pm_name | result
-------+----------+--------
1 | abkd | yes
2 | dfgkl | out
3 | kmnolk | out
4 | zzzxxbsd | out
(4 rows)
Now the select query which I have does parse the result of one case statement to another OR the result of one case statement is used with the another statement in the expression clause
Code: Select all
select pm_id,pm_name,
case when regexp_like(pm_name,'((ab)|(kfd)|(nm))')
then 'yes'
else 'out'
end as result,
case when result = 0
then 'g'
else 'b'
end as c_reult
from tempdb.litab;
ERROR 2624: Column "result" does not exist
Is this the observed behaviour in Vertica.
Are there any workaround for this..
Re: Ouput of one Case statement to another
Posted: Tue May 28, 2013 2:34 pm
by nnani
The possible workaround seen by me is
Code: Select all
select pm_id,pm_name,
result,
case
when result='yes'
then 0
else 1
end as c_result,
case when result='out'
then 'NA'
else 'out'
end as t_result
from
(
select pm_id, pm_name,
case when regexp_like(pm_name,'((ab)|(kfd)|(nm))')
then 'yes'
else 'out'
end as result
from
tempdb.litab)subq;
pm_id | pm_name | result | c_result | t_result
-------+----------+--------+----------+----------
1 | abkd | yes | 0 | out
2 | dfgkl | out | 1 | NA
3 | kmnolk | out | 1 | NA
4 | zzzxxbsd | out | 1 | NA
(4 rows)
Any more workarounds are welcome.....
Re: Ouput of one Case statement to another
Posted: Tue May 28, 2013 4:07 pm
by JimKnicely
Hi,
You can also embed a CASE within a CASE. Something like this:
Code: Select all
select pm_id,
pm_name,
result,
case
when (case
when regexp_like(pm_name,'((ab)|(kfd)|(nm))') then 'yes'
else 'out'
end) = 'yes' then 0
else 1
end as c_result,
case
when (case
when regexp_like(pm_name,'((ab)|(kfd)|(nm))') then 'yes'
else 'out'
end) = 'out' then 'NA'
else 'out'
end as t_result
from tempdb.litab;
Note: I didn't run this select, but it should work
Re: Ouput of one Case statement to another
Posted: Tue May 28, 2013 7:28 pm
by id10t
Hi!
Code: Select all
daniel=> select * from nnani;
id | name
----+--------
7 | ab----
8 | --ab--
9 | ----ab
10 | xy----
11 | --xy--
12 | ----xy
(6 rows)
Code: Select all
select *,
regexp_like(name, '^(ab)|(xy)$')::int as result,
decode(regexp_like(name, '^(ab)|(xy)$'), True, 'YES', 'OUT') as c_result,
decode(regexp_like(name, '^(ab)|(xy)$'), True, 'OUT', 'NA') as t_result
from nnani;
Code: Select all
daniel=> \e
id | name | result | c_result | t_result
----+--------+--------+----------+----------
7 | ab---- | 1 | YES | OUT
8 | --ab-- | 0 | OUT | NA
9 | ----ab | 0 | OUT | NA
10 | xy---- | 0 | OUT | NA
11 | --xy-- | 0 | OUT | NA
12 | ----xy | 1 | YES | OUT
(6 rows)
Re: Ouput of one Case statement to another
Posted: Tue May 28, 2013 7:32 pm
by JimKnicely
sKwa, that's a much better solution!
Re: Ouput of one Case statement to another
Posted: Tue May 28, 2013 7:35 pm
by id10t
Hi!
>> skWa, that's a much better solution!
Exactly same as yours (check explain), just little more compacted.
Re: Ouput of one Case statement to another
Posted: Wed May 29, 2013 6:55 am
by nnani
Hello guys,
In my scenarion, I am converting some TD scripts to Vertica. In TD the value of once case statement can be supplied as an input to next case statement,
while Vertica does not allow this.
The solution by knicely looks good to me. But the solution by skwa is not clear with me. As there is not computed column taken as a input to the next columns.
Skwa, Can you please explain me the solution.
Code: Select all
regexp_like(name, '^(ab)|(xy)$'[color=#4080FF])::int[/color] as result,
Also this casting is unclear with me. Can you please take time and explain me this.
Thanks in advance