asked on

Oracle SQL Sub Query

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

johnsone

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.

Helena Marková

In a brief I think that your select is OK. Why do you think it is not ?

kalees

ASKER

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

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kalees

ASKER

Perfect!

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