Q: Avoiding subquery Fellows,
I have a query as follows:
SELECT A.ID, B.ID, (HUGE SUBQUERY) as HS
FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
WHERE (HUGE SUBQUERY) > 0

I'd like to avoid repeating the subquery.
Is there any way to rewrite my WHERE as something like
WHERE HS > 0

Or I must turn my subquery into a join?

A: Look at With clause :
WITH HS AS (Huge subquery)

SELECT A.ID, B.ID, HS
FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
WHERE HS  > 0

OR
SELECT *
FROM
(
    SELECT A.ID, B.ID, (HUGE SUBQUERY) as HS
    FROM TABLE_A JOIN TABLE_B ON A.ID = B.ID
)
WHERE HS > 0


A: You could use a CTE:
WITH cteHS AS (
    SELECT xxx AS Value
        FROM Huge Subquery
)
SELECT A.ID, B.ID, cteHS.Value as HS
    FROM TABLE_A, cteHS
        JOIN TABLE_B ON A.ID = B.ID
    WHERE cteHS.Value > 0

