My question is regarding FULL OUTER JOINs on derived tables. My query is as follows:
select tb1.usr,tb1.ADD,tb2.UPD from (select usr,count(nin) ADD from RGS8 where act='ADD' and dte=20080310 group by usr) tb1 FULL OUTER JOIN (select usr,count(nin) UPD from RGS8 where act='UPD' and dte=20080310 group by usr) tb2 ON tb1.usr=tb2.usr
The query executes successfully when using the left or the right outer joins but no results are given with the full outer joins. I wonder if there are some constraints with FULL OUTER JOINs?
I'm not sure why it isn't working for you, unless perhaps you're using MySQL, which doesn't support FULL OUTER JOIN. But in that case you should've received an error message, rather than no results.
Here's an alternate strategy:
SELECT usr , SUM(CASE WHEN act = 'ADD' THEN 1 ELSE 0 END) AS ADD , SUM(CASE WHEN act = 'UPD' THEN 1 ELSE 0 END) AS UPD FROM RGS8 WHERE act IN ('ADD','UPD') AND dte = 20080310 GROUP BY usr
This should perform better as well, since it requires only one pass of the table.
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading