Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: getting the difference in 10g

    Hi

    Could someone please advise how to get the difference between 2 columns like

    Code:
    Max_size - Current_size=?


    I tried
    Code:
    select * from table where 
    
    Max_size minus Current_size < 0
    
    'minus' but got 'Invalid relational operator'.
    and when I tried

    Code:
    Max_size - Current_size
    
    I got: Missing Null Keyword
    Max_Size and Current_Size are 2 columns in the table being queried.

    Regards
    Shajju

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Please show the full SQL statement you are running

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    This is an example of what I think you're trying to do (it's hard to tell without the full SQL statement), but comparing columns in two tables:

    Efficiently compare columns from two tables - PL/SQL - Snipplr Social Snippet Repository

    I'm sure you can easily amend the SQL to fit your needs.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Jun 2012
    Posts
    13
    Hi,
    I created some data as you mentioned. As details like datatype was not mentioned there, so created it might be different. If so please sent some details like column structure or data type.


    *** SCRIPT START : SessionTAGE@odsuq1(1) 14-Jun-2012 11:29:58 ***
    Processing ...
    WITH rec AS
    (
    SELECT 10 max_size, 8 CURRENT_SIZE FROM dual UNION ALL
    SELECT 25 max_size, 18 CURRENT_SIZE FROM dual UNION ALL
    SELECT 12 max_size, 20 CURRENT_SIZE FROM dual UNION ALL
    SELECT 7 max_size, 7 CURRENT_SIZE FROM dual UNION ALL
    SELECT 4 max_size, 5 CURRENT_SIZE FROM dual UNION ALL
    SELECT 2 max_size, 2 CURRENT_SIZE FROM dual UNION ALL
    SELECT 15 max_size, 18 CURRENT_SIZE FROM dual UNION ALL
    SELECT 12 max_size, 10 CURRENT_SIZE FROM dual UNION ALL
    SELECT 8 max_size, 8 CURRENT_SIZE FROM dual UNION ALL
    SELECT 10 max_size, 9 CURRENT_SIZE FROM dual UNION ALL
    SELECT NULL max_size, 9 CURRENT_SIZE FROM dual UNION ALL
    SELECT 14 max_size, NULL CURRENT_SIZE FROM dual
    )
    SELECT max_size - current_size FROM rec

    *** SCRIPT END : SessionTAGE@odsuq1(1) 14-Jun-2012 11:29:59 ***



    The result shows last 2 rows as null. (may be not visible here but you can run the query).
    Last edited by Prasad09; 06-14-12 at 03:10.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select *
    from table
    where nvl(Max_size,0) - nvl(Current_size,0) < 0
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Red face

    This link:ORA-00908: missing NULL keyword; should explain what your problem is. But since you don't want to give us the actual error or the actual SQL giving the problem, no one can really help you.
    I got: Missing Null Keyword
    1) that is not the full error message. People are better able to help with the full error message
    2) That SQL you are posting is NOT the actual SQL you are having problem with. We know that because you can't create a table called table.
    Code:
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    dayneo@RMSD> create table table (v number);
    create table table (v number)
                 *
    ERROR at line 1:
    ORA-00903: invalid table name
    
    dayneo@RMSD>
    To find a solution, you will need to:
    1) Open the sqlplus command line.
    2) Paste the ACTUAL problem SQL into sqlplus and run it.
    3) Copy the entire sqlplus screen from beginning to end (that should include the ACTUAL SQL statement as well as the ACTUAL error message)
    4) Post a reply and past the text you copied in step 3 into the [CODE] tags. And please make sure you use the CODE tags!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •