Answer / ravin

Buddies, why are you all selecting from and updating the
tables. The question says, just two variables to be swapped
without using any extra variable and also with a single
statement.
And Ramakanth's answer is right i.e. y=x+y-(x=y) . Thats it.

^^x contains 1,2 - didn't understand though.

Answer / ramakanth

y=x+y-(x=y)

Answer / roopesh kumar

declare
x number:= 1;
y number:= 2;
begin
x := x + y;
dbms_output.put_line (x); -- output 3;
y := x - y;
dbms_output.put_line (y); -- output 1;
x := x - y;
dbms_output.put_line (x); -- output 2;
end;
/

Answer / radha sri seshu.kolla

YES SMITA I HAVE ONE DOUBT IN YOUR QUESTION. X CONTAIN 1,2
HOW ONE VARIABLE CAN HOLD TWO VALUES AT A TIME. TRY TO BE
MORE SPECIFIC IN YOUR QUESTION

Answer / v

yeah completely agree with u(the above person).The answer
was supposed to be in a single statement and everybody
wrote an history 4 it....hehe
no offence meant

Answer / divya c

(y!=x)||(x!=y)?x=y,y=x:y=x,x=y;

Answer / senthil kumar

declare
x number:= 1;
y number:= 3;
begin
x := x + y;
dbms_output.put_line (x); -- output 4;
y := x - y;
dbms_output.put_line (y); -- output 1;
x := x - y;
dbms_output.put_line (x); -- output 3;
end;
/

Now Y got 1 and x got 3 samae way of x = 2, y =4

Answer / sheshadri

String x="1,2";
String y="3,4";
x=(y+"$"+(y=x)).split("[$]")[0];

Answer / nanne saheb c

Assume that the sample table is as below
X Y
------------
1 3
2 4

Update sample set
X=decode(X,1,3,2,4),
Y=decode(Y,3,1,4,2);

(OR)
Update sample set
X = (case when 1 then 3
when 2 then 4
end),
Y = (case when 3 then 1
when 4 then 2
end);

Answer / raji_4u

IF YOU WANT TO UPDATE THE TABLE,

UPDATE TABLE_NAME
SET X = Y, Y = X;

--------------------------
IF YOU WANT TO JUST DISPLAY,

SELECT Y X,X Y FROM TABLE_NAME;

ISN'T THIS SIMPLE THAN SANTHI.K DID.

When the mutating error will comes? and how it will be resolved?

2 Answers  

what are the advantages of cursors than procedures?

2 Answers  

What is a memo field?

0 Answers  

What is difference between % ROWTYPE and TYPE RECORD ?

2 Answers  

How do you explain an index number?

0 Answers  

Which version of sql do I have?

0 Answers  

what is d diff between grant,commit,rollback n savepoint

1 Answers  

What is the difference between unique and primary key constraints?

0 Answers  

What is correlated sub-query?

7 Answers   Oracle,

what is explain plan?

4 Answers  

How to avoid duplicate records in a query?

0 Answers  

Hi, I am new in oracle(SQL), could anyone help me in writing a correct SQL. Below is the table structure. Table: Subsc Fields: 1. Sub_no (this field will hold values of subscriber nos, for e.g. S111111, S222222, S333333, S444444, etc.) 2. s_status (this field will hold values for different status of subscriber, for e.g. 'A', 'S', 'C', etc.) 3. cus_id (this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) Table: Bill Fields: 1. Bill_no this field will hold values of bill nos for e.g. 11111111, 22222222, 33333333, 44444444, etc.) 2. b_status = (this field will hold values for different status of bill for e.g. 'O', 'C', 'S', etc.) Note: 1. The Sub_no is a Primary key of Subsc table. 2. The cus_id is a foreign in Subsc table (referred from Bill_no field of Bill table) 3. The Bill_no field is the Primary key of Bill table. Query A --> I wrote a query to select cus_id/Bill_no which is in status open (b_status = 'O') and having more than two active subscriber (i.e. S_status = 'A') in it ( i.e. more the two subscribers in same bill). select s.cus_id from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id having count(sub_no) = 2 Problem : The above query will give the cus_id (or rather bill_no) which are in open status (b_status ='O) and which are having TWO ACTIVE Subscribers (s_status ='A') in it. However, this query will also lists the cus_id/bill_no which are having more than TWO subscribers in it (but only two subscriber will be in Active status (s_status = 'A') and the others will be in s_status = 'C' or s_status = 'S'. Help needed: I want to write a query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it. B--> If I include the sub_no in the above query then NO row are returned. select s.cus_id, s.sub_no from subsc s where exists (select 1 from bill where bill_no = s.cus_id and b_status = 'O') and s_status = 'A' group by s.cus_id, s.sub_no having count(sub_no) = 2 Help needed: I want to modify the above query which will fetch ONLY the cus_id/bill_no which are in open status (b_status ='O') and which are having ONLY TWO ACTIVE subscribers (s_status ='A') in it ALONG with the sub_no. Thanks a lot in advance. Regards, Nitin

0 Answers