Home » SQL & PL/SQL » SQL & PL/SQL » display message (oracle 9i)
display message [message #433086] Sun, 29 November 2009 23:32 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts goodmoring to all,
Please help me on this scenario

If the department no.is 10 ,then the message should be like this
"Department is 10"

If the salary is less than 1000 ,then the message should be like this
"salary is lessthan 1000"

If one row satifies both the conditions the message should be like this "Department is 10
salary is lessthan 1000"

The output should be like this

Deptno Sal Message
10 800 Department is 10
salary is lessthan 1000
10 900 Department is 10
salary is lessthan 1000

10 1200 Department is 10
10 1500 Department is 10

For that i tried the following code
SELECT deptno,sal,
case when deptno=10 then 'Department is 10'
when sal<1000 then 'salary is lessthan 1000'
END "message"
FROM emp;
but in this iam getting only one message
even if the both the conditions are satisfied please help me.
Thanks in advance

Re: display message [message #433088 is a reply to message #433086] Sun, 29 November 2009 23:52 Go to previous messageGo to next message
pv_snp
Messages: 11
Registered: July 2007
Location: INDIA
Junior Member

Hi.. Try This

SELECT deptno,sal,
case when deptno=10 and sal < 1000 then deptno ||' ' || sal ||'Department is' || deptno
when sal<1000
then deptno ||' ' || sal ||'Department is' || deptno || 'salary is less than 1000'
END "message"
FROM emp;
Re: display message [message #433089 is a reply to message #433088] Sun, 29 November 2009 23:55 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SELECT deptno,
       sal,
       CASE
         WHEN deptno = 10
              AND sal < 1000
         THEN deptno
              ||' '
              ||sal
              ||'Department is'
              ||deptno
         WHEN sal < 1000
         THEN deptno
              ||' '
              ||sal
              ||'Department is'
              ||deptno
              ||'salary is less than 1000'
       END "message"
FROM   emp; 


You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: display message [message #434036 is a reply to message #433086] Sun, 06 December 2009 22:25 Go to previous message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Previous Topic: Record display problem
Next Topic: Paging in pl/sql (merged)
Goto Forum:
  


Current Time: Fri Sep 30 05:29:14 CDT 2016

Total time taken to generate the page: 0.09162 seconds