Subqueries, what am I doing wrong? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Subqueries, what am I doing wrong?

Author

Topic

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 06:08:16

The problem that faces me relates to the statement below:

select (isnull(c.FIRSTNAMES,'')
+ ' '
+ isnull(c.[NAME],'')) ClientName,
c.REFER ClientRef,
p.CONT_NUM PolicyRef,
p.POL_NUM PolicyID,
cs.[Desc] ,
p.STATUS

from S_POLMAI p
inner join S_CLIENT c
on p.CLIENT_NUM = c.CLIENT_NUM
inner join Hydra.dbo.def_PolicyStatus cs
on p.STATUS = cs.[ID]

where p.PLAN_CODE = 'DGT'
and p.POL_NUM not in (select [id] from #temp1) -- Line in dispute
and p.POL_NUM not in (select distinct PolicyID
from TrackItems
where [Description] like '%blar blar blar%'
or [Description] like '%blar blar blar%')
and p.STATUS in (19,20)
and p.POL_NUM = ***** -- Test Case

order by c.[NAME]

With the 'Line in dispute' commented out the statement returns a single row relating to the POL_NUM entered as a test case. The table #temp1 contains a list of POL_NUM's excluding the one I have used as my test case. POL_NUM and [id] (returned by #temp1) are of the same data type. Running the full statement returns no rows. Changing the 'Line in dispute' to 'in' rather than 'not in', also returns no rows. Replacing the select statement with a comma delimited list of numbers excluding the test case POL_NUM, works as expected.

satishdg
Starting Member

10 Posts

Posted - 2005-04-06 : 07:06:01

Hi

and p.POL_NUM not in (select POL_NUM from #temp1) -- Line in dispute

Try this

Satish

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 07:11:13

#temp1 only contains one column called [id]. I populated the table with a list of POL_NUM's fitting a certian criteria.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-04-06 : 07:23:34

just to be sure:
have you commented the test case line when you used the line in dispute?
it's little mistakes like that that can make life miserable

Go with the flow & have fun! Else fight the flow

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 07:29:05

No, but I have confirmed that the POL_NUM I've used as a test case is not returned by #temp1. I really hope that is something silly, but I can't see it.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-06 : 09:05:17

quote:
Changing the 'Line in dispute' to 'in' rather than 'not in', also returns no rows.

What that says to me is that you get no results regardless of whether your value is IN or NOT IN the temp table. So the temp table line, it would appear, is not actually affecting your results at all, as one condition or the other must be true.

You need to look elsewhere in the code. Are you sure that you are not accidentally commenting out other sections along with that line?

-------
Moo. :)

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 09:06:00

I've managed to reduce the problem to this statement.

select *
from S_POLMAI
where POL_NUM not in (select distinct [id] from #temp1)

There are over 2000 distinct POL_NUM's in S_POLMAI, exactly 180 distinct [id]'s in #Temp1

When I run the query above, 0 rows are returned. When take 'not' out of the where clause 179 rows are returned.

mr_mist
Grunnio

1870 Posts

Posted - 2005-04-06 : 09:10:17

Are any of your IDs NULL?

-------
Moo. :)

kimberr
Starting Member

9 Posts

Posted - 2005-04-06 : 09:21:52

As made my last post I had the same idea. Yes, there was one null in #Temp1 that stopped the query from running. Thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources