asked on

Oracle SQL Nested Select

Hi All,

I need to combine the three queries below so that I do not have to run all three separate and then use vlookup in excel to combine them.

Please see comments in query's below. The link for all three queries is the CONTRACT_NUMBER

--Query 1

SELECT DISTINCT 
       CONTRACT_NUMBER,
       NAME,
       REGISTRATION_NUMBER,
       VARIANT,
       PERIOD,
       DISTANCE,
       START_DATE,
       PLANNED_END_DATE,
       RL_CONTRACTS_VW.RESIDUAL_VALUE,
       RL_CONTRACTS_VW.ADJUSTED_RESIDUAL_VALUE,
       CURRENT_ODOMETER,
       CURRENT_ODO_DATE,
       PAYMENT_PROFILE,
       '' AS "CONTRACT TYPE",
       '' AS "FINANCE RENTAL",     -- Result of query 2
       '' AS "NON-FINANCE RENTAL",    --Result of query 3
       '' AS "TOTAL RENTAL"    --Sum of query 1 and query 2 result
       FROM STD_BI.RL_FLEET_MASTER_VW
       INNER JOIN STD_BI.RL_CONTRACTS_VW
          ON (VEHICLE_REGISTRATION_NUMBER = REGISTRATION_NUMBER)
 WHERE (CONTRACT_STATUS = 'ACTIVE') AND (C_ID = 3) AND (PRODUCT_TYPE = 'OL') AND LATEST_REVISION = 'Y'
ORDER BY NAME

Open in new window

--Query2

SELECT
CONTRACT_NUMBER,
CASE WHEN SERVICE_TYPE_CODE IN ('FINANCE', 'RFT') THEN 'FINANCE' END,
SUM(NEXT_SCHEDULED_RENTAL) FINANCE_RENTAL
FROM STD_BI.RL2_CONTRACTS_SERVICES_VW
WHERE SERVICE_TYPE_CODE IN ('FINANCE', 'RFT')
GROUP BY 
CONTRACT_NUMBER,
CASE WHEN SERVICE_TYPE_CODE IN ('FINANCE', 'RFT') THEN 'FINANCE'END

Open in new window

--Query 3

SELECT
CONTRACT_NUMBER,
CASE WHEN SERVICE_TYPE_CODE NOT IN ('FINANCE', 'RFT') THEN 'NON FINANCE' END,
SUM(NEXT_SCHEDULED_RENTAL) NON_FINANCE_RENTAL
FROM STD_BI.RL2_CONTRACTS_SERVICES_VW
WHERE SERVICE_TYPE_CODE NOT IN ('FINANCE', 'RFT')
GROUP BY 
CONTRACT_NUMBER,
CASE WHEN SERVICE_TYPE_CODE NOT IN ('FINANCE', 'RFT') THEN 'NON FINANCE' END

Open in new window

Any help would be really appreciated,

Keith.

Guy Hengel [angelIII / a3]

a "simple" way : you can combine them likely like this:
<query1> would need to be replaced by your actual first query

select ...
  from ( <query1> ) q1
  join ( <query2> ) q2 on q2.CONTRACT_NUMBER = q1.CONTRACT_NUMBER
  join ( <query3> ) q3 on q3.CONTRACT_NUMBER = q1.CONTRACT_NUMBER

Open in new window

it may be LEFT join's instead of the default 'inner' join
you may want to read up this article in regards to "distinct":
https://www.experts-exchange.com/articles/3203/DISTINCT-and-GROUP-BY-and-why-does-it-not-work-for-my-query.html

and you should also review this article, to make sure you are using aliases ALWAYS:
https://www.experts-exchange.com/articles/11135/Why-should-I-use-aliases-in-my-queries.html

ASKER CERTIFIED SOLUTION

Qlemo

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

Many thanks