Solved

Oracle Query Help

Posted on 2011-03-17
12
274 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
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.

 

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
 
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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

777 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