DaPi's 1 prize
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: DaPi's 1 prize

  1. #1
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    DaPi's 1 prize

    It's getting quiet around here.
    OK, for 1 and vast honour, explain this (true story):

    I wanted to select the rows in table A containing an id existing in a code table B. I coded:
    Code:
    Select * From A
    Where myId = (Select myId From B Where CodeVal = 'Something')
    There were in fact no rows in A containing the id I was looking for, however the query returned all the rows of table A.

    Why? What mistake had I made?




    Added later:
    If you want to work on this problem for fun, DON'T SCROLL DOWN The prizewinning answer is two posts further on.
    Last edited by DaPi; 04-29-2005 at 04:13 AM.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
    SQLdesc A
     Name                          Null
    ?    Type
     
    ----------------------------- -------- ----------------------------
     
    MYID                                    CHAR(1)
     
    NAME                                    VARCHAR2(20)

    SQLselect from a;

    M NAME
    - --------------------
      
    TAMIL
      DAPI

    SQL
    desc b
     Name                         Null
    ?    Type
     
    ---------------------------- -------- ----------------------------
     
    MYID                                  CHAR(1)
     
    CODEVAL                               VARCHAR2(20)

    SQLselect from b ;

    M CODEVAL
    - --------------------
      
    abc

    SQL
    select from a 
          where myid 
    = (select myid from b where codeval 'abc') ;

    M NAME
    - --------------------
      
    TAMIL
      DAPI 
    I enter ' ' value for MYID in both tables.
    Did I tell the correct answer?

    Tamil

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Table B doesn't have a column called myID ...
    Code:
    SQL> create table A (myId number);
    
    Table created.
    
    SQL> insert into A values (1);
    
    1 row created.
    
    SQL> insert into A values (2);
    
    1 row created.
    
    SQL> create table B (codeval varchar2(30));
    
    Table created.
    
    SQL> insert into B values ('Something');
    
    1 row created.
    
    SQL> Select * From A
      2  Where myId = (Select myId From B Where CodeVal = 'Something')
      3  /
    
          MYID
    ----------
             1
             2
    Your name is DaPi and I claim my five pounds. Or 1 cent.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by tamilselvan
    I enter ' ' value for MYID in both tables.
    Sorry Tamil, I said "There were in fact no rows in A containing the id I was looking for". I'd never use ' ' as an id, but nothing is stopping me.


    slimdave wins the prize - I think it's now two beers and 1 I owe you. I'm being generous because the example you give has no id column in table B.

    The real-life problem was that the two corresponding id's had different names:
    rlRequiredDocumentId and rlRequiredDocId
    and I typed the same name twice.

    Two or three lessons to learn
    - better discipline in design & specification
    - using aliases can help a lot:
    Select * From A Where A.myId = (Select B.myId From B Where B.CodeVal = 'Something')
    would have failed.
    - sub-queries inherit variables
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Thanks Slimdave & Dapi.
    Slim deserves Johnnie Walker Black label Scotch.
    Learned new stuff.

    Tamil

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    [B]I think it's now two beers and 1 I owe you. I'm being generous because the example you give has no id column in table B.
    Penalised for presenting the simplest possible example? Tut.

    I'l reserve judegment on the generosity issue until I get those beers and that cash. Maybe I'll start a DaPi Generosity timer on my blog. "Days With No Beer: n".
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    Originally posted by slimdave
    Penalised for presenting the simplest possible example? Tut.

    I'l reserve judegment on the generosity issue until I get those beers and that cash. Maybe I'll start a DaPi Generosity timer on my blog. "Days With No Beer: n".
    You can always drink Coors Lite !!!
    Don't they make that out your way???
    this space intentionally left blank

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by gandolf989
    You can always drink Coors Lite !!!
    Don't they make that out your way???
    I said "Beer", you scoundrel.

    B-E-E-R
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by slimdave
    Penalised for presenting the simplest possible example? Tut.
    You've just lost your 1, fails to meet spec:
    . . . containing an id existing in a code table B
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by DaPi
    You've just lost your 1, fails to meet spec:
    I wanted to select the rows in table A containing an id existing in a code table B.
    Hey, you said that that's what you wanted to do -- you didn't say that the code actually was in table B. From the code your intentions were never in doubt ... just your competence, my little furry friend.

    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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