Today's Messages (on)  | Unanswered Messages (off)

Forum: SQL & PL/SQL
 Topic: Output Dummy Record When No Record Exists
Output Dummy Record When No Record Exists [message #678545] Thu, 12 December 2019 08:22
deay
Messages: 54
Registered: August 2005
Member
hi Everyone - I've tried every which way to get a 'default' record to output when no record exists in database for a report.
I've tried UNION ALL, CASE, NVL and can't seem to get anything to work. The Report involves running a 2 year comparison of revenue between media sources Internet and Radio by City. some years there is no Radio Revenue to compare with so no record exists and no output is produced however mgt would like to see output "Radio", 2018, 0,0 on the Report case in point below:
please note Oxnard and Milwaukee have had no Radio Revenue for 2018 and 2019 so no record is output in the first block.

MGT WANTS THIS

CITY                          |CAGE    |LAST|  LAST_YR$|LAST_YR_COUNT|CURR|  CURR_YR$|CURR_YR_COUNT
------------------------------|--------|----|----------|-------------|----|----------|-------------
Atlanta                       |Internet|2018|    314100|         5606|2019| 331239.25|         6303
Atlanta                       |Radio   |2018|       775|           10|2019|      1460|           19
Balch Springs                 |Internet|2018|    489504|         9238|2019|    460889|        10335
Balch Springs                 |Radio   |2018|      1000|           21|2019|      1900|           27
Milwaukee                     |Internet|2018| 103158.76|         2245|2019|    101864|         2568
Oxnard                        |Internet|2018|      9735|          162|2019|     15152|          173
TO LOOK LIKE THIS


CITY                          |CAGE    |LAST|  LAST_YR$|LAST_YR_COUNT|CURR|  CURR_YR$|CURR_YR_COUNT
------------------------------|--------|----|----------|-------------|----|----------|-------------
Atlanta                       |Internet|2018|    314100|         5606|2019| 331239.25|         6303
Atlanta                       |Radio   |2018|       775|           10|2019|      1460|           19
Balch Springs                 |Internet|2018|    489504|         9238|2019|    460889|        10335
Balch Springs                 |Radio   |2018|      1000|           21|2019|      1900|           27
Milwaukee                     |Internet|2018| 103158.76|         2245|2019|    101864|         2568
Milwaukee                     |Radio   |2018|         0|            0|2019|         0|            0
Oxnard                        |Internet|2018|      9735|          162|2019|     15152|          173
Oxnard                        |Radio   |2018|         0|            0|2019|         0|            0
this is the code:

set linesize 300
set pagesize 50000
set colsep "|"

WITH ADDRCITY as (select distinct city from inbound_zip)
select a.City,a.Cage1 CAGE,b.YR2 as Last_Year, b.tot_pmt2 Last_Yr$, b.cnt2 Last_Yr_Count ,a.YR1 Curr_Year,  a.tot_pmt1 Curr_Yr$, a.cnt1 Curr_Yr_Count
from
(select c.city as City, to_char(paydate,'YYYY') YR1 ,decode(substr(p.appealcode,2,1),'N','Internet','R','Radio') as Cage1, sum(p.payamount) TOT_PMT1 ,count(*) CNT1
from payment p, addrcity c
where substr(p.appealcode,2,1) in ('R','N')
and c.city in('Atlanta','Balch Springs','Oxnard','Milwaukee')
and p.idnumber in(select a.idnumber from address a where 
                  substr(a.zip,1,5) in (select i.zip from inbound_zip i where i.city=c.city))
and ((to_char(p.paydate,'mm') < (select extract(month from sysdate) from dual))
and (to_char(p.paydate,'YYYY') =  (select extract(year from sysdate) from dual)))
and not exists(select pr.ltransnum from payment pr
               where pr.trantype = 'PYR'
               and pr.ltransnum=p.ltransnum and pr.appealcode=p.appealcode)
and p.payamount > 0
group by c.city, to_char(p.paydate,'YYYY'), substr(p.appealcode,2,1)) a,
(select c.city as City, to_char(paydate,'YYYY') YR2 ,decode(substr(p.appealcode,2,1),'N','Internet','R','Radio') as Cage2, sum(p.payamount) TOT_PMT2 ,count(*) CNT2
from payment p, addrcity c
where substr(p.appealcode,2,1) in ('R','N')
and c.city in('Atlanta','Balch Springs','Oxnard','Milwaukee')
and p.idnumber in(select a.idnumber from address a where 
                  substr(a.zip,1,5) in (select i.zip from inbound_zip i where i.city=c.city))
and ((to_char(p.paydate,'mm') < (select extract(month from sysdate) from dual))
and (to_char(p.paydate,'YYYY') =  (select extract(year from sysdate)-1 from dual)))
and not exists(select pr.ltransnum from payment pr
               where pr.trantype = 'PYR'
               and pr.ltransnum=p.ltransnum and pr.appealcode=p.appealcode)
and p.payamount > 0
group by c.city, to_char(p.paydate,'YYYY'), substr(p.appealcode,2,1)) b
where a.cage1 = b.cage2 and a.city=b.city
order by a.city, a.cage1
any help/tips greatly appreciated.
Re: Output Dummy Record When No Record Exists [message #678546 is a reply to message #678545] Thu, 12 December 2019 08:42
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
Food for thought:

select  rownum,
        nvl(ename,'no data found') ename
  from      emp
        right join
            dual
        on deptno = &1
/
SQL> /
Enter value for 1: 10
old   6:         on deptno = &1
new   6:         on deptno = 10

    ROWNUM ENAME
---------- -------------
         1 CLARK
         2 KING
         3 MILLER

SQL> /
Enter value for 1: 99
old   6:         on deptno = &1
new   6:         on deptno = 99

    ROWNUM ENAME
---------- -------------
         1 no data found

SQL> 
SY.
Re: Output Dummy Record When No Record Exists [message #678547 is a reply to message #678546] Thu, 12 December 2019 08:55
deay
Messages: 54
Registered: August 2005
Member
hi Solomon and thanks,
I actually thought of creating a 'dummy' table with dummy records to pull in when no record exists but also thought there's got to be a way to check if row exists...if exists ..."do this"....if not exists ...."then this". trying to embed the code and getting the syntax right has be problematic.
will contemplate your logic, thanks.
Re: Output Dummy Record When No Record Exists [message #678548 is a reply to message #678547] Thu, 12 December 2019 09:36
Solomon Yakobson
Messages: 2901
Registered: January 2010
Location: Connecticut, USA
Senior Member
You need to partition outer join to list of city,cage:

with city_list as (
                   select  'Atlanta' city from dual union all
                   select  'Balch Springs' city from dual union all
                   select  'Oxnard' city from dual union all
                   select  'Milwaukee' city from dual
                  ),
     cage_list as (
                   select  'Internet' cage from dual union all
                   select  'Radio' cage from dual
                  ),
          data as (
                   select  'Atlanta' city,'Internet' cage,100 amt_2018,999 amt_2019 from dual union all
                   select  'Oxnard' city,'Radio' cage,55 amt_2018,123 amt_2019 from dual
                  )
select  ct.city,
        cg.cage,
        nvl(d.amt_2018,0) amt_2018,
        nvl(d.amt_2019,0) amt_2019
  from      city_list ct
        inner join
            cage_list cg
          on 1 = 1
          partition by(
                       ct.city,
                       cg.cage
                      )
        left outer join
            data d
          on     d.city = ct.city
             and
                 d.cage = cg.cage
  order by ct.city,
           cg.cage
/

CITY          CAGE       AMT_2018   AMT_2019
------------- -------- ---------- ----------
Atlanta       Internet        100        999
Atlanta       Radio             0          0
Balch Springs Internet          0          0
Balch Springs Radio             0          0
Milwaukee     Internet          0          0
Milwaukee     Radio             0          0
Oxnard        Internet          0          0
Oxnard        Radio            55        123

8 rows selected.

SQL> 
SY.
Re: Output Dummy Record When No Record Exists [message #678549 is a reply to message #678548] Thu, 12 December 2019 09:51
JPBoileau
Messages: 82
Registered: September 2017
Member
You have at least a couple of possibilities... Either:

Create tables of city, year, and revenue source, and outer join to them.

Create inline views of city, year and revenue source, and outer join to them.

create table revenue (city varchar2(80), source_name varchar2(80), year number(4), revenue number(10,2));
insert into revenue values ('Atlanta', 'Radio', 2019, 100000.00);
insert into revenue values ('Miami', 'Internet', 2019, 100000.00);
insert into revenue values ('New York City', 'Radio', 2018, 100000.00);
insert into revenue values ('Miami', 'Internet', 2017, 100000.00);

select city, source_name, year, revenue
from revenue
order by year, city;

CITY                 SOURCE_NAME                YEAR    REVENUE
-------------------- -------------------- ---------- ----------
Miami                Internet                   2017     100000
New York City        Radio                      2018     100000
Atlanta              Radio                      2019     100000
Miami                Internet                   2019     100000

select c.city, y.year, s.source_name, nvl(r.revenue, 0)
from (select distinct city from revenue) c,
   (select distinct year from revenue) y,
   (select distinct source_name from revenue) s,
   revenue r
where
   r.city(+) = c.city and
   r.year(+) = y.year and
   r.source_name(+) = s.source_name
order by
   y.year, c.city, s.source_name;
   

CITY                       YEAR SOURCE_NAME          NVL(R.REVENUE,0)
-------------------- ---------- -------------------- ----------------
Atlanta                    2017 Internet                            0
Atlanta                    2017 Radio                               0
Miami                      2017 Internet                       100000
Miami                      2017 Radio                               0
New York City              2017 Internet                            0
New York City              2017 Radio                               0
Atlanta                    2018 Internet                            0
Atlanta                    2018 Radio                               0
Miami                      2018 Internet                            0
Miami                      2018 Radio                               0
New York City              2018 Internet                            0
New York City              2018 Radio                          100000
Atlanta                    2019 Internet                            0
Atlanta                    2019 Radio                          100000
Miami                      2019 Internet                       100000
Miami                      2019 Radio                               0
New York City              2019 Internet                            0
New York City              2019 Radio                               0
JP
Re: Output Dummy Record When No Record Exists [message #678550 is a reply to message #678549] Thu, 12 December 2019 14:00
deay
Messages: 54
Registered: August 2005
Member
wow..thanks Solomon and JP....Solomon I hadn't thought of expanding WITH AS and using partition, your code clearly illustrates how that would work.

JP - I was thinking something similar about creating a dummy table and insert the records I need then join on that table.
I like Solomon's solution and will let everyone know when I get that working.

I knew the best minds here would help me figure this out... Wink



Current Time: Thu Dec 12 15:45:40 CST 2019