Output of one Case statement to another

[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..

[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.....

[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

[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)

[JimKnicely]

sKwa, that's a much better solution!

[id10t]

Hi!


>> skWa, that's a much better solution!
Exactly same as yours (check explain), just little more compacted.

[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