Solved

Oracle Query Help

Posted on 2011-03-17
12
276 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ER Diagram 3 42
how to find out the count of records based on the subfolders paths 11 39
Row_number in SQL 6 45
plsql job on oracle 18 76
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 …
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 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 video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

749 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