Subqueries in the ON clause are not supported

Moderator: NorbertKrupa

Post Reply
User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

Subqueries in the ON clause are not supported

Post by bud » 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?
Bud Anderson

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Subqueries in the ON clause are not supported

Post by JimKnicely » 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
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
bud
Newbie
Newbie
Posts: 14
Joined: Fri Oct 11, 2013 1:45 am

Re: Subqueries in the ON clause are not supported

Post by bud » Thu Dec 19, 2013 3:32 pm

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

scutter
Master
Master
Posts: 301
Joined: Tue Aug 07, 2012 2:15 am

Re: Subqueries in the ON clause are not supported

Post by scutter » 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;
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1778
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Subqueries in the ON clause are not supported

Post by JimKnicely » Thu Dec 19, 2013 5:38 pm

That's even better! :D :D :D
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

Matuxxalen
Newbie
Newbie
Posts: 1
Joined: Tue Nov 21, 2017 11:39 am

Re: Subqueries in the ON clause are not supported

Post by Matuxxalen » 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!!

Post Reply

Return to “Vertica SQL”