HP vertica SQL query to create new column

Tricky. I needed a little challenge today.

Part of the problem is that UserProgram doesn't contain information about program #3, even though you're output has it.  So, at the very least, I had to create a Programs table to store all the distinct programs.  If that's not a problem in your data set, then you could replace my lookup to Programs with a "select distinct program from UserProgram".

Then it's a matter of cartesianing the results together.  But my original attempt here didn't work, since whatever base table I had had a tendency to drive the result.  So, I puleld away, and use a dual cartesian from dual.  That solved it.

Solution:

dbadmin=> select * From userprogram ;
 user | program
------+---------
 A    |       4
 A    |       1
 B    |       4
 B    |       5
 B    |       2
(5 rows)

dbadmin=> select * from programs ;
 program | description
---------+-------------
       1 | program 1
       3 | program 3
       5 | program 5
       2 | program 2
       4 | program 4
(5 rows)

WITH cartesian AS
    (select users."user", programs.program
       from dual
       join (select distinct "user" from userprogram) as users on 1=1
       join (select program From programs) as programs on 1=1 )
select c."user", c.program
, case when c.program=up.program then 1 else 0 end
from cartesian c
left join userprogram up on c."user" = up."user" and c.program=up.program
order by 1,2 ;

 user | program | case
------+---------+------
 A    |       1 |    1
 A    |       2 |    0
 A    |       3 |    0
 A    |       4 |    1
 A    |       5 |    0
 B    |       1 |    0
 B    |       2 |    1
 B    |       3 |    0
 B    |       4 |    1
 B    |       5 |    1
(10 rows)

I do have some concerns about how well such a thing might scale. But so long as the projection on UserProgram is ordered by User,Program it should be fairly efficient. With Clause Materialization might also help the performance here, if need be.

Thank you for the reply !!! This works out beautifully