Hive Join with CTE

Question

Can someone help me to add the commented Join Condition in the below query. If I am executing this query is working fine but when I am trying to add one more join codition which is commented in the below query its not able to access the column high_V2 and giving column/table alisa not found issue.

with MYTABLE as (
select
  T1.LOW_V2 as LOW_V2,
  T2.LOW_V as LOW_V,
  T2.HIGH_V as HIGH_V 
  from TAB1 T1 
  inner join 
  TAB2 T2
  on 
    T1.LOW_V2=T2.LOW_V 
    -- and high_V2=T2.HIGH_V
    ) 
select lpad(concat(splitted[0],translate(splitted[1],'0','9')),18,0) as high_V2,
LOW_V2,LOW_V,HIGH_V from 
   (
select  split(regexp_replace(LOW_V2,'(\\d*?)(0+)$','$1|$2'),'\\|') splitted,
 LOW_V2, 
 LOW_V,HIGH_V 
 from MYTABLE )s;

score 0 · Answer 1 · answered Mar 09 '22 at 07:48

The order of operations doesn't work like that

The CTE is processed first. At the time it is processed high_v2 doesn't exist

Then the sub query (select split) is processed, then the lead query is processed (select lpad). It is the lead query that creates high_v2

It might be simpler for you to write multiple CTE so that they are processed from top down. This way you can more easily see the order. You can only use things in later CTE if you carry them through from an earlier one

with MYTABLE as (
select
  T1.LOW_V2 as LOW_V2,
  T2.LOW_V as LOW_V,
  T2.HIGH_V as HIGH_V 
  from TAB1 T1 
  inner join 
  TAB2 T2
  on 
    T1.LOW_V2=T2.LOW_V 
), 

s AS (
 select    
  split(regexp_replace(LOW_V2,'(\\d*?)(0+)$','$1|$2'),'\\|') splitted,
  LOW_V2, 
  LOW_V,HIGH_V 
 from 
  MYTABLE
),

t AS (

 select   
  lpad(concat(splitted[0],translate(splitted[1],'0','9')),18,0) as high_V2,
  LOW_V2,
  LOW_V,
  HIGH_V 
 from 
  s
)

SELECT * from t WHERE high_v = high_v2

Can you see how the flow is top to bottom and each successive CTE only uses things from the previous one..

Hive Join with CTE

1 Answers1

Linked