Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
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 now!
  • 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.

ddrillich (TechnicalUser) (OP)
29 Feb 12 11:23
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
SantaMufasa (TechnicalUser)
29 Feb 12 13:31
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."

ddrillich (TechnicalUser) (OP)
29 Feb 12 15:08
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
 
SantaMufasa (TechnicalUser)
29 Feb 12 16:05
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."

ddrillich (TechnicalUser) (OP)
29 Feb 12 16:12
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



 
SantaMufasa (TechnicalUser)
29 Feb 12 16:23
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."

ddrillich (TechnicalUser) (OP)
29 Feb 12 17:39
Much appreciated Mufasa.

Regards,
Dan
Helpful Member!  taupirho (Programmer)
1 Mar 12 10:15
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.

ddrillich (TechnicalUser) (OP)
2 Mar 12 10:57
Much appreciated Mufasa and taupirho.

Regards,
Dan
ddrillich (TechnicalUser) (OP)
15 Mar 12 11:06
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!

Back To Forum

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