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

On Aug 21, 1:41 am, Etantonio <etanto... (AT) gmail (DOT) com> wrote:
Create an inline view is one method.

select b.abi_capogru || '.' || b.controcod_capogru abi_cc_capogru,

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

decode(b.data_can_gru, 99991231, 0, b.data_can_gru) data_can_gru,

nullif(b.abi_comp, 0) abi_comp,

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

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