SQL:printing my own messages depending on query output

Question

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.

Answer 1

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

Answer 2

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?

Answer 3

This works (tested on OracleXE):

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