The Vertica Database Forums

Posted: **Thu Dec 19, 2013 2:42 pm**

Hi all,

I am trying to left join two tables and return the rows from t1 and matching rows in t2 but only those that have a maximum date.

As an example, I have these two tables:

Code: Select all

dbadmin=> select * from t1;
  c1
-------
 data2
 data1
(2 rows)

Code: Select all

dbadmin=> select * from t2;
  c1   | c2 |     c3
-------+----+------------
 data1 |  1 | 2013-12-19
 data1 |  2 | 2013-12-20
(2 rows)

Here's a simple left join of t1 and t2:

Code: Select all

dbadmin=> select * from t1 left join t2 on t2.c1 = t1.c1;
  c1   |  c1   |     c2
-------+-------+------------
 data2 |       |
 data1 | data1 | 2013-12-19
 data1 | data1 | 2013-12-19
(3 rows)

But I really want this result set:

Code: Select all

  c1   | c2 |     c3
-------+----+------------
 data1 |  2 | 2013-12-20
 data2 |    |

I tried running the following query but got an error:

Code: Select all

dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 and t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
ERROR 4816:  Subqueries in the ON clause are not supported

Next I tried the following query, but I lost data that is in t1 but not t2

Code: Select all

dbadmin=> select t1.c1, t2.c2, t2.c3 from t1 left join t2 on t2.c1 = t1.c1 where t2.c3 = (select max(t3.c3) from t2 t3 where t3.c1 = t2.c1) order by t1.c1;
  c1   | c2 |     c3
-------+----+------------
 data1 |  2 | 2013-12-20
(1 row)

Is there a work around for this?

Posted: **Thu Dec 19, 2013 3:07 pm**

Hi Bud,

Maybe this query will work for you?

Code: Select all

select t1.c1, t2.c2, t2.c3
  from t1 left join t2
    on t2.c1 = t1.c1
 where nvl(t2.c3, '1900-01-01') = nvl((select max(t3.c3) from t2 t3 where t3.c1 = t2.c1), '1900-01-01')
 order by t1.c1;

That '1900-01-01' data is arbitrary. That idea is that if both sides of the equality are null, using the nvl function to return the same value on both sides (the arbitrary date) makes the condition true.

FYI, there is a related topic "How to simulate Subqueries in the ON Clause in Vertica" here:

http://www.vertica-forums.com/viewtopic.php?f=48&t=1077

Posted: **Thu Dec 19, 2013 3:32 pm**

Thanks for the quick response, Jim! Your solution works for me! Nice!

Posted: **Thu Dec 19, 2013 3:47 pm**

Simplifying JIm’s query, this should also work - removing the t3 alias and using <=> for null equality instead of nvl().

select t1.c1, t2.c2, t2.c3
from t1 left join t2
on t2.c1 = t1.c1
where t2.c3 <=> (select max(t2.c3) from t2 where t2.c1 = t1.c1)
order by t1.c1;

Posted: **Thu Dec 19, 2013 5:38 pm**

That's even better!

Posted: **Tue Nov 21, 2017 11:46 am**

Hi I try to throw query

select
client_id,
si,
brand_id,
px,
py,
pz,
sc,
test,
apMac,
la,
lo,
time_stamp
from
wfa_4.dim_client inner join wfa_4.raw_data_with_json on
mp =( select store_mp_id from wfa_4.dim_store where id = 7) and si = client

ERROR
SQL Error [4816] [0A000]: [Vertica][VJDBC](4816) ERROR: Subqueries in the ON clause are not supported
[Vertica][VJDBC](4816) ERROR: Subqueries in the ON clause are not supported
com.vertica.util.ServerException: [Vertica][VJDBC](4816) ERROR: Subqueries in the ON clause are not supported

can help me please!!

The Vertica Database Forums

Subqueries in the ON clause are not supported

Subqueries in the ON clause are not supported

Re: Subqueries in the ON clause are not supported

Re: Subqueries in the ON clause are not supported

Re: Subqueries in the ON clause are not supported

Re: Subqueries in the ON clause are not supported

Re: Subqueries in the ON clause are not supported