Home » SQL & PL/SQL » SQL & PL/SQL » Need help for query (Oracle Database 10g)
Need help for query [message #437843] Wed, 06 January 2010 04:19 Go to next message
marlon_loyolite
Messages: 66
Registered: July 2006
Member
No Message Body

[Updated on: Wed, 06 January 2010 04:22] by Moderator

Report message to a moderator

Re: Need help for query [message #437846 is a reply to message #437843] Wed, 06 January 2010 04:28 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
When I tried to reformat your post (it was quite unreadable), an error occurred. The site did not process the change, and your post was lost.
My apologies for this; please repost your question.
Please use code tags to improve readability.

Not able to send this through PM, since the original poster has disabled PM
Re: Need help for query [message #437848 is a reply to message #437846] Wed, 06 January 2010 04:30 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Hi,
Having a scenario like below

CREATE TABLE t_tab_one(cid_1 NUMBER, cname VARCHAR2(20));

INSERT INTO T_TAB_ONE ( CID_1, CNAME ) VALUES ( 
1, 'XXX'); 
INSERT INTO T_TAB_ONE ( CID_1, CNAME ) VALUES ( 
99, 'YYY'); 
COMMIT;

CREATE TABLE t_tab_two(cid_2 NUMBER,c_orig_id NUMBER,c_r_id NUMBER,c_fac_id NUMBER)

INSERT INTO T_TAB_TWO ( CID_2, C_ORIG_ID, C_R_ID, C_FAC_ID ) VALUES ( 
1, 1, 999, 888); 
INSERT INTO T_TAB_TWO ( CID_2, C_ORIG_ID, C_R_ID, C_FAC_ID ) VALUES ( 
2, NULL, 999, 1); 
COMMIT;

SELECT t1.*, t2.*
FROM t_tab_one t1, t_tab_two t2
WHERE t1.cid_1 = nvl(t2.c_orig_id(+),t2.C_FAC_ID(+)) AND t2.c_r_id = 999;
/

OUTPUT:

CID_1 CNAME CID_2 C_ORIG_ID C_R_ID C_FAC_ID
---------- -------------------- ---------- ---------- ---------- ----------
1 XXX 1 1 999 888
1 XXX 2 999 1


2 rows selected.


I need the output as the following:


CID_1 CNAME CID_2 C_ORIG_ID C_R_ID C_FAC_ID
---------- -------------------- ---------- ---------- ---------- ----------
1 XXX 1 1 999 888
1 XXX 2 999 1
99 YYY NULL NULL NULL NULL

2 rows selected.

How do I get that. Please help.

Thanks,
Marlon

[Updated on: Wed, 06 January 2010 04:31]

Report message to a moderator

Re: Need help for query [message #437850 is a reply to message #437848] Wed, 06 January 2010 04:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Here it is
SQL> SELECT t1.*, t2.*
  2  FROM t_tab_one t1, t_tab_two t2
  3  WHERE t1.cid_1 = nvl(t2.c_orig_id(+),t2.C_FAC_ID(+))
  4  /

     CID_1 CNAME                     CID_2  C_ORIG_ID     C_R_ID   C_FAC_ID
---------- -------------------- ---------- ---------- ---------- ----------
         1 XXX                           1          1        999        888
         1 XXX                           2                   999          1
        99 YYY

SQL>
Re: Need help for query [message #437852 is a reply to message #437848] Wed, 06 January 2010 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

./fa/2115/0/ We have the answer before the question. Laughing

Regards
Michel
Re: Need help for query [message #437854 is a reply to message #437852] Wed, 06 January 2010 04:47 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
I got the mail as i subscribed this forum ....
So this feature(Email) is so helpful when the moderator deletes the messages Wink
Here I asked the same

Sriram Smile
Re: Need help for query [message #437855 is a reply to message #437854] Wed, 06 January 2010 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you could repost here the question as you have it...

Regards
Michel
Re: Need help for query [message #437856 is a reply to message #437855] Wed, 06 January 2010 05:02 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Michel Cadot wrote on Wed, 06 January 2010 16:28
Maybe you could repost here the question as you have it...

Regards
Michel


Yes thats what i did Here....

sriram Smile
./fa/7268/0/

[Updated on: Wed, 06 January 2010 05:18]

Report message to a moderator

Re: Need help for query [message #437875 is a reply to message #437856] Wed, 06 January 2010 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But could you post in TEXT and the whole post.
There is no question in what you posted.

Regards
Michel
Re: Need help for query [message #437877 is a reply to message #437875] Wed, 06 January 2010 05:51 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
As Frank Asked the OP to use code tags I again updated that message shall i post that nowin a text format same as the OP? Is it really required now ?

sriram Smile
Re: Need help for query [message #437886 is a reply to message #437877] Wed, 06 January 2010 06:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it really required now ?

If we want the topic can help someelse, yes.
Otherwise it is a dead topic.

Regards
Michel
Re: Need help for query [message #437893 is a reply to message #437886] Wed, 06 January 2010 07:03 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
this is what he posted--------


Hi,
Having a scenario like below

CREATE TABLE t_tab_one(cid_1 NUMBER, cname VARCHAR2(20))

INSERT INTO T_TAB_ONE ( CID_1, CNAME ) VALUES (
1, 'XXX');
INSERT INTO T_TAB_ONE ( CID_1, CNAME ) VALUES (
99, 'YYY');
COMMIT;

CREATE TABLE t_tab_two(cid_2 NUMBER,c_orig_id NUMBER,c_r_id NUMBER,c_fac_id NUMBER)

INSERT INTO T_TAB_TWO ( CID_2, C_ORIG_ID, C_R_ID, C_FAC_ID ) VALUES (
1, 1, 999, 888);
INSERT INTO T_TAB_TWO ( CID_2, C_ORIG_ID, C_R_ID, C_FAC_ID ) VALUES (
2, NULL, 999, 1);
COMMIT;

SELECT t1.*, t2.*
FROM t_tab_one t1, t_tab_two t2
WHERE t1.cid_1 = nvl(t2.c_orig_id(+),t2.C_FAC_ID(+)) AND t2.c_r_id = 999;
/

OUTPUT:

CID_1 CNAME CID_2 C_ORIG_ID C_R_ID C_FAC_ID
---------- -------------------- ---------- ---------- ---------- ----------
1 XXX 1 1 999 888
1 XXX 2 999 1


2 rows selected.


I need the output as the following:


CID_1 CNAME CID_2 C_ORIG_ID C_R_ID C_FAC_ID
---------- -------------------- ---------- ---------- ---------- ----------
1 XXX 1 1 999 888
1 XXX 2 999 1
99 YYY NULL NULL NULL NULL

2 rows selected.

How do I get that. Please help.

Thanks,
Marlon

sriram
Previous Topic: SQLPlus spool output
Next Topic: data refresh in Dev instance
Goto Forum:
  


Current Time: Sat Dec 03 01:18:02 CST 2016

Total time taken to generate the page: 0.11942 seconds