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;
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;
Last Comment
ASKER
They are just 3 select statements
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
Updated code...
OUTPUT
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
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) |
ASKER CERTIFIED SOLUTION
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.
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?
ASKER
Thank you Pavan. That worked for me !
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
TRUSTED BY
ASKER
but it's not quite right;
select distinct pol_num,
decode(form_num,'F350.0','
decode(form_num,'F353.0','
decode(form_num,'F353.1','
decode(form_num,'F555.5','
from simdata.table_a
;
select distinct pol_num,
decode(form_num,'F350.0','
decode(form_num,'F353.0','
decode(form_num,'F353.1','
decode(form_num,'F555.5','
from simdata.table_b;
select distinct pol_num,
decode(form_num,'F350.0','
decode(form_num,'F353.0','
decode(form_num,'F353.1','
decode(form_num,'F555.5','
from simdata.table_c;