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
GRChandrashekarAsked:
Who is Participating?
 
SharathData EngineerCommented:
check this.
SELECT CITYDESCRIPTION, 
         COMPANYACCNO, 
         COMPANYNAME, 
         ADDRESS, 
         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, 
         ADDRESS, 
         PINCODE, 
         PHONENO1

Open in new window

0
 
SharathData EngineerCommented:
How many max. no. of MEMBERNAMEs you can have for a COMPANYACCNO?
0
 
GRChandrashekarAuthor Commented:
THREE
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.

 
SharathData EngineerCommented:
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

0
 
GRChandrashekarAuthor Commented:
FOR COMPANYACCNO

THERE WILL BE
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES
0
 
SharathData EngineerCommented:
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.
0
 
GRChandrashekarAuthor Commented:
Yes it works request help to include
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES
0
 
GRChandrashekarAuthor Commented:
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
0
 
awking00Commented:
Use regular expressions to split the address. I think it's a little cleaner. See attached.
query.txt
0
 
GRChandrashekarAuthor Commented:
Can you please help me re-write my query
0
 
awking00Commented:
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,
0
 
GRChandrashekarAuthor Commented:
@Sharath_123:

Request help: Please alter the query to split address. Please help
0
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.

All Courses

From novice to tech pro — start learning today.