OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Select Query (Oracle 10g,Win7 23 bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Select Query [message #616125]

Thu, 12 June 2014 08:18

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Hello All,
I have one query like ..

select t.empno,t.ename,t.deptno,t.sal
into l_empno,l_ename,l_deptno,l_sal
from scott.emp t
where empno=7369;

Can i use case condition in "into" like ..

SELECT t.empno,t.ename,t.deptno,t.sal
INTO l_empno,l_ename,
CASE
WHERE 1
THEN l_deptno
ELSE l_deptno1
END,
l_sal
FROM scott.emp t
WHERE empno=7369;

If no then how can i put the condition on "into"...Please suggest me ..

Thanks ,
Xandot

Report message to a moderator

Re: Select Query [message #616128 is a reply to message #616125]

Thu, 12 June 2014 08:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

since you choose to NOT follow Posting Guidelines, I choose to NOT provide you with any answers.

Report message to a moderator

Re: Select Query [message #616130 is a reply to message #616128]

Thu, 12 June 2014 08:28

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

I apologies for that ...
I am using scott.emp table so i think script is not required ...

select t.empno,t.ename,t.deptno,t.sal 
into l_empno,l_ename,l_deptno,l_sal
from scott.emp t
where empno=7369;

Can i use case condition in "into" like ..

SELECT t.empno,t.ename,t.deptno,t.sal
INTO l_empno,l_ename,
CASE
WHERE 1
THEN l_deptno
ELSE l_deptno1
END,
l_sal
FROM scott.emp t
WHERE empno=7369;

If no then how can i put the condition on "into"...Please suggest me ..

Thanks ,
Xandot

Report message to a moderator

Re: Select Query [message #616131 is a reply to message #616130]

Thu, 12 June 2014 08:29

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can't have conditional intos

Report message to a moderator

Re: Select Query [message #616134 is a reply to message #616130]

Thu, 12 June 2014 08:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

INTO is (only) required within PL/SQL procedure & NEVER valid in plain SQL
REALIZE that INTO can only succeed when the result set consists of a single row

Report message to a moderator

Re: Select Query [message #616137 is a reply to message #616134]

Thu, 12 June 2014 08:39

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

Is there any via to do this things in sql...

Report message to a moderator

Re: Select Query [message #616138 is a reply to message #616125]

Thu, 12 June 2014 08:40

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Xandot wrote on Thu, 12 June 2014 18:48

Can i use case condition in "into"

Not clear about what you exactly want. How does it matter if you have only one variable? Why do you want the conditional INTO statement? Explain your requirement in detail.

If I assume that you are doing it in PL/SQL, then simply use IF-ELSE construct, for that matter even CASE construct. But as I said, it's not clear about what exactly you are trying to do.

Report message to a moderator

Re: Select Query [message #616139 is a reply to message #616137]

Thu, 12 June 2014 08:44

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Xandot wrote on Thu, 12 June 2014 19:09

Is there any via to do this things in sql...

Yes, no, may be...Unless you mention your requirement in detail.

Report message to a moderator

Re: Select Query [message #616140 is a reply to message #616138]

Thu, 12 June 2014 08:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

>Is there any via to do this things in sql...
what exactly is "THIS"?

what problem are you trying to solve?
how will you & I know when it has been solved?

Report message to a moderator

Re: Select Query [message #616141 is a reply to message #616140]

Thu, 12 June 2014 08:59

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

I got my answer using pl/sql...

declare
l_dept scott.emp.deptno%type;
begin
SELECT t.empno,t.ename,t.deptno,t.sal
INTO l_empno,l_ename,l_dept,l_sal
FROM scott.emp t
WHERE empno=7369;

if l_dept=10 then
     l_deptno:=l_dept;
else
     l_deptno1:=l_dept;
end if;
end;

Thank you all...

Report message to a moderator

Re: Select Query [message #616151 is a reply to message #616141]

Thu, 12 June 2014 09:42

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Xandot wrote on Thu, 12 June 2014 19:29

I got my answer using pl/sql...

if l_dept=10 then
     l_deptno:=l_dept;
else
     l_deptno1:=l_dept;
end if;
end;

What is this code going to do at all? Seems like unnecessary complication.

If l_dept = 10, then use the same variable for your next steps. Why do you need l_deptno and l_deptno1?

[Updated on: Thu, 12 June 2014 09:49]

Report message to a moderator

Re: Select Query [message #616189 is a reply to message #616151]

Fri, 13 June 2014 04:22

Xandot
Messages: 235
Registered: January 2014
Location: India

Senior Member

I have two items ( l_deptno and l_deptno1 on oracle apex ) and they visible with different conditions so when the l_deptno is visible than
the value of l_dept will show on l_deptno item otherwise it'll show in l_deptno1 item ...

So my problem is when the first item (l_deptno) is visible than value of l_dept should be shown in l_deptno else l_deptno1 ..that's why i am using if-else ..

Thanks,
Xandot

Report message to a moderator

Re: Select Query [message #616192 is a reply to message #616189]

Fri, 13 June 2014 05:12

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Why do you care where you display that value? What difference does it make if you display it in L_DEPTNO or L_DEPTNO1? What do you use these two items for, anyway?

Report message to a moderator

Previous Topic:	oracle package referring old schema after migration to new schema
Next Topic:	how to remove dots in the column

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 11:32:48 CDT 2024