Marcel Marino Marcel Marino - 2 months ago 15
Java Question

Oracle Subquery Returning No Records

We primarily use SQL Server for our queries, but it seems that when we use Java it runs in an Oracle engine. In this question I asked for the SQL Server method of counting records from multiple tables since most of the time SQL Server runs the same as Oracle. I was able to get data returned from this query:

declare @count table (count1 int, count2 int, count3 int)
INSERT INTO @count
select (SELECT COUNT(*) FROM Bike),
(SELECT COUNT(*) FROM Car),
(SELECT COUNT(*) FROM Truck)
select * from @count;


However when putting this in Java I get an error that no records were returned. Any idea what I can do here to fix this up?

Answer

In Oracle, you need to use the dual table:

select (SELECT COUNT(*) FROM Bike),
       (SELECT COUNT(*) FROM Car),
       (SELECT COUNT(*) FROM Truck)
from dual;

If you need to return these values in a single row, then the following should work in either database:

select b.bcnt, c.ccnt, t.tcnt
from (SELECT COUNT(*) as bcnt FROM Bike) b cross join
     (SELECT COUNT(*) as ccnt FROM Car) c cross join
     (SELECT COUNT(*) as tcnt FROM Truck) t;
Comments