Freak query ...

• virgilrucsandescu

  SSCrazy

  Points: 2457

  More actions

  September 10, 2008 at 2:32 pm

  #192574

  I have these queries:

  1a - select * from import_cc_requests where reference not in (select reference from import_cc_payments )

  or

  1b - select * from import_cc_requests where rtrim(reference) not in (select rtrim(reference) from import_cc_payments )

  - both these are returning 0 rows

  2 - select * from import_cc_requests where reference='012408-01'

  - this returns 1 row

  3 - select * from import_cc_payments where reference='012408-01'

  - this returns 0 rows

  Reference field is varchar(255) in both tables, same collation (database default).

  shouldn't 1a / 1b queries return at least this 1 row????

• Jeffrey Williams

  SSC Guru

  Points: 90350

  More actions

  September 10, 2008 at 3:31 pm

  #869997

  First, I don't see any difference between the two queries. I am assuming you meant to modify the second query to something else.

  As for your problem - can your sub-query return any null values? If so, then that will cause the problem you are seeing. To correct it, modify the sub-query to exclude nulls.

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• Lowell

  SSC Guru

  Points: 323546

  More actions

  September 10, 2008 at 6:30 pm

  #870058

  two things i see...can reference be null in the payments? also i prefer to use a left join syntax instead..

  1a - select * from import_cc_requests where reference not in (select reference from import_cc_payments WHERE REFERENCE IS NOT NULL)

  or

  select import_cc_requests.*

  from import_cc_requests

  LEFT OUTER JOIN import_cc_payments

  ON import_cc_requests .REFERENCE = import_cc_payments.REFERENCE

  WHERE import_cc_payments.REFERENCE IS NULL

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jeff Moden

  SSC Guru

  Points: 1004635

  More actions

  September 10, 2008 at 8:22 pm

  #870086

  Lowell (9/10/2008)

  ---

  two also i prefer to use a left join syntax instead..

  Check the execution plans of both on a set of big tables sometime...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• virgilrucsandescu

  SSCrazy

  Points: 2457

  More actions

  September 10, 2008 at 9:17 pm

  #870094

  Sorry guys, I updated the 1b query, this is supposed to be "rtrim(reference)" instead of just "reference".

  And reference doesn't have any NULL value in the 2 above mentioned tables ...

  It should be something too simple - data comes from two Excel worksheets ....

  I just want to list the rows in the requests table that don't have any payment associated, and there are around ~ 200 of them and both the first two queries returns 0 rows .... The value is just a random value picked from the ..._requests table ...

  This is really mind-boggling - something like this never happened to me before...

• Jeff Moden

  SSC Guru

  Points: 1004635

  More actions

  September 10, 2008 at 10:38 pm

  #870107

  virgilrucsandescu (9/10/2008)

  ---

  I have these queries:

  1a - select * from import_cc_requests where reference not in (select reference from import_cc_payments )

  or

  1b - select * from import_cc_requests where rtrim(reference) not in (select rtrim(reference) from import_cc_payments )

  - both these are returning 0 rows

  2 - select * from import_cc_requests where reference='012408-01'

  - this returns 1 row

  3 - select * from import_cc_payments where reference='012408-01'

  - this returns 0 rows

  Reference field is varchar(255) in both tables, same collation (database default).

  shouldn't 1a / 1b queries return at least this 1 row????

  Should work the way you've shown it. Have you paraphrased a real query or are these the actual real queries you've tried?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• karthik M

  One Orange Chip

  Points: 29139

  More actions

  September 11, 2008 at 2:41 am

  #870160

  what is the rowcount of import_cc_payments table ?

  karthik

• virgilrucsandescu

  SSCrazy

  Points: 2457

  More actions

  September 11, 2008 at 6:47 am

  #870268

  1 - select count(*) from import_cc_requests = 307 rows

  2 - select count(*) from import_cc_requests where reference in (select reference from import_cc_payments ) = 113 rows

  3 - select count(*) from import_cc_requests where reference not in (select reference from import_cc_payments ) = 0 rows ??????

  4 - select count(*) from import_cc_requests where reference is null - 0 rows

  5 - select count(*) from import_cc_requests where rtrim(reference)='' - 0 rows

  Shouldn't 1 = 2 + 3 ?????

• David Betteridge

  Hall of Fame

  Points: 3264

  More actions

  September 11, 2008 at 7:03 am

  #870276

  Does using table aliases make any difference? (I know it shouldn't...)

  select count(*) from import_cc_requests r where r.reference not in (select reference r2 from import_cc_payments r2 )

• Lowell

  SSC Guru

  Points: 323546

  More actions

  September 11, 2008 at 8:21 am

  #870336

  i can emulate the same error if the payments table has a row with nulls in the REFERENCE column.

  other than that, maybe it's a difference in coallation between the two tables?or maybe comparing nvarchar to a varchar or something?

  lets see the definition of the REFERENCE column for both tables...

  CREATE TABLE tmp1(REFERENCE NCHAR(20),OTHERSTUFF VARCHAR(10) )

  CREATE TABLE tmp2(REFERENCE NCHAR(30),OTHERSTUFF VARCHAR(10) )

  INSERT INTO tmp1 values (N'REF1','example')

  INSERT INTO tmp1 values (N'REF2','example')

  INSERT INTO tmp1 values (N'REF3','example')

  INSERT INTO tmp2 values (N'REF1','example')

  INSERT INTO tmp2 values (NULL,'example')

  select count(*) from tmp1 where reference not in (select reference from tmp2 )

  drop table tmp1

  drop table tmp2

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  September 11, 2008 at 4:57 pm

  #870702

  Try different set of queries:

  1 - select count(*) from import_cc_payments where reference IS NULL

  2 - select count(*) from import_cc_requests R where EXISTS (select 1 from import_cc_payments P WHERE P.reference = R.reference)

  3 - select count(*) from import_cc_requests R where NOT EXISTS (select 1 from import_cc_payments P WHERE P.reference = R.reference)

  _____________
  Code for TallyGenerator