Use column in oracle subquery

Question

Example SQL, revolving this solves my case of the real problem.

In select below, I need to use the column mes in the where 1=1, but gives invalid identifier, how can I access that column in the subselect select ?

SELECT (select a 
        from ( SELECT 1 as a, 2 as b, 3 as c FROM dual where 1=1) 
        where 1 = mes) as teste          
FROM (select LEVEL as mes FROM Dual CONNECT BY Level <= 12)

Real case, using Daniel Mendes' tip

WITH MESES (MES) AS ( SELECT LEVEL AS MES FROM DUAL CONNECT BY LEVEL <= 12 )


SELECT (SELECT codvend FROM( SELECT
    cab.codvend,
    DECODE((select NVL(m.prevrec, 0) from tgfmet m where m.codvend = CAB.CODVEND and m.dtref = '01/06/2019'), 0, 0,
        ROUND(NVL(sum(DECODE(CAB.TIPMOV, 'D', cab.vlrnota*-1, cab.vlrnota)), 0) / (select NVL(m.prevrec, 0) from tgfmet m where m.codvend = CAB.CODVEND and m.dtref = '01/06/2019') * 100, 2) 
    ) as porcentagem_mes

    FROM TGFCAB CAB WHERE 
    CAB.dtneg BETWEEN '01/06/19' AND '30/06/19' 
    AND CAB.CODVEND = MESES.MES
    AND (CAB.TIPMOV = 'V' OR CAB.TIPMOV = 'D')
    AND cab.codtipoper in (5,6,76)
    AND cab.statusnota = 'L' GROUP BY cab.codvend ORDER BY porcentagem_mes DESC 
)  WHERE ROWNUM <= 1  ) a



FROM MESES

In AND CAB.CODVEND = MESES.MES the month is not yet available, there is more hint of why I can not use it there?

Answer 1

Igor, then use WITH to create this query, it is a great way to work with subquery:

WITH MESES (MES) AS ( SELECT LEVEL AS MES FROM DUAL CONNECT BY LEVEL <= 12 )
SELECT (SELECT A
          FROM (SELECT 1 AS A,2 AS B,3 AS C
                  FROM MESES WHERE MESES.MES=1)
         WHERE 1 = MESES.MES) AS TESTE
  FROM MESES

I hope that way you’ll serve him.

About WITH: https://oracle-base.com/articles/misc/with-clause