troubleshooting Question

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

Avatar of Anil Lad
Anil LadFlag for Canada asked on
Oracle DatabaseSQL
7 Comments1 Solution104 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros