Solved

MSSQL Server 2008

Posted on 2009-07-14
6
171 Views
Last Modified: 2012-05-07
I have this select statement that works find in SQL 2000.  We recently migrated to sql server 2008 and now it doesn't work correctly, I have narrowed it down to the line:

where D.OrgID in (select distinct(I.OrgID) from Strat_Customers.dbo.Customer_CareInvoice I)

Does something need to be changed to work in 2008?  The entire select is below:
select D.DebtorID, DS.ServiceID, cast(Payments as money) - IsNull(LastPayment,0) as Payment, cast(Adjustments as money) - IsNull(LastAdjustment,0) as Adjustment, 'File Balance <> Service Balance' as Reason, cast(Payments as money), cast(Adjustments as money), LastPayment, LastAdjustment
from Strat_Customers.dbo.Customer_CareInvoice IL
inner join DebtorService DS on IL.InvoiceNumber = DS.InvoiceNo
inner join  DebtorInvoice DI  on DS.ServiceID = DI.ServiceID
inner join Debtor D on DS.DebtorID = D.DebtorID 
where D.OrgID in (select distinct(I.OrgID) from Strat_Customers.dbo.Customer_CareInvoice I)  --Change for new customer
and cast(IL.InvoiceBalance as money) <> BalanceDue
and D.StatusID in (1,2,6,8,9,10,12,20,19) --New, Suspended, Bad Address, Letters Complete, Letter Series, Payment Plan, Payment Late, Payment Breach, Invoice

Open in new window

0
Comment
Question by:N R
  • 3
  • 3
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24850316

you don't need distinct, actually (it won't change the results, it only might impact performance)
you have to ensure that i.OrgID is not null
where D.OrgID in (select I.OrgID from Strat_Customers.dbo.Customer_CareInvoice I where i.OrgID IS NOT NULL)

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24850321
you might also use this
where exists ( select null from Strat_Customers.dbo.Customer_CareInvoice I where i.OrgID = D.OrgID )

Open in new window

0
 
LVL 11

Author Comment

by:N R
ID: 24850667
No there are never null orgid's.  I just changed it to list out the possible orgid's for that company like this:

where D.OrgID in (2534,2535,2536,2538)

Now it returns the expected results
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24850693
what are the data types of the 2 OrgID fields?
0
 
LVL 11

Author Comment

by:N R
ID: 24850713
They are Int
0
 
LVL 11

Author Comment

by:N R
ID: 24850849
Strange that it doesn't work, but no big deal I can just update my SP's to have the orgid listed in there.  Thanks.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Server Trigger 8 35
Star schema daily updates 2 35
disk usage reporting tools 27 52
ODBC settings not showng in FileMaker External Data Sources 6 23
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question