martes, noviembre 25, 2008

Poesia.

SELECT DISTINCT prg.ppmpkey , prg.NAME , prg.contractname , gya1.gyaname1 CCNAME, gya1.gyacustnrold , gya1.gyaname2 cc_custid, gya2.gyaname1 KANAME , gya2.gyacustnrold,
gya2.gyaname2 ka_custid--, x.gyapkey
FROM
(SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN ('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D')
-- AND ZAC0.gyapkeyc='0000000Q407'
ORDER BY 2 , 3 ) x ,
(SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D')
-- AND ZAC0.gyapkeyc='0000000Q407'
) y , prg , GYA0 gya1 , GYA0 gya2 ,
(SELECT ppmpkey , gyapkey , COUNT(gyapkey)
FROM (
SELECT NVL(PZC0.ppmpkey,'NULL') ppmpkey,
ZAC0.gyapkeyc gyapkey,PZC0.datefrom,PZC0.dateto , PZC0.GYAPKEY PZC0GYAKEY
FROM PZC0,ZAC0
WHERE ZAC0.gyapkeya=PZC0.gyapkey
AND ZAC0.regionkey LIKE trim(PZC0.regionkey)||'%'
AND PZC0.ppmpkey IN ('0000000BCGL' , '0000000BCGM' , '0000000BCGP' , '0000000BCGQ',
'0000000BCWQ' , '0000000BCWP' , '0000000BCWQ')
AND PZC0.status NOT IN ('d','D'))
GROUP BY gyapkey , ppmpkey
HAVING COUNT(gyapkey) > 1
ORDER BY 1
) dup
WHERE x.ppmpkey=y.ppmpkey AND
X.gyapkey = y.gyapkey AND
y.datefrom <= X.dateto AND
prg.ppmpkey=x.ppmpkey AND
gya1.gyapkey = x.gyapkey AND
gya2.gyapkey = x.PZC0GYAKEY
AND dup.ppmpkey=x.ppmpkey
AND dup.gyapkey=x.gyapkey
AND gya1.gyacustnr<>gya2.gyacustnr
-- AND prg.ppmpkey='0000000B7OM'
ORDER BY 2 , 3 , 6

3 comentarios:

caminante dijo...

:O

Qué es eso? Poesía al programado? Yo no entiendo es nah'!

enredO dijo...

Mas que poesía yo diría espagueti de letras. En mi ignorancia, diría que por lo que veo andas haciendo un "query" para sacar un dato especifico sin que se repita o algo así....

juanpi dijo...

Estaba entendiedo todito, pero como que en el camino me fui perdiendo, no te entendi un par de acciones, pero a fin de cuentas, quien entiende la poesia.