CREATE A LINEAR/ROW RESULT

I run an SQL query that results in data being displayed in rows.  Some of the information is duplicated due to a few unique/distinct bits of data, i.e., multiple rows are produced for some VINs.  The reason for the multiple rows is due to the information gathered in columns D (APCVRCD), E (CVRCD_RESP_AM), L (LINE_NO) and M (VCOMMENTS) - attached is a workbook with the RAW data and the purposed FINISHED data;  I would like my results to look like the FINISHED worksheet.  How can I accomplish this?  If it can be accomplished using, then that would be great.  If it can be done using either Excel or Access, then that is fine too.   EE-DISPLAY-ROWS-LINEARLY.xls
castlerjSenior AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Swadhin RaySenior Technical Engineer Commented:
Can you please provide the logic for your expected result.
0
castlerjSenior AnalystAuthor Commented:
SELECT DISTINCT A.REPAIR_DEALER_CODE FCLTY, A.VIN_NUMBER VIN, A.CLAIM_NUMBER RO, A.CLAIM_LINE_NUMBER ROL, A.FIRST_SUBMISSION_DATE ADD_DTE, A.FINALIZE_REPAIR_DATE CSR_DTE,
      A.HDR_REQ_COVER RCVR_CD, A.HDR_APP_COVER ACVR_CD, D.APPROVED_COVERAGE ACVR_RESPON_CD, D.DEALER_TOTAL_PAID ACVR_RESPON_AM, B.LAST_UPDATE_USER_ID LUPDT_DTE, B.APPROVER_ID VAPP_UID,  C.LINE_NO, C.COMMENTS VCOMMENTS
FROM EDW.PNC_CLAIMS                                A,
       EDW.PNC_VCAN_CLAIMS         B,
       EDW.PNC_VCAN_COMMENTS   C,
       EDW.PNC_CLAIM_COVERAGES D
WHERE A.REPAIR_DEALER_CODE = B.FACILITY_NO (+)
AND A.VIN_NUMBER = B.VIN (+)
AND A.CLAIM_NUMBER =B.WORKORDER_NO (+)
AND A.CLAIM_LINE_NUMBER = B.WORKORDERLINE_NO (+)
AND A.REPAIR_DEALER_CODE = C.DEALER_NO (+)
AND A.VIN_NUMBER = C.VIN (+)
AND A.CLAIM_NUMBER =C.WORKORDER_NO (+)
AND A.CLAIM_LINE_NUMBER = C.WORKORDERLINE_NO (+)
AND A.REPAIR_DEALER_CODE = D.REPAIR_DEALER_CODE (+)
AND A.VIN_NUMBER = D.VIN_NUMBER (+)
AND A.CLAIM_NUMBER =D.CLAIM_NUMBER (+)
AND A.CLAIM_LINE_NUMBER = D.CLAIM_LINE_NUMBER (+)
AND A.HDR_APP_COVER = 'FG'
AND A.FINALIZE_REPAIR_DATE BETWEEN '01OCT2011' AND '31OCT2011'
ORDER BY A.REPAIR_DEALER_CODE, A.VIN_NUMBER, A.CLAIM_NUMBER, A.CLAIM_LINE_NUMBER
0
Swadhin RaySenior Technical Engineer Commented:
provide some sample data for the tables :
EDW.PNC_CLAIMS  ,EDW.PNC_VCAN_CLAIMS , EDW.PNC_VCAN_COMMENTS   ,EDW.PNC_CLAIM_COVERAGES

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.

castlerjSenior AnalystAuthor Commented:
Attached is a data for one facility from December 1, 2011 to date for the requested tables. EE-DATA-LINEAR-QUESTION.xls
0
Swadhin RaySenior Technical Engineer Commented:
I will test and reply soon .
0
Swadhin RaySenior Technical Engineer Commented:
Let take an example like I have a table with structure as :

CREATE TABLE MYPRODS
  ( ACVR_RESPON_CD VARCHAR2(3), ACVR_RESPON_AM NUMBER  ) ;

Open in new window


/*Insert records */
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('DI',340.14);
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('FG',600);

commit;

Open in new window


Now when you select the records will be like :

SQL> select * from MYPRODS;

ACV ACVR_RESPON_AM
--- --------------
DI          340.14
FG             600

Open in new window


And my expected result will be like:

