Can you please let me know how to convert the below sql server code to Snowflake, since Snowflake doesn't support correlated sub queries.
SELECT DISTINCT
CURRENT_DATE as creationdate, LastName,
(SELECT Date AS Exp1
FROM PSU AS P
WHERE (HQL = a.B1L) AND (HQH = a.B1H) AND
(HQN =
(SELECT MAX(HQN) AS Exp1
FROM PSU AS P2
WHERE (HQL = a.B1L) AND (HQH = a.B1H) AND (HQZ = 'P') AND (HQF = 'P'))))AS "paymentDATE",
(CASE WHEN (SELECT MAX(H6P)
FROM sss
WHERE H6P = B1L AND H6CPS NOT IN ('H', 'C')) IS NULL THEN 'N' ELSE 'Y' END) AS "ORDER(Y/N)"
FROM ACT AS a INNER JOIN
CU AS c ON c.BSL = a.B1L INNER JOIN
CS AS cs ON cs.B2L = a.B1L AND a.B1H = cs.B2H
WHERE (a.B1M <> 0) AND (cs.B2K IN ('H'))
Snowflake code I created for the above code, I didn't create a CTE yet for "ORDER(Y/N)" field: I feel the joins are not correct in CTE and not sure how to join the CTE to main query
WITH RECURSIVE PaymentDate AS
(SELECT MAX(HQN) AS Expr1
FROM PSU AS P2
JOIN POP AS a ON (P2.HQL = a.B1L) AND (P2.HQH= a.B1H) AND (P2.HQZ = 'P') AND (P2.HQF= 'P')
)
, LPD AS
(SELECT Date as Exp1 FROM
PSU AS P
JOIN ACT AS A ON (P.HQL = a.B1L) AND (P.HQH = a.B1H)
JOIN TNR ON (P.HQN =TNR.Expr1)
)
SELECT DISTINCT CURRENT_DATE as creationdate, LastName
(CASE WHEN (SELECT MAX(H6P) FROM sss WHERE H6P = B1L AND H6CPS NOT IN ('H', 'C'))
IS NULL THEN 'N' ELSE 'Y' END) AS "ORDER(Y/N)"
FROM ACT AS a INNER JOIN
CU AS c ON c.BSL = a.B1L
INNER JOIN CS AS cs ON cs.B2L = a.B1L AND a.B1H = cs.B2H
WHERE (a.B1M <> 0) AND (cs.B2K IN ('H'))
If you re-write both sub-queries as CTEs that should work.
First CTE gets these values: HQL, HQH, MAX(HQN)
Second CTE joins PSU to the first CTE to get the "paymentDATE"
Join the 2nd CTE into your main query