Solved

Oracle Query Help

Posted on 2011-03-17
12
278 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
12 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35163202
How many max. no. of MEMBERNAMEs you can have for a COMPANYACCNO?
0
 

Author Comment

by:GRChandrashekar
ID: 35163205
THREE
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35163236
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

Author Comment

by:GRChandrashekar
ID: 35163246
FOR COMPANYACCNO

THERE WILL BE
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35163255
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
 

Author Comment

by:GRChandrashekar
ID: 35163262
Yes it works request help to include
3 MEMBERACCNO
3 TITLE
3 MEMBERNAMES
0
 
LVL 41

Accepted Solution

by:
Sharath earned 450 total points
ID: 35163398
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
 

Author Comment

by:GRChandrashekar
ID: 35163686
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 35165525
Use regular expressions to split the address. I think it's a little cleaner. See attached.
query.txt
0
 

Author Comment

by:GRChandrashekar
ID: 35165791
Can you please help me re-write my query
0
 
LVL 32

Expert Comment

by:awking00
ID: 35165841
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
 

Author Comment

by:GRChandrashekar
ID: 35170104
@Sharath_123:

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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question