Solved

Oracle Query Help

Posted on 2011-03-17
12
273 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
  • 6
  • 4
  • 2
12 Comments
 
LVL 40

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 40

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
 

Author Comment

by:GRChandrashekar
ID: 35163246
FOR COMPANYACCNO

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

867 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now