Solved

MSSQL Server 2008

Posted on 2009-07-14
6
175 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

752 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