ACVR_RESPON_CD    ACVR_RESPON_AM       ACVR_RESPON_CD_1          ACVR_RESPON_AM_1
======================================================================
DI                                          340.14                           FG                                         600


Query to get the expected result :

SQL> SELECT a1.ACVR_RESPON_CD,
  2    a2.ACVR_RESPON_CD,
  3    a1.ACVR_RESPON_AM ,
  4    a2.ACVR_RESPON_AM
  5  FROM MYPRODS a1 ,
  6    MYPRODS a2
  7  WHERE a1.ACVR_RESPON_AM > a2.ACVR_RESPON_AM ;

ACVR_RESPON_CD    ACVR_RESPON_AM       ACVR_RESPON_CD_1          ACVR_RESPON_AM_1
--- --- -------------- --------------
FG  DI             600         340.14

Open in new window


0
Swadhin RaySenior Technical Engineer Commented:
Sorry as per expected result try like :

SQL> SELECT a2.ACVR_RESPON_CD,a2.ACVR_RESPON_AM,
  a1.ACVR_RESPON_CD,
  a1.ACVR_RESPON_AM   
FROM MYPRODS a1 ,
  MYPRODS a2
WHERE a1.ACVR_RESPON_AM > a2.ACVR_RESPON_AM ;
     
 ACVR_RESPON_CD    ACVR_RESPON_AM  ACVR_RESPON_CD_1    ACVR_RESPON_AM_1
======================================================================
DI	                                     340.14	            FG	                               600

Open in new window

0
Swadhin RaySenior Technical Engineer Commented:
Try to create a view based on your query and then try to run the query as below:

CREATE VIEW   LINEARLY AS SELECT DISTINCT A.REPAIR_DEALER_CODE FCLTY,
    A.VIN_NUMBER VIN,
    A.CLAIM_NUMBER RO,
    A.CLAIM_LINE_NUMBER ROL,
    A.FIRST_SUBMISSION_DATE ADD_DTE,
    A.FINALIZE_REPAIR_DATE CSR_DTE,
    A.HDR_REQ_COVER RCVR_CD,
    A.HDR_APP_COVER ACVR_CD,
    D.APPROVED_COVERAGE ACVR_RESPON_CD,
    D.DEALER_TOTAL_PAID ACVR_RESPON_AM,
    B.LAST_UPDATE_USER_ID LUPDT_DTE,
    B.APPROVER_ID VAPP_UID,
    C.LINE_NO,
    C.COMMENTS VCOMMENTS
  FROM PNC_CLAIMS A,
    PNC_VCAN_CLAIMS B,
    PNC_VCAN_COMMENTS C,
    PNC_CLAIM_COVERAGES D
  WHERE A.REPAIR_DEALER_CODE = B.FACILITY_NO (+)
  AND A.VIN_NUMBER           = B.VIN (+)
  AND A.CLAIM_NUMBER         =B.WORKORDER_NO (+)
  AND A.CLAIM_LINE_NUMBER    = B.WORKORDERLINE_NO (+)
  AND A.REPAIR_DEALER_CODE   = C.DEALER_NO (+)
  AND A.VIN_NUMBER           = C.VIN (+)
  AND A.CLAIM_NUMBER         =C.WORKORDER_NO (+)
  AND A.CLAIM_LINE_NUMBER    = C.WORKORDERLINE_NO (+)
  AND A.REPAIR_DEALER_CODE   = D.REPAIR_DEALER_CODE (+)
  AND A.VIN_NUMBER           = D.VIN_NUMBER (+)
  AND A.CLAIM_NUMBER         =D.CLAIM_NUMBER (+)
  AND A.CLAIM_LINE_NUMBER    = D.CLAIM_LINE_NUMBER (+)
  AND A.HDR_APP_COVER        = 'FG'
  AND A.FINALIZE_REPAIR_DATE BETWEEN '01DEC2011' AND '31DEC2011'
  ORDER BY A.REPAIR_DEALER_CODE,
    A.VIN_NUMBER,
    A.CLAIM_NUMBER,
    A.CLAIM_LINE_NUMBER ;

Open in new window


