Need Oracle SQl to generate report to look like specific excel format.

I have 3  tables:  Table A, Table B and Table C
Each table have policy number and from number.
What I want to do is combine these three table
and generate a report like this:
Example  Policy P-4404 has "Y" under column F350.0 to mean that yes this policy has this form number
         blank means this policy does not have this form number.
         I am dealing with only 4 form numbers.

I need a script in oracle to get this type of result.  Is it possible?  
Thank you.
Al
report to look like this:
         
Pol_NO, F350.0, F353.0, F353.1, F555.5
------  ------  ------  ------  ------
P-4404, Y,      Y,      Y,      Y
P-0600, Y,      Y,      Y,
P-0603, Y,      Y
P-6171, Y
----
-- Script to create 3 table and insert sample data
CREATE TABLE SIMDATA.TABLE_A
(
  POL_NUM   VARCHAR2(9 BYTE),
  FORM_NUM  VARCHAR2(10 BYTE)
);

CREATE TABLE SIMDATA.TABLE_B
(
  POL_NUM   VARCHAR2(9 BYTE),
  FORM_NUM  VARCHAR2(10 BYTE)
);
CREATE TABLE SIMDATA.TABLE_C
(
  POL_NUM   VARCHAR2(9 BYTE),
  FORM_NUM  VARCHAR2(10 BYTE)
);
---DATA
SET DEFINE OFF;
Insert into SIMDATA.TABLE_A
   (POL_NUM, FORM_NUM)
 Values
   ('P-4404', 'F350.0');
Insert into SIMDATA.TABLE_A
   (POL_NUM, FORM_NUM)
 Values
   ('P-0600', 'F350.0');
Insert into SIMDATA.TABLE_A
   (POL_NUM, FORM_NUM)
 Values
   ('P-0603', 'F350.0');
Insert into SIMDATA.TABLE_A
   (POL_NUM, FORM_NUM)
 Values
   ('P-6171', 'F350.0');
Insert into SIMDATA.TABLE_A
   (POL_NUM, FORM_NUM)
 Values
   ('P-6533', 'F350.0');
COMMIT;
----TABLE B
SET DEFINE OFF;
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-4404', 'F353.0');
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-4404', 'F353.1');
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-0600', 'F353.1');
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-0600', 'F353.0');
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-5283', 'F353.1');
Insert into SIMDATA.TABLE_B
   (POL_NUM, FORM_NUM)
 Values
   ('P-0603', 'F353.0');
COMMIT;
---TABLE C
SET DEFINE OFF;
Insert into SIMDATA.TABLE_C
   (POL_NUM, FORM_NUM)
 Values
   ('P-4404', 'F555.5');
Insert into SIMDATA.TABLE_C
   (POL_NUM, FORM_NUM)
 Values
   ('P-9022', 'F555.5');
Insert into SIMDATA.TABLE_C
   (POL_NUM, FORM_NUM)
 Values
   ('P-2613', 'F555.5');
COMMIT;
ALad2005Asked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
We can also handle NULLs if you dont need them..

Updated code..

SELECT  
  pol_num
      ,NVL(MAX(decode(form_num,'F350.0','Y')),' ') F350_0
      ,NVL(MAX(decode(form_num,'F353.0','Y')),' ') F353_0
      ,NVL(MAX(decode(form_num,'F353.1','Y')),' ') F353_1
      ,NVL(MAX(decode(form_num,'F555.5','Y')),' ') F555_5
  FROM 
  (
      select a.POL_NUM,a.FORM_NUM FROM TABLE_A a 
      UNION ALL
      SELECT b.POL_NUM,b.FORM_NUM FROM TABLE_B b
      UNION ALL
      SELECT c.POL_NUM,c.FORM_NUM FROM TABLE_C c
  )a
  GROUP BY pol_num
  

Open in new window


