Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle SQL Sub Query

Posted on 2016-08-15
5
Medium Priority
?
62 Views
Last Modified: 2016-08-15
I have a query that I am running, code below, which is not giving the desired output. I have changed the joins etc to try to get the desired result with no success.

With my limited knowledge, I have come to the conclusion that a sub query may be required but am unsure how to structure it.

The result that I am looking for is all contracts that are 'ACTIVE' in C_ID'3' and the latest revision Y, no problem. The views are joined on the only option of customer account code.

The issue arises when I join the consultants view and need to retrieve the consultant details where the AC.EA_ACCOUNT_TYPE = 'C' for customer, the only other option is 'P' for prospect. In this view it is possible that  there will be some customers may have two entries, one as a 'C' and one as a 'P' I just need to retrieve the 'C's linked by account code.  

I hope that I have explained this well enough for you all to decipher! if not please ask.

Many thanks in advance,

Keith


SELECT C.REGISTRATION_NUMBER,
       C.ACCOUNT_CODE,
       C.NAME,
       AC.CLIENT_SUPPORT,
       AC.SALESPERSON,
       AC.ACCOUNT_MANAGER
  FROM STD_BI.RL2_CONTRACTS_VW C
       LEFT JOIN STD_BI.RL_ACCOUNT_CONSULTANTS_VW AC
          ON (AC.EA_ACCOUNT_CODE = C.ACCOUNT_CODE)
 WHERE     ( (C.CONTRACT_STATUS = 'ACTIVE') AND (C.C_ID = 3))
       AND (C.LATEST_REVISION = 'Y') AND (AC.EA_ACCOUNT_TYPE = 'C')
 GROUP BY C.REGISTRATION_NUMBER,
       C.ACCOUNT_CODE,
       C.NAME,
       AC.CLIENT_SUPPORT,
       AC.SALESPERSON,
       AC.ACCOUNT_MANAGER
0
Comment
Question by:kalees
[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
  • 2
  • 2
5 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 41756183
I'm not sure I completely understand what you are asking, but it sounds like you are getting rows where there isn't a 'C' in account type.  If that is the case, my guess is that you don't want the left join.
SELECT C.registration_number, 
       C.account_code, 
       C.NAME, 
       AC.client_support, 
       AC.salesperson, 
       AC.account_manager 
FROM   std_bi.rl2_contracts_vw C 
       JOIN std_bi.rl_account_consultants_vw AC 
         ON ( AC.ea_account_code = C.account_code ) 
WHERE  C.contract_status = 'ACTIVE' 
       AND C.c_id = 3 
       AND C.latest_revision = 'Y' 
       AND AC.ea_account_type = 'C' 
GROUP  BY C.registration_number, 
          C.account_code, 
          C.NAME, 
          AC.client_support, 
          AC.salesperson, 
          AC.account_manager 

Open in new window

There is no need for the parenthesis in your where clause.  They way they were structured it could prevent usage of an index.  It probably didn't, but it is possible.  They are all the same operator, AND, so you don't need them.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 41756185
In a brief I think that your select is OK. Why do you think it is not ?
0
 

Author Comment

by:kalees
ID: 41756237
Hi, many thanks for the responses, I maybe missed a little in the explanation.

There may not be a matching record in the consultants view so I am expecting to see some blanks for consultants in the returned data and there is not.

I have just done a test commenting out anything to do with consultants and have the correct number of records returned proving that the consultants is restricting the data set. This is why I was thinking about sub query, but as I said, this is somewhat out of my knowledge range!

Keith
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 41756287
Then this may be what you are looking for:
SELECT C.registration_number, 
       C.account_code, 
       C.name, 
       AC.client_support, 
       AC.salesperson, 
       AC.account_manager 
FROM   std_bi.rl2_contracts_vw C 
       left join std_bi.rl_account_consultants_vw AC 
              ON ( AC.ea_account_code = C.account_code 
                   AND AC.ea_account_type = 'C' ) 
WHERE  C.contract_status = 'ACTIVE' 
       AND C.c_id = 3 
       AND C.latest_revision = 'Y' 
GROUP  BY C.registration_number, 
          C.account_code, 
          C.name, 
          AC.client_support, 
          AC.salesperson, 
          AC.account_manager 

Open in new window

If that isn't it, please provide sample data and expected results.
1
 

Author Closing Comment

by:kalees
ID: 41756296
Perfect!

Many thanks Johnsone. I just need to understand having a where clause in the join now
0

Featured Post

Independent Software Vendors: 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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

719 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