CODEWITHSUNDEEP
sqljoincase
P3P0
P3P0

Reputation: 165

Join On conditional column SQL

I'm trying to do a LEFT join on a created column(y_prcs_new) in SQL but I get the next error: "Column 'y_prcs_new' cannot be resolved"

This is the query:

SELECT 
sh.i_rqst,
sh.y_rqst,
sh.c_close_reason,
CASE WHEN sh.y_prcs IS NULL THEN 3
     WHEN sh.y_prcs = 1 AND  sh.c_close_reason = 5 THEN 3
     WHEN (sh.y_prcs IS NOT NULL OR sh.y_prcs != 1) AND sh.c_close_reason != 5  THEN sh.y_prcs
END AS y_prcs_new,
fr.t_close_reason

FROM st_hist sh

LEFT JOIN
        (SELECT DISTINCT fr.c_close_reason, fr.t_close_reason, fr.y_prcs
        FROM sRsn fr) AS fr
        ON sh.c_close_reason = fr.c_close_reason 
        AND y_prcs_new = fr.y_prcs

Do you know how can fix it?

Upvotes: 0

Views: 44

Answers (1)

zealous
zealous

Reputation: 7503

You query is incorrect as join statement executes first and then select statement in the end of the query, so on join query could not find y_prcs_new.

select   
  i_rqst,
  y_rqst,
  c_close_reason,
  y_prcs_new,  
  t_close_reason
from
(
  SELECT 
    i_rqst,
    y_rqst,
    c_close_reason,
    CASE WHEN y_prcs IS NULL THEN 3
         WHEN y_prcs = 1 AND  c_close_reason = 5 THEN 3
         WHEN (y_prcs IS NOT NULL OR y_prcs != 1) AND c_close_reason != 5  THEN y_prcs
    END AS y_prcs_new

  FROM st_hist
) sh
LEFT JOIN
( SELECT 
    DISTINCT c_close_reason, 
    t_close_reason, 
    y_prcs
  FROM sRsn
) fr
ON sh.c_close_reason = fr.c_close_reason 
AND sh.y_prcs_new = fr.y_prcs

Upvotes: 1

Related Questions