OUTPUT
| POL_NUM | F350_0 | F353_0 | F353_1 | F555_5 |
|---------|--------|--------|--------|--------|
|  P-5283 |        |        |      Y |        |
|  P-4404 |      Y |      Y |      Y |      Y |
|  P-2613 |        |        |        |      Y |
|  P-9022 |        |        |        |      Y |
|  P-0600 |      Y |      Y |      Y |        |
|  P-6171 |      Y |        |        |        |
|  P-6533 |      Y |        |        |        |
|  P-0603 |      Y |      Y |        |        |
        

Open in new window

0
 
ALad2005Author Commented:
I came up with this.
but it's not quite right;
select distinct pol_num,
    decode(form_num,'F350.0','Y') F350_0,
    decode(form_num,'F353.0','Y') F353_0,
    decode(form_num,'F353.1','Y') F353_1,
    decode(form_num,'F555.5','Y') F555_5
from simdata.table_a
;
select distinct pol_num,
    decode(form_num,'F350.0','Y') F350_0,
    decode(form_num,'F353.0','Y') F353_0,
    decode(form_num,'F353.1','Y') F353_1,
    decode(form_num,'F555.5','Y') F555_5
from simdata.table_b;
select distinct pol_num,
    decode(form_num,'F350.0','Y') F350_0,
    decode(form_num,'F353.0','Y') F353_0,
    decode(form_num,'F353.1','Y') F353_1,
    decode(form_num,'F555.5','Y') F555_5
from simdata.table_c;
0
 
ALad2005Author Commented:
They are just 3 select statements
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Pawan KumarDatabase ExpertCommented:
Please try this

SELECT  
DISTINCT pol_num
    ,decode(form_num,'F350.0','Y') F350_0
    ,decode(form_num,'F353.0','Y') F353_0
    ,decode(form_num,'F353.1','Y') F353_1
    ,decode(form_num,'F555.5','Y') F555_5
FROM 
(
	select a.POL_NUM,a.FORM_NUM FROM TABLE_A a 
	UNION ALL
	SELECT b.POL_NUM,b.FORM_NUM FROM TABLE_B b
	UNION ALL
	SELECT c.POL_NUM,c.FORM_NUM FROM TABLE_C c
)a

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Updated code...

SELECT  
pol_num
    ,MAX(decode(form_num,'F350.0','Y')) F350_0
    ,MAX(decode(form_num,'F353.0','Y')) F353_0
    ,MAX(decode(form_num,'F353.1','Y')) F353_1
    ,MAX(decode(form_num,'F555.5','Y')) F555_5
FROM 
(
	select a.POL_NUM,a.FORM_NUM FROM TABLE_A a 
	UNION ALL
	SELECT b.POL_NUM,b.FORM_NUM FROM TABLE_B b
	UNION ALL
	SELECT c.POL_NUM,c.FORM_NUM FROM TABLE_C c
)a
GROUP BY pol_num

Open in new window


OUTPUT
| POL_NUM | F350_0 | F353_0 | F353_1 | F555_5 |
|---------|--------|--------|--------|--------|
|  P-5283 | (null) | (null) |      Y | (null) |
|  P-4404 |      Y |      Y |      Y |      Y |
|  P-2613 | (null) | (null) | (null) |      Y |
|  P-9022 | (null) | (null) | (null) |      Y |
|  P-0600 |      Y |      Y |      Y | (null) |
|  P-6171 |      Y | (null) | (null) | (null) |
|  P-6533 |      Y | (null) | (null) | (null) |
|  P-0603 |      Y |      Y | (null) | (null) |

Open in new window

0
 
awking00Commented:
Can you describe in more detail your criteria for selection. For example, pol_num P-6533 has a form_num of F350.0 but doesn't show in your desired output. Is there some sort of precedent set by table_A's pol_num that requires that pol_num to exist in the B and C tables before it can be included in the report along with any form_nums that are greater than the form_num (i.e. children) that matches the pol_num in table_A?
0
 
ALad2005Author Commented:
Thank you Pavan. That worked  for me !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.