February 7, 2011 at 8:21 am
I'm just starting to write some queries using Oracle SQL Developer on a 10g database, and I'm finding it really difficult to write even some basic queries like:
select (select count(*) from table1) + (select count(*) from table2)
Could someone let me know how to write that in PLSQL? The resident Oracle expert at my job is telling me I'd have to create my report by getting those results separately and using a formula in excel to get the number I need... ridiculous...
February 7, 2011 at 8:57 am
Oracle requires you to have a FROM regardless of whether you're returning the results of a table - because of this, they have created a special table called 'dual' which you must select from if you want to return a single row with scalar values, so your query would be:
select (select count(*) from table1) + (select count(*) from table2) from dual;
February 7, 2011 at 9:10 am
Cool, that worked, thanks! Well, it worked for +, -, *, but not for /. Does oracle use another symbol for division?
February 7, 2011 at 9:14 am
February 7, 2011 at 9:23 am
Hi Ninja, I looked at the link you provided, but I do not understand how that answers my question. Here's the statement I'm trying to run:
select
((select count(*) from table1)
/
(select count(*) from table1))
from dual;
I get error message "SQL command not properly ended". The statement works as expected when you replace the / with a +.
February 7, 2011 at 9:29 am
I don't believe you can start a line with a slash in Oracle as it's a reserved character (for executing a batch) - take the carraige returns out and it should work fine
February 7, 2011 at 10:16 am
Awesome, the statement below worked, thanks HowardW!
select
((select count(*) from table1) /
(select count(*) from table1))
from dual;
February 7, 2011 at 10:54 am
Oracle documentation is free so, when in doubt RTFM 😉
Here http://www.oracle.com/pls/db102/portal.all_books
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 7, 2011 at 11:00 am
PaulB-TheOneAndOnly (2/7/2011)
Oracle documentation is free so, when in doubt RTFM 😉
And I thought BOL was huge!!! :w00t:
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply