Home » SQL & PL/SQL » SQL & PL/SQL » how to display null rows using oracle query
how to display null rows using oracle query [message #384216] Mon, 02 February 2009 21:31 Go to next message
svani212
Messages: 1
Registered: February 2009
Junior Member
Hi All,

I am new to this site. I got stuck up in framing my oracle query. Pls assist.

I have a table DMTB_DRIVING_RECORD with fields

DRV_ID
VEH_ENTITY_C
DRIVING_MODE_C
HIRER_DRV_ID
START_DT

Where each hirer_drv_id has 0 or 1 or multiple reliefs. sample data is as follows.

DRV_ID VEH_ENTITY_C DRIVING_MODE_C HIRER_DRV_ID START_DT
======================================================================
414.00 CCPL HR 102 11/7/2008 4:01
413.00 CCPL RE 102 11/7/2008 4:01

As above hirer 102 is having two reliefs [414, 413] under VEH_ENTITY_C ccpl.

I need to display number of [drv_id's]as reliefs for a particular hirer_drv_id under each veh_entity_c.

My current query returns the number of existent rows, the non-existent rows cannot be listed out.
suppose there are no hirers with 2reliefs under entity VEH_ENTITY_C ccpl it is not shown in the output.

output is supposed to be in the following form:


relief ccpl ctpl
------------ ---------------------
0 relief 2 0

1 relief 3 2

2 relief 0 1

3 relief 4 0

4 relief 0 3

5 relief 2 1

> 5 relief 1 0

My query:
=========

SELECT reliefs,
SUM(DECODE(VEH_ENTITY_C, 'CCPL',1, 0 )) AS CCPL,
SUM(DECODE(VEH_ENTITY_C, 'CTPL',1, 0 )) AS CTPL
FROM
(
SELECT COUNT(drv_id) reliefs , VEH_ENTITY_C, hirer_drv_id
FROM dmtb_driving_record
WHERE end_dt IS NULL
AND DRIVING_MODE_C = 'RE'
AND TO_CHAR(START_DT, 'MM/DD/YY') < '12/12/08'
GROUP BY hirer_drv_id, VEH_ENTITY_C
UNION ALL
SELECT 0 reliefs, VEH_ENTITY_C, hirer_drv_id
FROM dmtb_driving_record
WHERE end_dt IS NULL
AND DRIVING_MODE_C = 'RE'
AND TO_CHAR(START_DT, 'MM/DD/YY') < '12/12/08'
GROUP BY hirer_drv_id, VEH_ENTITY_C
ORDER BY reliefs
)
GROUP BY reliefs ORDER BY reliefs

o/p
====


reliefs ccpl ctpl
-----------------------

0 2 0

1 3 2
--at this point even if no row exists for 2reliefs need to display the null rows as well.
3 4 0

4 0 3

5 2 1

>5 1 0

Hope you understand my requirement. Early help is appreciated.

Thanks in advance,
vani.

Re: how to display null rows using oracle query [message #384219 is a reply to message #384216] Mon, 02 February 2009 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Please post using <code-tags> as described in Posting Guidelines!

Re: how to display null rows using oracle query [message #384223 is a reply to message #384216] Mon, 02 February 2009 23:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unreadable and ununderstandable without format.
Read and apply guidelines as BlackSwan said.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Mon, 02 February 2009 23:08]

Report message to a moderator

Re: how to display null rows using oracle query [message #384286 is a reply to message #384216] Tue, 03 February 2009 02:39 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
will nvl(column_name,' ') help you
Re: how to display null rows using oracle query [message #384297 is a reply to message #384216] Tue, 03 February 2009 03:12 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It looks like an outer join with some (possibly dynamically generated) table. Possible methods are shown e.g. in "Oracle Row Generator Techniques" article.

As you did not post testcase and Oracle version, I will post my own for Oracle 10.2.0.3:
SQL> with real_data as (select 1 val, 5 c1 from dual union all
  2                     select 2 val, 4 c1 from dual union all
  3                     select 4 val, 1 c1 from dual),
  4       full_data as (select level val from dual
  5                     connect by level <= (select max(val) from real_data))
  6  select f.val, r.c1
  7  from full_data f
  8    left join real_data r on (f.val = r.val)
  9  order by f.val;

       VAL         C1
---------- ----------
         1          5
         2          4
         3
         4          1

SQL> 


But, maybe your report tool is able to do it without SQL change.

Just one remark:
TO_CHAR(START_DT, 'MM/DD/YY') < '12/12/08'

You are aware, that you are comparing strings?
So e.g. '01/01/09' < '12/12/08' is true (as '0' < '1').
Previous Topic: including DDL within a procedure, why?
Next Topic: To avoid insertions using merge in oracle 9.2.0.8.0 release. (merged)
Goto Forum:
  


Current Time: Sat Dec 10 11:13:17 CST 2016

Total time taken to generate the page: 0.10519 seconds