Avatar of Anil Lad
Anil LadFlag for Canada

asked on 

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;
Oracle DatabaseSQL

Avatar of undefined
Last Comment
Anil Lad
Avatar of Anil Lad
Anil Lad
Flag of Canada image

ASKER

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;
Avatar of Anil Lad
Anil Lad
Flag of Canada image

ASKER

They are just 3 select statements
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of awking00
awking00
Flag of United States of America image

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?
Avatar of Anil Lad
Anil Lad
Flag of Canada image

ASKER

Thank you Pavan. That worked  for me !
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo