Home » SQL & PL/SQL » SQL & PL/SQL » Print characters if no rows are selected
Print characters if no rows are selected [message #222578] Mon, 05 March 2007 09:15 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
I have a table with 5 columns and I base my report on this column.

How do I print *NTR* in one of the columns if no rows are selected.

*NTR* is Nothing to Report.

Regards,

Re: Print characters if no rows are selected [message #222633 is a reply to message #222578] Mon, 05 March 2007 12:54 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Barbara Boehmer provided a solution for this here.
Re: Print characters if no rows are selected [message #222640 is a reply to message #222633] Mon, 05 March 2007 14:23 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks..

Regards,
Re: Print characters if no rows are selected [message #223422 is a reply to message #222578] Thu, 08 March 2007 17:27 Go to previous message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
Here's another way to do it that allows you to customize the "n rows selected." message as well as "no rows selected". It also saves you from hitting the same table twice.

variable v_c1 varchar2(6)

execute :v_c1 := 'Z'

set heading  off
set feedback off

column c_header_1 noprint new_value v_header_1
column c_header_2 noprint new_value v_header_2
column c_footer   noprint new_value v_footer

repheader left v_header_1 skip 1 v_header_2
repfooter left v_footer

select
  nvl2
    ( tb.row_count ,
      '       KEY C1     C2         C3                 C4' ,
      null
    ) as c_header_1 ,
  nvl2
    ( tb.row_count ,
      '---------- ------ ---------- ---------- ----------' ,
      null
    ) as c_header_2 ,
  case sign( nvl( tb.row_count, 0 ) - 1 )
    when 1 then chr(10) || tb.row_count || ' Items Found'
    when 0 then chr(10) || tb.row_count || ' Item Found'
    else '** No Items Found **'
    end
    as c_footer ,
  tb.key, tb.c1, tb.c2, tb.c3, tb.c4
from
  ( select 1 from dual ) ta
    left outer join
    ( select to_char( count(*) over () ) as row_count, t.*
      from   t
      where  c1 like :v_c1
    ) tb on (1=1)
;



** No Items Found **

execute :v_c1 := 'X'
/

       KEY C1     C2         C3                 C4
---------- ------ ---------- ---------- ----------
         1 X      AAA        2005-04-03       3000

1 Item Found

execute :v_c1 := '%'
/

       KEY C1     C2         C3                 C4
---------- ------ ---------- ---------- ----------
         1 X      AAA        2005-04-03       3000
         2 Y      BB         2006-05-04       2000
         3 Y      CCCC       2007-06-05       4000
         4 Y                 2008-07-06          0
         5 Y      DDDDD                       5000
         6 Y

6 Items Found


You can read more about this technique at SQL Snippets: Custom Feedback Messages - Changing the Header and Footer.

--
SnippetyJoe
http://www.sqlsnippets.com/
Previous Topic: sql for date(formate)
Next Topic: Sql query
Goto Forum:
  


Current Time: Sat Dec 03 16:23:05 CST 2016

Total time taken to generate the page: 0.08266 seconds