Question on Creating Placeholder Rows

[Paul Tennis]

OK, so here’s the problem.
I have a flat table which has summary information for each department.
The only possible statuses are 1, 0 and -1.
When I run my plain vanilla SQL query I get something like this:

DEPT COUNT PERCENT TOTAL STATUS COMPLIANCY
Dept1 18 81.8 22 1 Compliant
Dept1 1 4.5 22 0 Non Compliant
Dept1 3 13.6 22 -1 Unknown
Dept2 6 85.7 7 1 Compliant
Dept2 1 14.3 7 -1 Unknown
Dept3 1 50 2 1 Compliant
Dept3 1 50 2 0 Non Compliant
Dept4 1 100 1 1 Compliant


Whenever there are records for all statuses, three rows are displayed.
But if a department does not have a particular status, then there is no row presented (obviously).
I would like to create ‘dummy’ rows in the result for those department statuses which are not present in the table. This is necessary so that I can export the results to Excel for further analysis i.e. pivots tables. Note that the “Compliancy” field is a Case statement based on Status. I’m not looking to update the table, just the result of the query.

For example, Dept1 in the above sample has all three statuses, so no rows need to be created.

Dept2 is missing a status 0 row, so I need to create a row such as:

Dept2 0 0 0 0

Likewise Dept3 needs a dummy row such as:

Dept3 0 0 0 -1

Dept4 needs 2 dummy rows such as:

Dept4 0 0 0 0
Dept4 0 0 0 -1

I don’t need a full solution, but if someone can provide the logic, that would be helpful. Though not necessary, if there’s a way to copy the “Total” amounts to the new rows that would be great.

Thanks in advance,

PaulT

[Oralloy]

Paul,

Create a table of department/statuses and outer join that to your data. That will give you NULL values for the undefined fields.

Alternately, you can create a query that builds the necessary rows from a table of statuses and departments, and then join that to your basic query using a union. Something like this:

SELECT DEPT, COUNT, PERCENT, TOTAL, STATUS, COMPLIANCY
  FROM StatusTable
UNION
SELECT std.DEPT, 0, 0, 0, sts.STATUS, 0
  FROM (SELECT DISTINCT DEPT FROM StatusTable) std,
       (SELECT DISTINCT STATUS FROM StatusTable) sts
  WHERE (0 =
         (SELECT ANY(*)
            FROM StatusTable
            WHERE (DEPT = std.DEPT)
                  AND (STATUS = sts.STATUS))))

At least that's where I'd building the query at.

Please let us know what you come up with.

Good luck!

[Paul Tennis]

Thanks Oralloy,
I ended up linking to the Oracle table in Access through ODBC and creating a table of dummy records in Access, then running a query taking all the Oracle table records and a UNION to select those dummy records whose department/status was not one of the Oracle records. It worked fine, perhaps not the most elegant, but it worked. Thanks for pointing me in the right direction.

PaulT

[amitpatel66]

Also you can try this:

SQL> ed
Wrote file afiedt.buf
 
  1  with t as (select 'dept1' col1,1 col2,'Compliant' col3 from dual UNION
  2  select 'dept1',0,'Non Compliant' from dual UNION
  3  select 'dept1',-1,'Unknown' from dual
  4  UNION select 'dept2',-1,'Unknown' from dual
  5  UNION select 'dept2',1,'Compliant' from dual
  6  UNION select 'dept3',1,'Compliant' from dual)
  7  select x.col1,x.num,t.col3 FROM
  8  (SELECT distinct col1,num from t,(SELECT 0 num FROM Dual
  9  union select 1 from dual
 10  union select -1 from dual)) x,t
 11  WHERE t.col1(+) = x.col1
 12  AND   t.col2(+) = x.num
 13* order by x.col1,x.num DESC
SQL> /
 
COL1         NUM COL3
----- ---------- -------------
dept1          1 Compliant
dept1          0 Non Compliant
dept1         -1 Unknown
dept2          1 Compliant
dept2          0
dept2         -1 Unknown
dept3          1 Compliant
dept3          0
dept3         -1
 
9 rows selected.
 
SQL> 
 

[Paul Tennis]

Oralloy,
I've been dissecting your SQL example to try to learn from it. I've never used the ANY operator. When I adapted your query I get an error which I think it occurring around this part:
WHERE (0 =
(SELECT ANY(*)

ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action:
Error at Line: 7 Column: 11

Any insight is appreciated.
PaulT

[Oralloy]

Paul,

I did my example in Access 2007, so there may be an SQL disconnect between our dialects. More on this in a sec.

To answer your first question, the ANY aggregate function returns 0 if no rows match the WHERE clause, and 1 if any row at all matches the where clause. It's like the COUNT aggregate function, except that it allows the server to avoid actually counting all matches.

Another thing that may be biting you is my use of an embedded SELECT in the WHERE clause. Again, Access lets me do this, Oracle may have a problem with it. One way to test is to replace the SELECT with a constant or a simple calculation, just to see if the code gets past the parser.

Let me know what happens. Once we know the cause of the trouble, then we can modify the query to work properly.

Luck!
Oralloy