Post reply

Select Statement Errors..??

  • June 15, 2005 at 11:44 am

    #67108

    Hello everyone,

    I am having a strange issue with some "not in" Where statements..

    I have one staging table called C_Funds..

    I also have a product table called Product..

    On C_Funds there is an Id Called FundId and that maps to ActualId on product.

    I am trying to find all the id's in C_Funds that do not exist in product

    and viceaversa...

    Here are my statements and then my issue:

    Select FundId From C_Funds Where ActualId Not in(Select ActualId from Product)-- Find New Funds

    Select ActualId From Product Where ActualId not in(Select FundId from C_Funds)-- Check for funds to reclassify

    Now the stats..:

    C_Funds total rows = 500

    Product Total Rows = 403

    The second select statement brings a return of 50 rows in Product not in C_Fund, This is proven by checking the fundid number one by one.

    HERE IS THE ISSUE:

    The first select Statement returns no rows...

    Now I deleted a row in the product table of a matching id to check.. and it still returns no rows..

    Am I missing something.. or can smoeone provide a hint where to look.

    thanks

    RJ

  • June 15, 2005 at 11:48 am

    #565808

    here....

    Select FundId From C_Funds Where ActualId Not in(Select ActualId from Product)-- Find New Funds

     

    Select FundId From C_Funds Where FundId Not in(Select ActualId from Product)-- Find New Funds

     

     

    Kindest Regards,

    Vasc

  • June 15, 2005 at 11:58 am

    #565810

    Not exists would be faster than not in for this task.

  • June 15, 2005 at 12:00 pm

    #565811

    Vasc,

    teh two select statements should be different one finds FUNDID in C_Funds not in Actualid in Product and the other finds actualid in Product not in fundid in C_FUNDS

     

    You passed me two of the same.. isse is second statement above works.. first Statement doesn't

     

    Thanks

    RJ

  • June 15, 2005 at 12:01 pm

    #565813

    Not exists also returns zero rows when I know there should eb at least one..

     

     

  • June 15, 2005 at 12:03 pm

    #565814

    The first statement was with the mistake the sec is correct ActualId is in fact FundId that was your mistake

    Kindest Regards,

    Vasc

  • June 15, 2005 at 12:04 pm

    #565817

    Sorry Vasc, Didn't see the typo in my statements.. however even the change to FundId brings a zero return of rows that should at least be 1 but my guess based on counts more like 77

     

    RJ

  • June 15, 2005 at 12:11 pm

    #565821

    Hmm that is really strange now...

    Just try:

    Select ActualId, FundID

    From Product FULL OUTER JOIN

    C_Funds ON ActualID=FundID

    --Where ActualId IS NULL --return new FundID

    --Where FundId IS NULL --return new ActualID

     

    with this Query you can check all rows where should they be

    Kindest Regards,

    Vasc

  • June 15, 2005 at 12:25 pm

    #565826

    Vasc,

    Those select Statements work correctly and I get 148 for the first and 50 i get for second..

    The second matches the second statement I wrote.. but why doesn't the first "NOT IN" work correctly.>???

     

    Thanks for your help I can use what you gave me to create my recordset..

    RJ

     

  • June 15, 2005 at 12:55 pm

    #565840

    Should work ...

    PS. you have a column ActualId in C_Funds?

    Kindest Regards,

    Vasc

  • June 15, 2005 at 12:58 pm

    #565842

    no C_funds has FUNDID which is equal to Product ActualId

  • June 15, 2005 at 1:06 pm

    #565845

    I don't know why this

    Select FundId From C_Funds

    Where FundId NOT IN (Select ActualId from Product)-- Find New Funds

    is not working ...It should

    Kindest Regards,

    Vasc

  • June 15, 2005 at 1:15 pm

    #565853

    Can you show the data for those columns in each table?

    And how ActualId is related to FundId??

  • June 15, 2005 at 1:20 pm

    #565858

    They are both a string id of equal length.. both (varchar 64)

     

    Sample from C_Funds:

    Admin        2005-06-15 18:17:45.000 NULL 2005-06-15 18:17:45.000 123501863

    Admin        2005-06-15 18:17:45.000 NULL 2005-06-15 18:17:45.000 123501866

    Admin        2005-06-15 18:17:45.000 NULL 2005-06-15 18:17:45.000 123501869

    Admin        2005-06-15 18:17:45.000 NULL 2005-06-15 18:17:45.000 123501872

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123501881

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123501884

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123501887

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123501890

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123502307

    Admin        2005-06-15 18:17:46.000 NULL 2005-06-15 18:17:46.000 123502331

     

    Sample from Products

    123460054 PE 1.0 2005-03-01 21:46:25.000

    123460055 PE 1.0 2005-03-01 21:46:25.000

    123460056 PE 1.0 2005-03-01 21:46:25.000

    123460061 RE 1.0 2005-03-01 21:46:26.000

    123460075 FOHF 1.0 2005-03-01 21:46:26.000

    123460078 PE 1.0 2005-03-01 21:46:26.000

    123460081 SAI 1.0 2005-03-01 21:46:26.000

    123496404 RE 1.0 2005-03-01 21:46:26.000

    123496428 MF 1.0 2005-03-01 21:46:26.000

    123496431 MF 1.0 2005-03-01 21:46:26.000

    123496440 SAI 1.0 2005-03-01 21:46:26.000

    123496449 FOHF 1.0 2005-03-01 21:46:27.000

    123496452 MF 1.0 2005-03-01 21:46:27.000

     

  • June 15, 2005 at 1:35 pm

    #565868

    Any of the id columns allow nulls?

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply