Field not recognized from inside inner query

From: Etantonio <etantonio_at_gmail.com>
Date: Thu, 20 Aug 2009 08:40:14 -0700 (PDT)
Message-ID: <7b9d3498-8def-41d7-9074-5a2bc1ba69c8_at_g19g2000vbi.googlegroups.com>

Good morning,
in the following query I've a problem with B.ABI_CAPOGRU that is not recognized inside the select max
this is probably due to the fact that inside the select max I've other select, how can I solve this problem ?
This query does exactly what I need but it doesn't work, may you help me ?

Antonio
www.etantonio.it/en

SELECT CONCAT(ABI_CAPOGRU,CONCAT('.',CONTROCOD_CAPOGRU)) ABI_CC_CAPOGRU ,  

B.PROG_PUBBL                                                           ,
 
B.DATA_RIF                                                             ,
 
B.DATAORA_PREL                                                         ,
 
B.ABI_CAPOGRU                                                          ,
 
B.CONTROCOD_CAPOGRU                                                    ,
 
B.DEN_GRUPPO                                                           ,


  (

        SELECT MAX(data_isc_gru) FROM
        (
          SELECT  DISTINCT X.data_isc_gru
             FROM TGRUBAN_INTERNET X
          WHERE X.ABI_CAPOGRU = B.ABI_CAPOGRU
          AND X.TIPO_PART       = 1
          minus
          SELECT  DISTINCT X.data_isc_gru
             FROM TGRUBAN_INTERNET X, TGRUBAN_INTERNET Y
          WHERE
              X.ABI_CAPOGRU = B.ABI_CAPOGRU
          AND

X.cod_interno
= Y.cod_interno

          AND X.TIPO_PART       = 1
          AND ( to_date( X.DATA_ISC, 'YYYYMMDD' ) - to_date
( Y.DATA_CAN, 'YYYYMMDD' ) ) = 1
          AND X.DEN_COMP <> Y.DEN_COMP
        )

  ) AS DATA_ISC_GRU ,
  CASE
    WHEN B.DATA_CAN_GRU = 99991231
    THEN 0
    ELSE B.DATA_CAN_GRU
  END AS DATA_CAN_GRU ,
  CASE
    WHEN B.ABI_COMP = 0
    THEN NULL
    ELSE B.ABI_COMP
  END AS ABI_COMP ,
  CASE
    WHEN B.CONTROCOD_COMP = 0
    THEN NULL
    ELSE B.CONTROCOD_COMP

  END AS CONTROCOD_COMP ,
  B.COD_INTERNO         ,
  B.COD_FIS             ,
  B.TIPO_PART           ,
  B.DES_TIPO_PART       ,
  B.DEN_COMP            ,
  B.COD_ATT_ECO         ,
  B.DES_ATT_ECO         ,
  B.COM_LEG_SEDE        ,
  B.COM_AMM_SEDE        ,
  B.DATA_ISC            ,

  CASE
    WHEN B.DATA_CAN = 99991231
    THEN 0
    ELSE B.DATA_CAN

  END       AS DATA_CAN        ,
  20071231         AS DATA_RIF_GRUPPO ,
  0         AS COD_ENTE        ,

  E.DEN_LOC AS DEN_STATO
   FROM TGRUBAN_INTERNET B,
  TLOC_INTERNET D ,
  TLOC_INTERNET E
WHERE B.COM_AMM_SEDE = D.COD_LOC

AND D.COD_STATO        = E.COD_STATO
AND E.COD_STATO        = E.COD_LOC
AND B.ABI_CAPOGRU       IN

(

    SELECT DISTINCT A.ABI_CAPOGRU

       FROM TGRUBAN_INTERNET A
      WHERE 20071231 BETWEEN A.DATA_ISC AND A.DATA_CAN

    AND 20071231 < A.DATA_CAN
  )
AND 20071231 BETWEEN DATA_ISC_GRU AND DATA_CAN_GRU AND 20071231 < DATA_CAN_GRU
AND TIPO_PART = 1
ORDER BY DEN_GRUPPO ASC,
  ABI_CAPOGRU ASC ,
  DATA_ISC_GRU ASC Received on Thu Aug 20 2009 - 10:40:14 CDT