Home » SQL & PL/SQL » SQL & PL/SQL » Output Dummy Record When No Record Exists (Oracle 12.2 Windows 2012 )
Output Dummy Record When No Record Exists [message #678545] |
Thu, 12 December 2019 08:22 |
deay
Messages: 62 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: 3275 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: 62 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: 3275 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: 88 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: 62 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...
|
|
|
Goto Forum:
Current Time: Sat Jun 15 17:16:23 CDT 2024
|