Post reply

know TSQL, need to learn PLSQL

  • February 7, 2011 at 8:21 am

    #244896

    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

    #1283180

    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

    #1283198

    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

    #1283202

    http://www.functionx.com/oracle/Lesson03.htm

  • February 7, 2011 at 9:23 am

    #1283212

    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

    #1283228

    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

    #1283279

    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

    #1283311

    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

    #1283315

    PaulB-TheOneAndOnly (2/7/2011)

    Oracle documentation is free so, when in doubt RTFM 😉

    Here http://www.oracle.com/pls/db102/portal.all_books

    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