INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

SQL handling of no results

SQL handling of no results

(OP)
Good Day,

We have the following SQL command -

CODE

SELECT DISTINCT
     COMPANY_USERID_DISPLAYNAME."DISPLAYNAME" AS COMPANY_USERID_DISPLAYNAME_DISPL,
     COMPANY_USERID_DISPLAYNAME."USERID" AS COMPANY_USERID_DISPLAYNAME_USERI,
     EVENTSSUMMARY."USERID" AS EVENTSSUMMARY_USERID,
     EVENTSSUMMARY."FILEPATH" AS EVENTSSUMMARY_FILEPATH
FROM
     "TSREPORTING"."EVENTSSUMMARY" EVENTSSUMMARY INNER JOIN "TSREPORTING"."COMPANY_USERID_DISPLAYNAME"
      COMPANY_USERID_DISPLAYNAME ON EVENTSSUMMARY."USERID" = COMPANY_USERID_DISPLAYNAME."USERID"
WHERE FILEPATH IS NOT NULL
      AND UPPER(EVENTSSUMMARY."FILEPATH") LIKE '%.PAGE'
ORDER BY COMPANY_USERID_DISPLAYNAME_DISPL DESC,EVENTSSUMMARY_FILEPATH

We would like to improve it, so in case no results are available, we would like the command to return "There was no data found for this particular search."

Any suggestions, by any chance?

Regards,
Dan

RE: SQL handling of no results

Dan,

What interface to the database are you using...SQL*Plus, Crystal Reports, in-house application, et cetera? Is the message (from SQL*Plus, for example) "no rows selected" not satisfactory for your needs?

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

RE: SQL handling of no results

(OP)
Hi Mufasa,

We are calling the SQL from an iReport environment and the developer feels more comfortable in getting this message directly from the SQL call...

Regards,
Dan
 

RE: SQL handling of no results

Dan,

I presume then that you would like some type of return code for each row, from your queries, such that for each row there is the equivalent of an "Okay" returned, but if no row returns from your query, then it returns the message, "There was no data found for this particular search."

Is my presumption correct?

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

RE: SQL handling of no results

(OP)
Hi Mufasa,

I think the second part is what we are looking for -

Quote:

... but if no row returns from your query, then it returns the message, "There was no data found for this particular search."

We also realized that we can call a stored procedure from JRXML or the iReport tool...

Regards,
Dan



 

RE: SQL handling of no results

If you want a single-row message to appear as a result of a 0-row results set, then calling a stored procedure that returns either your multi-row results or the 0-row message is certainly the way to go.

If, instead, you are using straight SQL, then a return code per row (similar to my earlier post's suggestion) is the only way I can think of for producing the results you wanted.

santaMufasa
(aka Dave of Sandy, Utah, USA)
"People may forget what you say, but they will never forget how you made them feel."

RE: SQL handling of no results

(OP)
Much appreciated Mufasa.

Regards,
Dan

RE: SQL handling of no results

Not exactly what you were looking for but you might be able to change it to suit.

  1  select empno || ' ' || ename data
  2  from emp
  3* where ename = 'FORD'
SQL> /

DATA
---------------------------------------------------
7902 FORD



  1  select empno || ' ' || ename data
  2  from emp
  3* where ename = 'FORD PREFECT'
SQL> /

no rows selected


  1  select case
  2  when z.ename = chr(255) then 'There are no rows to select'
  3  else z.data end data
  4  from (
  5  select ename, ename || ' ' || empno data, rownum rn
  6  from emp
  7  where ename = 'FORD'
  8  union all
  9  select chr(255),chr(255) || ' ' || -999999 ,-999999
 10  from dual
 11  ) z
 12* where decode(rownum, 1, z.rn, rownum) = z.rn
SQL> /

DATA
---------------------------------------------------
FORD 7902


  1  select case
  2  when z.ename = chr(255) then 'There are no rows to select'
  3  else z.data end data
  4  from (
  5  select ename, ename || ' ' || empno data, rownum rn
  6  from emp
  7  where ename = 'FORD PREFECT'
  8  union all
  9  select chr(255),chr(255) || ' ' || -999999 ,-999999
 10  from dual
 11  ) z
 12* where decode(rownum, 1, z.rn, rownum) = z.rn
SQL> /

DATA
---------------------------------------------------
There are no rows to select


 


In order to understand recursion, you must first understand recursion.

RE: SQL handling of no results

(OP)
Much appreciated Mufasa and taupirho.

Regards,
Dan

RE: SQL handling of no results

(OP)
Thank you taupirho - beautiful thing.

Regards,
Dan

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close