SQL handling of no results 1

[ddrillich]

Good Day,

We have the following SQL command -

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]

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?

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

 

[ddrillich]

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]

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?

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

 

[ddrillich]

Hi Mufasa,

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

... 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]

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.

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

 

[ddrillich]

Much appreciated Mufasa.

Regards,
Dan

 

[taupirho]

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]

Much appreciated Mufasa and taupirho.

Regards,
Dan

 

[ddrillich]

Thank you taupirho - beautiful thing.

Regards,
Dan