SQL How-to NVL, NVL2 and KEEP Dense rank

A video on using the switching functions NVL and NVL2 to handle null values in SQL and how to create a binary flag. Also the use of Keep dense rank to return a value from the first or last row in a rank argument. Part of the SQL How-To Series

A short video on how you can import Excel data into an Oracle database using AllAround Automations PLSQL Developer

Thanks to AllAround: www.allroundautomations.com

Check out my site for Associated Blog: http://scottstansfield.com/

Follow me on Twitter: twitter.com/biztechscott

NVL, NVL2, Keep dense rank – Excel Output

 

 SELECT cust.customer_id
,cust.first_name
,cust.last_name
,cust.company_name
, sales.sale_date
, sales.delivery_date
, max(nvl2(calls.customer_id,'Y','N')) cust_called
, max(calls.call_date) last_call
, max(agent.department) KEEP (DENSE_RANK FIRST ORDER BY call_date nulls last) as last_department
, max(demand.demand_desc) KEEP (DENSE_RANK FIRST ORDER BY call_date nulls last) as last_department
FROM customer_master cust
, sales
left join calls_taken calls
on sales.customer_id = calls.customer_id
and call_date between sales.sale_date and nvl(sales.delivery_date,sysdate)
left join agent
on calls.agent = agent.agent
left join demand
on calls.demand_id = demand.demand_id
where cust.customer_id = sales.customer_id
group by cust.customer_id
,cust.first_name
,cust.last_name
,cust.company_name
, sales.sale_date
, sales.delivery_date

Leave a Reply

Your email address will not be published. Required fields are marked *