Expected result based on your excel
SELECT a1.FCLTY ,
  a1.VIN ,
  a1.RO ,
  a1.ROL ,
  a1.ADD_DTE ,
  a1.CSR_DTE ,
  a1.RCVR_CD ,
  a1.ACVR_CD ,
  a1.ACVR_RESPON_CD,
  a2.ACVR_RESPON_CD,
  a1.ACVR_RESPON_AM ,
  a2.ACVR_RESPON_AM ,
  a1.LUPDT_DTE ,
  a1.VAPP_UID ,
  a1.LINE_NO ,
  a2.LINE_NO ,
  a1.VCOMMENTS ,
  a2.VCOMMENTS
FROM LINEARLY a1 ,
  LINEARLY a2
WHERE a1.ACVR_RESPON_AM > a2.ACVR_RESPON_AM ;

Open in new window

0
castlerjSenior AnalystAuthor Commented:
OK, I'll give it a go...
0
castlerjSenior AnalystAuthor Commented:
Attached are my results...it appears that I have duplicated data in my LINEARLY table (not sure why)...also, I did not include the LINE_NO or VCOMMENTS yet as I wanted to get the ACVR_RESPON_CD and AM down first...how does the code handle 3 ACVR_RESPON_CD (FG, DI, CP) for the same VIN, RO, ROL? EE-RESPON-TEST-LINEAR-PNC-CLAIM-.xls
0
Swadhin RaySenior Technical Engineer Commented:
Try like this :

--Create the table 

CREATE TABLE MYPRODS
  ( ACVR_RESPON_CD VARCHAR2(3), ACVR_RESPON_AM NUMBER  ) ;

/*Insert records */
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('DI',340.14);
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('DI',340.14);
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('FG',600);
Insert into MYPRODS (ACVR_RESPON_CD,ACVR_RESPON_AM) values ('FG',600);

commit;

-- Query the table 

select * from myprods;

--Output 
ACVR_RESPON_CD ACVR_RESPON_AM
------------------------------
DI	            340.14
DI	            340.14
FG	            600
FG	            600

-- Expected result 

ACVR_RESPON_CD ACVR_RESPON_AM ACVR_RESPON_CD_1 ACVR_RESPON_AM_1
-----------------------------------------------------------------
DI	              340.14	            FG	            600


-- Query to convert the column data to break in rows

SELECT a2.ACVR_RESPON_CD,a2.ACVR_RESPON_AM,
  a1.ACVR_RESPON_CD ACVR_RESPON_CD_1,
  a1.ACVR_RESPON_AM  ACVR_RESPON_AM_1  
FROM MYPRODS a1 ,
  MYPRODS a2
WHERE a1.ACVR_RESPON_AM > a2.ACVR_RESPON_AM ;    

-- Got duplicate records , so now for eleminating duplicate records do like below:

select *  FROM myprods A WHERE ROWID > (
      SELECT min(rowid) FROM myprods B
      WHERE A.ACVR_RESPON_CD = B.ACVR_RESPON_CD);
      
--Output 

ACVR_RESPON_CD ACVR_RESPON_AM
------------------------------
DI	            340.14
FG	            600

-- Based on the above out put if run the earlier query then we will get our desired output:

--- Creating a view eliminating the views from the table 

CREATE VIEW myprod_v AS
SELECT *
FROM myprods A
WHERE ROWID >
  ( SELECT MIN(rowid) FROM myprods B WHERE A.ACVR_RESPON_CD = B.ACVR_RESPON_CD );
  
----- Final Query to get the desired result : 
  
  SELECT a2.ACVR_RESPON_CD,
  a2.ACVR_RESPON_AM,
  a1.ACVR_RESPON_CD ACVR_RESPON_CD_1,
  a1.ACVR_RESPON_AM ACVR_RESPON_AM_1
FROM myprod_v a1 ,
  myprod_v a2
WHERE a1.ACVR_RESPON_AM > a2.ACVR_RESPON_AM ;


-- Final Output:

ACVR_RESPON_CD ACVR_RESPON_AM ACVR_RESPON_CD_1 ACVR_RESPON_AM_1
-----------------------------------------------------------------
DI	              340.14	            FG	            600

Open in new window

0
castlerjSenior AnalystAuthor Commented:
Sorry for not responding...I was away and have not been able to test the code/query...I will test it within the next 24 hours...thanks for your understanding...
0
Swadhin RaySenior Technical Engineer Commented:
if your issue is sloved then you can close this question.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
castlerjSenior AnalystAuthor Commented:
Solved my question...easy to follow...thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

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.