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