asked on

Oracle Query Help

Dear Experts

I need your help

I have query as follows

/* Formatted on 18/03/2011 11:42:48 AM (QP5 v5.136.908.31019) */
SELECT CITY.CITYDESCRIPTION,
COMPANY.COMPANYACCNO,
COMPANY.COMPANYNAME,
COMPANY.ADDRESS,
COMPANY.PINCODE,
COMPANY.PHONENO1,
MEMBER.MEMBERACCNO,
MEMBER.MEMBERNAME,
TITLE.TITLEDESCRIPTION
FROM CITY
JOIN COMPANY
ON (CITY.CITY_ID = COMPANY.CITY_ID)
JOIN MEMBER
ON (COMPANY.COMPANY_ID = MEMBER.COMPANY_ID)
JOIN TITLE
ON (MEMBER.SPOUSETITLE_ID = TITLE.TITLE_ID)
WHERE (MEMBER.FLAG = 0)

In attached excel sheet, I have given actual output and required output.
I need to alter this query to get required output
Urgent.xls

Sharath S

How many max. no. of MEMBERNAMEs you can have for a COMPANYACCNO?

GRChandrashekar

ASKER

THREE

Sharath S

Can you check this?

SELECT CITYDESCRIPTION, COMPANYACCNO, COMPANYNAME, ADDRESS, PINCODE, PHONENO1, MEMBERACCNO, 
         MAX(CASE 
               WHEN rn = 1 THEN MEMBERNAME 
             END) MEMBERNAME, 
         MAX(CASE 
               WHEN rn = 2 THEN MEMBERNAME 
             END) MEMBERNAME2, 
         MAX(CASE 
               WHEN rn = 3 THEN MEMBERNAME 
             END) MEMBERNAME3, 
         TITLEDESCRIPTION 
    FROM (SELECT CITY.CITYDESCRIPTION, COMPANY.COMPANYACCNO, COMPANY.COMPANYNAME, COMPANY.ADDRESS, COMPANY.PINCODE, 
                 COMPANY.PHONENO1, MEMBER.MEMBERACCNO, MEMBER.MEMBERNAME, TITLE.TITLEDESCRIPTION, 
                 ROW_NUMBER() 
                   OVER(PARTITION BY COMPANY.COMPANYACCNO ORDER BY MEMBER.MEMBERNAME) rn 
            FROM CITY 
                 JOIN COMPANY 
                   ON (CITY.CITY_ID = COMPANY.CITY_ID) 
                 JOIN MEMBER 
                   ON (COMPANY.COMPANY_ID = MEMBER.COMPANY_ID) 
                 JOIN TITLE 
                   ON (MEMBER.SPOUSETITLE_ID = TITLE.TITLE_ID) 
           WHERE (MEMBER.FLAG = 0)) t1 
GROUP BY CITYDESCRIPTION, COMPANYACCNO, COMPANYNAME, ADDRESS, PINCODE, PHONENO1, MEMBERACCNO, TITLEDESCRIPTION

Open in new window

GRChandrashekar

ASKER

FOR COMPANYACCNO

THERE WILL BE
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES

Sharath S

Run my above query and see if you are able to get MemberName in one record. The same logic can be implemented for other columns also.

GRChandrashekar

ASKER

Yes it works request help to include
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRChandrashekar

ASKER

One last help. I am tyring to split the address into 4 parts but not sure where to split so it gives error

SELECT CITYDESCRIPTION,
COMPANYACCNO,
COMPANYNAME,

TRIM (SUBSTR (COMPANY.ADDRESS,
1,
INSTR (COMPANY.ADDRESS, '$', 1, 1) - 1
)
) AS ADDRESS1,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 1) + 1,
(INSTR (COMPANY.ADDRESS, '$', 1, 2) - 1
)
- (INSTR (COMPANY.ADDRESS, '$', 1, 1))
)
) AS ADDRESS2,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 2) + 1,
(INSTR (COMPANY.ADDRESS, '$', 1, 3) - 1
)
- (INSTR (COMPANY.ADDRESS, '$', 1, 2))
)
) AS ADDRESS3,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 3) + 1
)
) AS ADDRESS4,
PINCODE,
PHONENO1,
MAX(CASE
WHEN rn = 1 THEN MEMBERACCNO
END) MEMBERACCNO,
MAX(CASE
WHEN rn = 2 THEN MEMBERACCNO
END) MEMBERACCNO2,
MAX(CASE
WHEN rn = 3 THEN MEMBERACCNO
END) MEMBERACCNO3,
MAX(CASE
WHEN rn = 1 THEN MEMBERNAME
END) MEMBERNAME,
MAX(CASE
WHEN rn = 2 THEN MEMBERNAME
END) MEMBERNAME2,
MAX(CASE
WHEN rn = 3 THEN MEMBERNAME
END) MEMBERNAME3,
MAX(CASE
WHEN rn = 1 THEN TITLEDESCRIPTION
END) TITLEDESCRIPTION,
MAX(CASE
WHEN rn = 2 THEN TITLEDESCRIPTION
END) TITLEDESCRIPTION2,
MAX(CASE
WHEN rn = 3 THEN TITLEDESCRIPTION
END) TITLEDESCRIPTION3
FROM (SELECT CITY.CITYDESCRIPTION,
COMPANY.COMPANYACCNO,
COMPANY.COMPANYNAME,
COMPANY.ADDRESS,
COMPANY.PINCODE,
COMPANY.PHONENO1,
MEMBER.MEMBERACCNO,
MEMBER.MEMBERNAME,
TITLE.TITLEDESCRIPTION,
ROW_NUMBER()
OVER(PARTITION BY COMPANY.COMPANYACCNO ORDER BY MEMBER.MEMBERNAME) rn
FROM CITY
JOIN COMPANY
ON (CITY.CITY_ID = COMPANY.CITY_ID)
JOIN MEMBER
ON (COMPANY.COMPANY_ID = MEMBER.COMPANY_ID)
JOIN TITLE
ON (MEMBER.SPOUSETITLE_ID = TITLE.TITLE_ID)
WHERE (MEMBER.FLAG = 0)) t1
GROUP BY CITYDESCRIPTION,
COMPANYACCNO,
COMPANYNAME,
PINCODE,
PHONENO1

SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GRChandrashekar

ASKER

Can you please help me re-write my query

awking00

You should just be able to replace this -
TRIM (SUBSTR (COMPANY.ADDRESS,
1,
INSTR (COMPANY.ADDRESS, '$', 1, 1) - 1
)
) AS ADDRESS1,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 1) + 1,
(INSTR (COMPANY.ADDRESS, '$', 1, 2) - 1
)
- (INSTR (COMPANY.ADDRESS, '$', 1, 1))
)
) AS ADDRESS2,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 2) + 1,
(INSTR (COMPANY.ADDRESS, '$', 1, 3) - 1
)
- (INSTR (COMPANY.ADDRESS, '$', 1, 2))
)
) AS ADDRESS3,
TRIM (SUBSTR (COMPANY.ADDRESS,
INSTR (COMPANY.ADDRESS, '$', 1, 3) + 1
)
) AS ADDRESS4,
with this -
regexp_substr(address,'^[^$]+') as address1,
trim('$' from regexp_substr(address,'\$[^$]+')) as address2,
trim('$' from regexp_substr(address,'\$[^$]+',1,2)) as address3,
trim('$' from regexp_substr(address,'\$[^$]+',1,3)) as address4,

GRChandrashekar

ASKER

@Sharath_123:

Request help: Please alter the query to split address. Please help