Denilson da Silva Guedes Deni, July 04, 2019 - 3:19 pm UTC
I did not mention a detail previously, I am executing the select clause through the TOAD tool, retrieving the selected data from the clipboard and copying it to an excel sheet. Even using chr (13) || chr (10) as a separator in the LISTAGG function, did not result in line break within the same cell of the excel worksheet.
Follows the query executed in TOAD.
SELECT P.CDINSCRICAOIMOB || '-' || P.NUDIGITOINSCRICAOIMOB AS CDINSCRICAOIMOB,
(SELECT TRIM(PC.NUPROCESSO) || ' - ' || RP.DSRESULTADOPROCESSO
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.RESULTADOPROCESSO RP ON RP.CDRESULTADOPROCESSO = PC.STRESULTADO
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS NUPROCESSO2014,
(SELECT CFC.VLVARIAVELCALCULOOLD
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.CADFINANCCOMPLEMENTAR CFC ON CFC.STCARACTERIZACAO = PC.STCARACTERIZACAO
AND CFC.NUPROCESSO = PC.NUPROCESSO
AND CFC.AAEXERCICIO = PC.AAEXERCICIO
AND TRIM(CFC.SGVARIAVELCALCULO) = 'IPTU'
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS VLIPTUIMPUGNACAOOLD,
(SELECT CFC.VLVARIAVELCALCULONEW
FROM DBASAT.PROPRIEDADEPROCESSO PP
JOIN DBASAT.PROCESSO PC ON PC.STCARACTERIZACAO = PP.STCARACTERIZACAO
AND PC.NUPROCESSO = PP.NUPROCESSO
AND PC.AAEXERCICIO = 2014
AND PC.CDRECEITA = 28
AND PC.FLPROCESSOVALIDO = 'S'
JOIN DBASAT.CADFINANCCOMPLEMENTAR CFC ON CFC.STCARACTERIZACAO = PC.STCARACTERIZACAO
AND CFC.NUPROCESSO = PC.NUPROCESSO
AND CFC.AAEXERCICIO = PC.AAEXERCICIO
AND TRIM(CFC.SGVARIAVELCALCULO) = 'IPTU'
WHERE PP.STCARACTERIZACAO = 1
AND PP.CDINSCRICAOIMOB = P.CDINSCRICAOIMOB) AS VLIPTUIMPUGNACAONEW,
(SELECT RC.VLRESULTADOCALCULO
FROM DBASAT.RESULTADOCALCULO RC
WHERE RC.AAEXERCICIO = 2014
AND RC.NUBASE = P.CDINSCRICAOIMOB
AND RC.NUFILIAL = 0
AND RC.CDTIPOCONTRIBUINTE = 'I'
AND RC.SGOBJETOCALCULO = 'IPTU') AS VLIPTU,
(SELECT CC.VLLANCAMENTOORIGINAL
FROM DBASAT.CONTACONTRIBUINTE CC
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS VLDIVIDAIPTUDIRETO,
(SELECT LISTAGG(LC.CDHISTORICO || ' - ' || trim(to_char(LC.VLLANCAM, '999G999G999G9999G999D99')), ', ') WITHIN GROUP (ORDER BY LC.SQLANCAMENTOCONTA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.LANCAMENTOCONTA LC ON LC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
AND LC.NUPARCELA IS NULL
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS DSLANCAMENTOSIPTUDIRETO,
(SELECT LISTAGG(trim(to_char(PARC.NUPARCELA, '00')) || ' - ' || trim(to_char(PARC.VLPARCELA, '999G999G999G9999G999D99')) || ' - ' || trim(to_char(PARC.DTVENCIMENTOPARCELA, 'dd/mm/yyyy')) || ' - ' || PARC.STPARCELA, chr(13) || chr(10)) WITHIN GROUP (ORDER BY PARC.NUPARCELA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.PARCELA PARC ON PARC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 1) AS DSCOTASIPTUDIRETO,
(SELECT CC.VLLANCAMENTOORIGINAL
FROM DBASAT.CONTACONTRIBUINTE CC
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS VLDIVIDAIPTUPROCESSO,
(SELECT LISTAGG(LC.CDHISTORICO || ' - ' || trim(to_char(LC.VLLANCAM, '999G999G999G9999G999D99')), ', ') WITHIN GROUP (ORDER BY LC.SQLANCAMENTOCONTA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.LANCAMENTOCONTA LC ON LC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
AND LC.NUPARCELA IS NULL
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS DSLANCAMENTOSIPTUPROCESSO,
(SELECT LISTAGG(trim(to_char(PARC.NUPARCELA, '00')) || ' - ' || trim(to_char(PARC.VLPARCELA, '999G999G999G9999G999D99')) || ' - ' || trim(to_char(PARC.DTVENCIMENTOPARCELA, 'dd/mm/yyyy')) || ' - ' || PARC.STPARCELA, chr(13) || chr(10)) WITHIN GROUP (ORDER BY PARC.NUPARCELA)
FROM DBASAT.CONTACONTRIBUINTE CC
JOIN DBASAT.PARCELA PARC ON PARC.SQCONTACONTRIBUINTE = CC.SQCONTACONTRIBUINTE
WHERE CC.AAEXERCICIO = 2014
AND CC.NUBASE = P.CDINSCRICAOIMOB
AND CC.NUFILIAL = 0
AND CC.CDTIPOCONTRIBUINTE = 'I'
AND CC.CDRECEITA = 1
AND CC.CDEVENTOLANCAM = 6) AS DSCOTASIPTUPROCESSO
from DBASAT.PROPRIEDADE p
where P.CDINSCRICAOIMOB in (637985,
637986,
637987,
637988,
637989,
637990,
637991,
637992,
637993,
637994,
637995,
637996,
637997,
637998,
637999,
638000,
638001,
638002,
638003,
638004,
638006,
638007,
638008,
638009,
638010,
638011,
638012,
638013,
638014,
638015,
638016,
638017,
638018,
638019,
638020,
638021,
638022,
638023,
638024,
638025,
638026,
638027,
638028,
638029,
638030,
638032,
638033,
638034,
638035,
638036,
638037,
638038,
638039,
638040,
638041,
638042,
638043,
638044,
638045,
638046,
638047,
638048,
638049,
638050,
638051,
638052,
638053,
638054,
638055,
638056,
638057,
645827,
679080,
679082,
679083,
679084,
679085,
679086,
679089,
679091,
679099,
679103,
679107,
679114,
679126,
679165,
679166,
679167,
679168,
679169,
679170,
679172,
679173,
679175,
679176,
679177,
679179,
679180,
679181,
679197,
679199,
679200,
679201,
679202,
679203,
679205,
679207,
679209,
679210,
679211,
679213,
679216,
679218,
679221,
679223)
ORDER BY P.CDINSCRICAOIMOB