Two things... Your IN statements and you UNION statement s probably what's killing you.

Try EXISTS instead of IN, and try using UNION ALL of there can be no duplicates between...

Oh man, those memories from Math 101 lessons...

😀

A workaround can be to concatenate the two values into a binary string or varchar, and then later split the values in the SELECT statement.

He only has to read the replies here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=109222

Which service pack, if any, are you using on the SQL Server?

DECLARE@data VARCHAR(4)

SET@data = '0061'

SELECTCAST(@Data AS INT) / 1000.0

You're welcome.

I forgot to mention

BETWEEN

NOT BETWEEN

as two more potential "triangular join" triggers.

There is a real difference when running a concatenating code in SQL Server 2000 and SQL Server 2005.

SQL Server 2005 displays the "erratic" behaviour you describe such as only returning...

See code above from Jack Corbett.

Is does everything you need and not using dynamic sql.

You find that WHILE with IF easier than this?

DECLARE @Loop INT

SET @Loop = 1

WHILE @Loop <...

Learn you JOINs

Select L.BdKey

From ArLine L

Join ArInvoice V On

L.ArVKey = V.ArVKey

And V.ArVType = 9

Where L.BdKey Is Null

Easy.

Look at the

1) JOIN arguments

2) WHERE arguments

3) Correlated subqueries arguments

Equal character "=" only are most often OK, but the character combinations to look for are mostly

>

>=

<

<=

<> or !=

Haven't you...

DECLARE@Loop INT,

@Value VARCHAR(3)

SELECT@Loop = 1,

@Value = NumberValue

FROMNumber

WHERENumberID = 1

WHILE @Loop < 8

BEGIN

PRINTREPLICATE(@Value, 4 - ABS(4 - @Loop))

SET@Loop = @Loop + 1

END

UNIONs? IFs?

DECLARE@Loop INT

SET@Loop = 1

WHILE @Loop < 8

BEGIN

PRINTREPLICATE('1', 4 - ABS(4 - @Loop))

SET@Loop = @Loop + 1

END

SELECTREPLICATE('1', 4 - ABS(4 - Number))

FROMmaster..spt_values

WHEREType = 'P'

AND Number BETWEEN 1 AND 7

No need for calendar table.

This question is also asked and answered here

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=108802