Duplicar una solo fila en varias
A pesar de lo que digan mis compañeros â€PRO- JAVA’ del blog , a mi esto me ha parecido de bastante utilidad, asà que os lo escribo:
Imaginad que tenéis una tabla con dos registros.
SQL> desc prueba1
 Name                                     Null?   Type
 —————————————– ——– —————————-
 ANNIO                                             VARCHAR2(4)
 MES_INI                                           VARCHAR2(2)
 MES_FIN                                          VARCHAR2(2)
SQL> select * from prueba1;
2008 01 12
2009 01 08
2010 01 10
En cada año tenemos un mes inicial y mes final. Con una sóla select quiero devolver tantas filas como meses hay comprendidos entre ese intervalo.
Esto lo realizamos con una especie de bucle con CONNECT BY PRIOR por el mismo campo. Lo más importante de esta select es poder utilizar la pseudocolumna que nos da el connect by, LEVEL, con la que podremos limitar el bucle.
SELECT ANNIO,
TO_CHAR(ADD_MONTHS(TO_DATE(MES_INI,’MM’),LEVEL-1),’MM’) MONTH,
LEVEL
FROM PRUEBA1
CONNECT BY PRIOR ANNIO = ANNIO
AND PRIOR MES_INI=MES_INI
AND LEVEL <= TO_NUMBER(MES_FIN)
AND PRIOR DBMS_RANDOM.STRING (‘p’, 10) IS NOT NULL
Con esto obtendrÃamos lo siguiente:
ANNI MOÂ Â Â Â Â LEVEL
—- — ———-
2008 01Â Â Â Â Â Â Â Â Â 1
2008 02Â Â Â Â Â Â Â Â Â 2
2008 03Â Â Â Â Â Â Â Â Â 3
2008 04Â Â Â Â Â Â Â Â Â 4
2008 05Â Â Â Â Â Â Â Â Â 5
2008 06Â Â Â Â Â Â Â Â Â 6
2008 07Â Â Â Â Â Â Â Â Â 7
2008 08Â Â Â Â Â Â Â Â Â 8
2008 09Â Â Â Â Â Â Â Â Â 9
2008 10Â Â Â Â Â Â Â Â 10
2008 11Â Â Â Â Â Â Â Â 11
2008 12Â Â Â Â Â Â Â Â 12
2009 01Â Â Â Â Â Â Â Â Â 1
2009 02Â Â Â Â Â Â Â Â Â 2
2009 03Â Â Â Â Â Â Â Â Â 3
2009 04Â Â Â Â Â Â Â Â Â 4
2009 05Â Â Â Â Â Â Â Â Â 5
2009 06Â Â Â Â Â Â Â Â Â 6
2009 07Â Â Â Â Â Â Â Â Â 7
2009 08Â Â Â Â Â Â Â Â Â 8
2010 01Â Â Â Â Â Â Â Â Â 1
2010 02Â Â Â Â Â Â Â Â Â 2
2010 03Â Â Â Â Â Â Â Â Â 3
2010 04Â Â Â Â Â Â Â Â Â 4
2010 05Â Â Â Â Â Â Â Â Â 5
2010 06Â Â Â Â Â Â Â Â Â 6
2010 07Â Â Â Â Â Â Â Â Â 7
2010 08Â Â Â Â Â Â Â Â Â 8
2010 09Â Â Â Â Â Â Â Â Â 9
2010 10Â Â Â Â Â Â Â Â 10
Â
Que no se nos olvide meter la última condición o nos dará un error tipo:
ORA-01436: CONNECT BY loop in user data
Aquà he encontrado una explicación sobre el paquete dbms_random, por si os interesa:
http://www.adp-gmbh.ch/ora/plsql/dbms_random.html
Otra de las utilidades que he visto a esto, es poder devolver carácter a carácter cada fila:
SQL>Â select * from prueba2;
AAA BBB CCC
BBB CCC DDD
DDD EEE FFF
Asà podrÃamos, por ejemplo, contar el número de veces que un determinado carácter aparece en cada fila
SELECT SUBSTR(FILA , LEVEL , 1) as FIL FROM prueba2
CONNECT BY PRIOR FILA = FILA
AND LEVEL <= LENGTH(FILA)
AND PRIOR dbms_random.string (‘a’, 10) IS NOT NULL;
Â
FIL
- -
AÂ
AÂ
AÂ
 Â
BÂ
BÂ
BÂ
 Â
CÂ
CÂ
CÂ
BÂ
BÂ
BÂ
 Â
CÂ
CÂ
CÂ
DÂ
DÂ
DÂ
DÂ
DÂ
DÂ
 Â
EÂ
EÂ
EÂ
 Â
FÂ
FÂ
FÂ
33 rows selected.
Y la última utilidad que he encontrado, devolver un registro formado por varias palabras una a una:
Creamos una tabla:
create table prueba2 as select ‘ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS’ frase from dual;
Y ahora devolveremos ese registro palabra por palabra:
SQL> select * from prueba2;
FRASE
————————————–
ESTOY,DEVOLVIENDO,LA,FILA,POR,PALABRAS
SELECT SUBSTR (FRASE,
INSTR (FRASE, ‘,’, 1, ROWNUM) + 1,
INSTR (FRASE, ‘,’, 1, ROWNUM+1) – INSTR(FRASE, ‘,’, 1, ROWNUM) -1 ) PALABRAS
FROM ( SELECT ‘,’||FRASE||’,’ FRASE FROM PRUEBA2)
CONNECT BY ROWNUM < LENGTH(FRASE)-LENGTH(REPLACE(FRASE,’,',”))
AND PRIOR DBMS_RANDOM.RANDOM IS NOT NULL
PALABRAS
—————————————-
ESTOY
DEVOLVIENDO
LA
FILA
POR
PALABRAS