Re: Field not recognized from inside inner query

From: Peter Nilsson <airia_at_acay.com.au>
Date: Thu, 20 Aug 2009 23:02:08 -0700 (PDT)
Message-ID: <d87aea51-be56-4ff8-b3ce-483deee29a6e_at_v37g2000prg.googlegroups.com>

On Aug 21, 1:41 am, Etantonio <etanto..._at_gmail.com> wrote:
> 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 ?

Create an inline view is one method.

> This query does exactly what I need but it doesn't work,
> may you help me ?

...
> SELECT CONCAT(ABI_CAPOGRU,CONCAT('.',CONTROCOD_CAPOGRU))
> ABI_CC_CAPOGRU ,
select b.abi_capogru || '.' || b.controcod_capogru abi_cc_capogru,

<snip>
> (
> 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 ,
Since it's difficult to decifer what this query is meant to do, it's hard to offer an alternative. You haven't supplied any DDL and a proper solution may well depend on that.

> CASE
> WHEN B.DATA_CAN_GRU = 99991231
> THEN 0
> ELSE B.DATA_CAN_GRU
> END AS DATA_CAN_GRU ,
  decode(b.data_can_gru, 99991231, 0, b.data_can_gru) data_can_gru,

> CASE
> WHEN B.ABI_COMP = 0
> THEN NULL
> ELSE B.ABI_COMP
> END AS ABI_COMP ,
  nullif(b.abi_comp, 0) abi_comp,

<snip>
> 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
> )

Your condition is equivalent to...

  where a.data_isc <= 20071231
    and 20071231 <= a.data_can
    and 20071231 < a.data_can

So I'd just write...

  where a.data_isc <= 20071231
    and 20071231 < a.data_can

YMMV But it seems to me you can replace that entire B.ABI_CAPOGRU IN test with...

  and b.abi_capogru is not null
  and b.data_isc <= 20071231
  and 20071231 < b.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
At a guess, you can 'solve' your problem with something like...

  select ...
         bx.data_isc_gru,
         ...

    from tgruban_internet b
    left join

         (
          select abi_capogru, max(data_isc_gru) data_isc_gru
            from (
                  select x.abi_capogru, x.data_isc_gru
                    from tgruban_internet x
                   where x.tipo_part = 1
                  minus
                  select x.abi_capogru, x.data_isc_gru
                    from tgruban_internet x,
                         tgruban_internet y
                   where 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
                 )
           group by abi_capogru
         ) bx on bx.abi_capogru = b.abi_capogru,

--
Peter

Received on Fri Aug 21 2009 - 01:02:08 CDT