Exists
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Exists

  1. #1
    Join Date
    Feb 2002
    Posts
    25

    Exists

    Hello,

    How can I substitute "exists" in the following query:

    select *
    from tables
    where adj.REASON_CD not in ('LG01','LGLF')
    and adj.ADJUSTMENT_ID in (select max(a.adjustment_id)
    from web1.adjustment a
    where a.master_account_id = da.MASTER_ACCOUNT_ID)


    Thanks in advance,

    BV

  2. #2
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    I didn't see the possibility to use 'exists' in this statement.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by dba1
    I didn't see the possibility to use 'exists' in this statement.
    Code:
    select *
    from tables
    where adj.REASON_CD not in ('LG01','LGLF')
    and exists ( select null
                 from web1.adjustment a
                 where a.master_account_id = da.MASTER_ACCOUNT_ID
    	     having adj.ADJUSTMENT_ID = max(a.adjustment_id );
    but i don't sure that this is the best way for this query.

  4. #4
    Join Date
    Jan 2002
    Location
    Canada
    Posts
    195
    Originally posted by Shestakov
    Code:
    select *
    from tables
    where adj.REASON_CD not in ('LG01','LGLF')
    and exists ( select null
                 from web1.adjustment a
                 where a.master_account_id = da.MASTER_ACCOUNT_ID
    	     having adj.ADJUSTMENT_ID = max(a.adjustment_id );
    but i don't sure that this is the best way for this query.
    This SQL statement will not work.

  5. #5
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by dba1
    This SQL statement will not work.
    What's wrong with query???
    Code:
    SQL> select o.object_name, o.object_id
    from all_objects o
    where o.object_type not in ('PROCEDURE', 'FUNCTION')
    and exists (select null
                from sys.obj$ s
                where s.obj# = o.object_id
                having o.object_id = max(s.dataobj#)
               )
    and rownum < 10;
      2    3    4    5    6    7    8    9  
    OBJECT_NAME			OBJECT_ID
    ------------------------------ ----------
    ACCESS$ 			       89
    AQ$_MESSAGE_TYPES		     2407
    AQ$_MSGTYPES_PRIMARY		     2408
    AQ$_PENDING_MESSAGES		     2411
    AQ$_PROPAGATION_STATUS		     2409
    AQ$_PROPAGATION_STATUS_PRIMARY	     2410
    AQ$_QTABLE_AFFINITIES_PK	     2401
    AQ$_QUEUE_STATISTICS		     2413
    AQ$_QUEUE_STATITICS_PK		     2414
    
    9 rows selected.

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dba1
    This SQL statement will not work.
    Could you explain why not???
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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



Click Here to Expand Forum to Full Width