How to replace 'OR' as to get right result

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to replace 'OR' as to get right result

Author

Topic

ankur_gurha
Starting Member

20 Posts

Posted - 2006-07-31 : 10:08:21

Hi Guys .. I actually want to update the column with the results when either of the nlc.id matches up in the result set.. but the problem one of the values in my 'AND ' statement for the nlc.id matches up it wont go to the next nlc.id and pick that value up as well becoz its an OR statement between them, can ony one let me know how can i go about this problem.. Any comments would be much appreciated... I really look forward for ur help..

The query is just below..

UPDATE t2 SET InsuranceRevenue = nf.InsuranceRevenue FROM #Temp1 t2
INNER JOIN
(
SELECT si.tranref,
nlc.id,
SUM(isnull(sil.amount,0) ) AS 'InsuranceRevenue'

FROM Job As j
LEFT OUTER JOIN SalesInvoice si ON si.job = j.id
LEFT OUTER JOIN SalesInvoiceLines sil ON sil.trannr = si.id
LEFT OUTER JOIN NominalLinkCode nlc ON nlc.id = sil.revenuecode
LEFT OUTER JOIN Modification mod ON mod.id = j.inquiryLogged

WHERE mod.[date] BETWEEN @FromDate AND @ToDate
AND
(
nlc.id = '-4029651930510448494'
OR nlc.id = '9205736881087145474'
OR nlc.id = '-304994718767961087'
OR nlc.id = '5888048505145408372'
OR nlc.id = '5763359488507192556'
OR nlc.id = '-8789134301885392205'
OR nlc.id = '-5192842848853456615'
)

GROUP BY nlc.name , si.tranref , nlc.id

) AS nf ON nf.tranref = t2.tranref

Thanks for the help in advance..

Cheers!!
A..

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-31 : 10:37:57

maybe this?


AND nlc.id in ('-4029651930510448494'
,'9205736881087145474'
,'-304994718767961087'
,'5888048505145408372'
,'5763359488507192556'
,'-8789134301885392205' 
,'-5192842848853456615'
           )

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

ankur_gurha
Starting Member

20 Posts

Posted - 2006-07-31 : 11:36:45

No.. 'IN' doesnt works as well it brings back the same results as with the 'OR' looking for some suggestions..

Thanks,
A..

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-31 : 12:19:24

"it wont go to the next nlc.id and pick that value up as well becoz its an OR statement "
Not sure what you mean here.

Follow the first link in my signature and repost your question.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources