CODEWITHSUNDEEP
sqloracle-database
user177785
user177785

Reputation: 2269

SQL:printing my own messages depending on query output

how can i print my own messages depending on the output of a SQL quesry. Eg:

print "got it" when select * from emp wherempno=10;  return atlest one record.
else " not presnet" when the above quesry returns 0 records

i just one one sql quesry and not a Pl/SQL code.I am using oracle 9 db.

Upvotes: 1

Views: 821

Answers (3)

Graham
Graham

Reputation: 15214

This works (tested on OracleXE):

SELECT CASE WHEN COUNT(1) = 0 THEN 'not present' ELSE 'got it' END
FROM emp
WHERE mpno = 10

Upvotes: 1

Adriaan Stander
Adriaan Stander

Reputation: 166406

Can you not use a ROWCOUNT to determine the number of rows affected, and then combine that with an IF statement to achieve what you are looking for?

Upvotes: 0

tschaible
tschaible

Reputation: 7695

You could try grabbing the total in a sub-query and then selectively returning the result in a case statement.

I don't have access to oracle at the moment, so the syntax may not be perfect, but something like below should work

select
  case t.c
    when 0 then 'not presnet'
    else 'got it'
  end as result
  from
    (select count(*) as c from emp wherempno=10) t

Upvotes: 1

Related Questions