Results 1 to 6 of 6

Thread: select stmt

  1. #1
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108

    Unanswered: select stmt

    hie guys i wanted to find the batch numbers which are not in this table 'trap_ra_vca_sc_p' from 'trap_stage_vca_sc_p'

    as i see manually there are some batch numbers not in the first table but then when i run this query, it says no rows return...
    its very weird... the datatype is number...

    i tried creating two test tables and tried with some example data, it works... any help ?

    Code:
    select * from trap_stage_vca_sc_p
    where batchnr not in (select batch_no from trap_ra_vca_sc_p)
    this is very very weird.. i tried left trim and right trim as well.. but it doesnt work

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >hie guys i wanted to find the batch numbers which are not in this table 'trap_ra_vca_sc_p' from 'trap_stage_vca_sc_p'

    OK, please step back & THINK!
    You are
    select from trap_ra_vca_sc_p
    Please explain how/why you expect SQL to report values that are NOT in this table?
    You can not SELECT values that do not exist; a VALUE must exist to be selected.
    I bet that 83574059463524 is not in trap_ra_vca_sc_p but I doubt that you want SQL to return it.

    Your query is backwards.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2005
    Location
    Kuala Lumpur, MY
    Posts
    108
    anacedent..
    okies i got what u meant...

    let me give an example

    lets say the table trap_ra_vca_sc_p has value,
    1, 2, 3

    and trap_stage_vca_sc_p has 1,2,3,4

    i want it to return '4'

    (i have attached the real situation... pls view and comment)
    Attached Files Attached Files
    Last edited by shatishr; 03-31-06 at 02:15.

  4. #4
    Join Date
    Sep 2004
    Posts
    60
    I had tried recreating your tables & was not able to reporduce your problem..
    See the following :

    SQL> select * from abc ;

    RA
    ----------
    1
    2
    3

    SQL> select * from abcd ;

    STAGE
    ----------
    1
    2
    3
    4

    SQL> select * from abcd where stage not in (select distinct ra from abc) ;

    STAGE
    ----------
    4

    am I missing any thing ??

  5. #5
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Can there be null values in trap_ra_vca_sc_p.batch_no?

    If so, try:

    Code:
    select * from trap_stage_vca_sc_p
    where  batchnr not in
           ( select batch_no from trap_ra_vca_sc_p
             where  batch_no is not null );

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    How about this?

    SELECT batchnr FROM trap_stage_vca_sc_p
    MINUS
    SELECT batch_no FROM trap_ra_vca_sc_p;

Posting Permissions

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