Home » SQL & PL/SQL » SQL & PL/SQL » join the result of two different queries
join the result of two different queries [message #285177] Mon, 03 December 2007 17:31 Go to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
hi, ive been stuck for a while with this hope anyone can help

I have 4 different tables

CAT_A
cve_atr
cve_tbl
nom_atr

REL
cve_atr
cve_atr2
nom

DATOENT_ATR
cve_datoEnt
cve_atr

DATO_ENT
cve_datoEntcve_tipo

REL -> CAT_A <- DATOENT_ATR <- DATO_ENT


On my REL table i have this:
both (column a and b) are pks, from CAT_A
60 | 9 | curpPrimerAp
61 | 10 | curpSegunAp


On CAT_A
(the second column is one of the values im going to receive)
9 | 1 | curpPrimerAp
10 | 1 | curpSegunAp
11...
...
59...
60 | 5 | AP_P
61 | 5 | AP_M

DATO_ENT has the other value im receiving

Im going to receive two values: CAT_A.cve_tbl and DATO_ENT.cve_tipo
based on that im supposed to get something like this:

CVE_ATRIBUTO | CVE_ATRIBUTO | NOMBRE | NOMBRE_ATRIBUTO
60 | 9 | AP_P | curpPrimerAp
61 | 10 | AP_M | curpSegunAp


any ideas????


the queries i have are these:

select c.NOMBRE_ATRIBUTO, c.CVE_ATRIBUTO, r.NOMBRE, r.CVE_ATR_IND_REC, r.CVE_ATRIBUTO_TABLA_REQUERIDA
FROM CAT_ATRIBUTO_TBL c, RELACIONES r WHERE CVE_TABLA = 1 AND CVE_ATRIBUTO IN (
SELECT CVE_ATRIBUTO FROM DATOENT_ATRIBUTO WHERE CVE_DATOENT IN (
SELECT CVE_DATOENT FROM DATO_ENTRADA WHERE CVE_TIPO_DATOENT = 2
)
) AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA

which gives me:

NOMBRE_ATRIBUTO | CVE_ATRIBUTO | NOMBRE | CVE_ATR_IND_REC | CVE_ATRIBUTO_TABLA_REQUERIDA

curpSexo |5 |curpSexo |74 |5
curpSexo |5 |curpSexo |63 |5
curpPrimerApellid |9 |curpPrimerApellid |60 |9
curpSegunApellid |10 |curpSegunApellid |61 |10
curpNombre |11 |curpNombre |62 |11
curpFechaNacim |12 |curpFechaNacim |64 |12

and the other one:

select NOMBRE_ATRIBUTO as bla FROM CAT_ATRIBUTO_TBL WHERE CVE_ATRIBUTO IN(
select CVE_ATR_IND_REC from RELACIONES r where r.CVE_ATRIBUTO_TABLA_REQUERIDA IN (
SELECT CVE_ATRIBUTO FROM DATOENT_ATRIBUTO WHERE CVE_DATOENT IN (
SELECT CVE_DATOENT FROM DATO_ENTRADA WHERE CVE_TIPO_DATOENT = 2
)
)
);

NOMBRE

APELLIDO_P
APELLIDO_M
NOMBRE
SEXO
F_NAC
CURP


i need to replace the result of the second query into the first column of my first one


Thanks


  • Attachment: test.sql
    (Size: 5.12KB, Downloaded 203 times)

[Updated on: Mon, 03 December 2007 18:14]

Report message to a moderator

Re: join the result of two different queries [message #285178 is a reply to message #285177] Mon, 03 December 2007 17:52 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
UGLY & borderline unreadable post!
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in URL above including the section on Formating your post.

>join the result of two different queries
Two queries? I don't even see 1 query.

Quote:


Show us what you did (if you tried it yourself), including errors and/or why the result is not what you want.
Provide DDL (CREATE TABLE, etc.) as appropriate instead of listing the table structure. It'll help people setting up a test set (yes, some people really do that)
Provide INSERT statements for sample data instead of pasting in or mocking up the results of a SELECT statement.
Provide your expected result set and explain the rules/reasons that lead to it.



[Updated on: Mon, 03 December 2007 17:52] by Moderator

Report message to a moderator

Re: join the result of two different queries [message #285212 is a reply to message #285177] Mon, 03 December 2007 23:31 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Even looks tough to read for Non-english Developer Laughing

Is this what you want to achieve ?

select c.NOMBRE_ATRIBUTO, c.CVE_ATRIBUTO, 
       r.NOMBRE, r.CVE_ATR_IND_REC, 
       r.CVE_ATRIBUTO_TABLA_REQUERIDA,
       (select NOMBRE_ATRIBUTO 
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC)  NOMBRE 
FROM  CAT_ATRIBUTO_TBL c, 
      RELACIONES r 
WHERE CVE_TABLA = 1 
AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO 
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA 
                                           WHERE CVE_TIPO_DATOENT = 2)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA



This is called Correlated subquery technique.

If this is not you are looking for try to Post as suggested by Ana.

Thumbs Up
Rajuvan.
icon14.gif  Re: join the result of two different queries [message #285428 is a reply to message #285212] Tue, 04 December 2007 09:14 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
WOW , thats awesome, thank you very much, you saved my life
it worked great!!!!

i know it was kinddda hard to read-understand, but thats exactly what i was looking for, Surprised
Shocked

[Updated on: Tue, 04 December 2007 09:18]

Report message to a moderator

Re: join the result of two different queries [message #285478 is a reply to message #285212] Tue, 04 December 2007 17:35 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
I need to create a procedure with an out param that returns the result of a query, how can i achieve this?
  • Attachment: TESTproc.sql
    (Size: 6.00KB, Downloaded 145 times)

[Updated on: Tue, 04 December 2007 18:47]

Report message to a moderator

Re: join the result of two different queries [message #285479 is a reply to message #285177] Tue, 04 December 2007 18:21 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
maikit,
Since you don't want to follow the posting guidelines, You're On Your Own (YOYO) & you can wait for Rajuvan to spoon feed you your next solution.
out parameter in a stored procedure [message #285480 is a reply to message #285177] Tue, 04 December 2007 18:31 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
i changed the previoous post, is it ok now?

I need to create a procedure with an out param that returns the result of a query

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT ???
) AS

  nom_atr_tbl RELACIONES.NOMBRE%TYPE;
  NOM CAT_ATRIBUTO_TBL.NOMBRE_ATRIBUTO%TYPE;
  
  
BEGIN
  select r.NOMBRE,
       (select NOMBRE_ATRIBUTO
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC) NOMBREBIO
  INTO nom_atr_tbl, NOM
  FROM  CAT_ATRIBUTO_TBL c, RELACIONES r 
  WHERE CVE_TABLA = p_CVE_TABLA
  AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA 
                                           WHERE CVE_TIPO_DATOENT = p_CVE_TIPO_DATOENT)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;
END RELACIONA_DATOS;

what i expect from the the proc is:

NOMBRE             |   NOMBREBIO
tipoDocProbClave   |   CVE_DOCTO_PROB
curpDocumProbNum   |   AN_NUM_ACTA
tipoDocProbClave   |   AN_FECHA_REG
curpDocumProbNum   |   AN_TOMO
tipoDocProbClave   |   AN_LIBRO
curpDocumProbNum   |   AN_FOJA
tipoDocProbClave   |   AN_ENTIDAD_REG
curpDocumProbNum   |   AN_ID_MUNICIPIO
curpDocumProbNum   |   AN_CRIP

[Updated on: Tue, 04 December 2007 18:55]

Report message to a moderator

Re: join the result of two different queries [message #285498 is a reply to message #285177] Tue, 04 December 2007 22:34 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


You can ,

1. Make two Out parameter Or
2. Make One parmeter of separate Type

Or if you want just to display in SQLPlus

Use the combination of sys_refcursor Out argument + Print command

Since the members started calling me spoon feeder ,
its your turn now.

Just google or search on the board . good luck

Thumbs Up
Rajuvan
Re: out parameter in a stored procedure [message #285542 is a reply to message #285480] Wed, 05 December 2007 00:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either use e REF CURSOR or a table/array parameter (custom TYPE).
The procedure does not display anything, you have to handle your out parameter in the calling application to get the result you want.
What is your application (maybe SQL*Plus).

Regards
Michel
Re: join the result of two different queries [message #285556 is a reply to message #285177] Wed, 05 December 2007 00:52 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Both the points are there in my reply .

Its just a matter of google to OP Smile

Thumbs Up
Rajuvan
Re: out parameter in a stored procedure [message #285806 is a reply to message #285542] Wed, 05 December 2007 12:59 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
i need the procedure to be called from another procedure, this proc will use the values retrieved, am i storing the values correctly with sys_refcursor??

this is what i did...

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT SYS_REFCURSOR
) IS


BEGIN
  open p_res FOR
  select r.NOMBRE,
       (select NOMBRE_ATRIBUTO
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC) NOMBREBIO
--  INTO p_res
  FROM  CAT_ATRIBUTO_TBL c, RELACIONES r 
  WHERE CVE_TABLA = p_CVE_TABLA
  AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO 
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA 
                                           WHERE CVE_TIPO_DATOENT = p_CVE_TIPO_DATOENT)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;
END RELACIONA_DATOS;


i also tried creating a package but how cani get the values into the out var?

create or replace PACKAGE REFCURSOR AS

TYPE crs_type IS REF CURSOR;

PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT crs_type
);

END REFCURSOR;



i also tried this but i dont know if this is what u meant

create or replace
package body REFCURSOR as

PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT crs_type
) IS


BEGIN

/*
  Descripción:
  Argumentos:
  Autor:
  Fecha última modificación: 
*/
  select r.NOMBRE,
       (select NOMBRE_ATRIBUTO
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC) NOMBREBIO
  INTO p_res
  FROM  CAT_ATRIBUTO_TBL c, RELACIONES r 
  WHERE CVE_TABLA = p_CVE_TABLA
  AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO 
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA 
                                           WHERE CVE_TIPO_DATOENT = p_CVE_TIPO_DATOENT)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;
END;

end REFCURSOR;

[Updated on: Wed, 05 December 2007 13:17]

Report message to a moderator

Re: out parameter in a stored procedure [message #285807 is a reply to message #285806] Wed, 05 December 2007 13:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Then fetch the cursor you get.

Regards
Michel
Re: out parameter in a stored procedure [message #285813 is a reply to message #285807] Wed, 05 December 2007 14:01 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
I get an error
it says invlaid cursor


DECLARE
  P_CVE_TABLA NUMBER;
  P_CVE_TIPO_DATOENT NUMBER;
  P_RES SYS_REFCURSOR;
  nom_atr_tbl RELACIONES.NOMBRE%TYPE;
  NOM CAT_ATRIBUTO_TBL.NOMBRE_ATRIBUTO%TYPE;
BEGIN
  P_CVE_TABLA := 1;
  P_CVE_TIPO_DATOENT := 2;

  RELACIONA_DATOS(
    P_CVE_TABLA => P_CVE_TABLA,
    P_CVE_TIPO_DATOENT => P_CVE_TIPO_DATOENT,
    P_RES => P_RES
  );

LOOP
  FETCH p_res INTO nom_atr_tbl, NOM;  
  EXIT WHEN p_res%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(nom_atr_tbl || '     ' || NOM);
END LOOP;
END;


also tried this but got the same error

row is a %ROWTYPE

while (p_res%found) loop
      dbms_output.put_line(nom_atr_tbl  || NOM);
     fetch p_res into row;
end loop;
close p_res;

[Updated on: Wed, 05 December 2007 14:06]

Report message to a moderator

Re: out parameter in a stored procedure [message #285817 is a reply to message #285813] Wed, 05 December 2007 14:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"open p_ref for select ..." in your procedure.
I don't know which one you used in the package or standalone, you didn't say.

Regards
Michel
Re: out parameter in a stored procedure [message #285818 is a reply to message #285817] Wed, 05 December 2007 14:42 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
it works now,

create or replace PROCEDURE RELACIONA_DATOS(
    p_CVE_TABLA IN NUMBER,
    p_CVE_TIPO_DATOENT IN NUMBER,
    p_res OUT SYS_REFCURSOR
) IS

  nom_atr_tbl RELACIONES.NOMBRE%TYPE;
  NOM CAT_ATRIBUTO_TBL.NOMBRE_ATRIBUTO%TYPE;
  
  row REL_EST%ROWTYPE;

BEGIN

  open p_res FOR
  select r.NOMBRE,
       (select NOMBRE_ATRIBUTO
        FROM CAT_ATRIBUTO_TBL  CATTR
        WHERE CATTR.CVE_ATRIBUTO = r.CVE_ATR_IND_REC) NOMBREBIO
--  INTO p_res
  FROM  CAT_ATRIBUTO_TBL c, RELACIONES r 
  WHERE CVE_TABLA = p_CVE_TABLA
  AND CVE_ATRIBUTO IN (SELECT CVE_ATRIBUTO 
                     FROM DATOENT_ATRIBUTO 
                     WHERE CVE_DATOENT IN (SELECT CVE_DATOENT 
                                           FROM DATO_ENTRADA 
                                           WHERE CVE_TIPO_DATOENT = p_CVE_TIPO_DATOENT)
                     ) 
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;

LOOP
  FETCH p_res INTO nom_atr_tbl, NOM;
  DBMS_OUTPUT.PUT_LINE(nom_atr_tbl || '     ' || NOM);
  EXIT WHEN p_res%NOTFOUND;

END LOOP;
/*
while (p_res%found) loop
      dbms_output.put_line(nom_atr_tbl  || NOM);
fetch p_res into row;
end loop;
*/

close p_res;
END RELACIONA_DATOS;



DECLARE
  P_CVE_TABLA NUMBER;
  P_CVE_TIPO_DATOENT NUMBER;
  P_RES SYS_REFCURSOR;
BEGIN
  P_CVE_TABLA := 1;
  P_CVE_TIPO_DATOENT := 6;

  RELACIONA_DATOS(
    P_CVE_TABLA => P_CVE_TABLA,
    P_CVE_TIPO_DATOENT => P_CVE_TIPO_DATOENT,
    P_RES => P_RES
  );
END;


Thnx for your help!
Re: join the result of two different queries [message #285851 is a reply to message #285177] Wed, 05 December 2007 22:01 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Hey maikit ,

This result is deviated from your actual query .

Quote:

need to create a procedure with an out param that returns the result of a query


Here refcusor is opened and closed in the called procedue itself. it is not returning any thing.
If that was the case you could have used even a normal cursor Smile

What you have to to is to Open the cusor in the called procedure and fetch ,loop and all othe stuffs in the calling procedure.

Thumbs Up
Rajuvan.

Re: join the result of two different queries [message #286091 is a reply to message #285851] Thu, 06 December 2007 07:50 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
mh... im not getting it, isnt that what i did?


.
.
.
AND c.CVE_ATRIBUTO = r.CVE_ATRIBUTO_TABLA_REQUERIDA;

LOOP
  FETCH p_res INTO nom_atr_tbl, NOM;
  DBMS_OUTPUT.PUT_LINE(nom_atr_tbl || '     ' || NOM);
  EXIT WHEN p_res%NOTFOUND;

END LOOP;

[Updated on: Thu, 06 December 2007 08:05]

Report message to a moderator

Re: join the result of two different queries [message #286185 is a reply to message #286091] Thu, 06 December 2007 15:20 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
The output from the cursor seems to be correct (except the fact, it writes the last empty row, as the NOTFOUND check is done AFTER output - should be done right after FETCH).

But, rephrasing rajavu1's answer:
Your procedure returns cursor, which is UNUSABLE outside it, as it was fetched and closed in the procedure. After any try of its usage outside, you will end up with an error. For this case, it would be sufficient (and safer) to declare the cursor INSIDE the procedure, where it is used.
In the common application, the main goal of the PROCEDURE shall be to return an OPENED cursor. Then the CALLER (an anonymous block in your example) can FETCH its rows, process them as required, and in the end CLOSE the cursor.
When called from other environment than SQL*Plus (eg. JDBC), you will probably need other output method than DBMS_OUTPUT (as it will show NOTHING there).
Re: join the result of two different queries [message #286198 is a reply to message #286185] Thu, 06 December 2007 19:11 Go to previous messageGo to next message
maikit
Messages: 9
Registered: December 2007
Junior Member
So, i just have to remove the close instruction, and then handle the output within my application, say, java, right?

LOOP
  FETCH p_res INTO nom_atr_tbl, NOM;
  EXIT WHEN p_res%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(nom_atr_tbl || '     ' || NOM);

END LOOP;


--close p_res;

[Updated on: Thu, 06 December 2007 19:19]

Report message to a moderator

Re: join the result of two different queries [message #286211 is a reply to message #286198] Thu, 06 December 2007 21:52 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:

So, i just have to remove the close instruction, and then handle the output within my application, say, java, right?

No. Then you return cursor without any rows to process.
Remove the output processing (LOOP) from the procedure too.
You may use it in an anonymous block to test procedure functionality.
In JAVA, process the cursor as required (or let the JAVA developer do it).
Re: join the result of two different queries [message #286215 is a reply to message #285177] Thu, 06 December 2007 22:49 Go to previous message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Ie,

RELACIONA_DATOS will have only the Refcursor open statement while the Loop and maninultaion will be done in the the called (here Anonymous block)

Thumbs Up
Rajuvan
Previous Topic: Needs syntax checks for PL/SQL (Stored Procedure) with IN OUT Parameters
Next Topic: Oracle Function to replace Oracle sequence
Goto Forum:
  


Current Time: Fri Dec 02 12:06:26 CST 2016

Total time taken to generate the page: 0.26873 seconds