Problem with a local variable

[lflecher]

Hello everybody,

Here I have two tables. Let's call them A and B.

A contains the folowing data: (address of persons)

PostCode Street Number CdeTour 
-------- ------ ------ -------
31000     Well.   0025    NULL
31000     Well.   0050    NULL
31000     Wash.   0010    NULL

B contains the folowing data:

PostCode Street FirstEven LastEven FirstOdd LastOdd CdeTour
-------- ------ --------- -------- -------- ------- -------
31000     Well.      0002     0040     0043    0073     100
31000     Well.      0042     0084     0001    0041     200
31000     Wash.      0002     0050     0001    0049     100

* My problem is that I want to update the field A.CdeTour depending on the data present in B.

Let's take the first line from the table A: the number of the street 'well.' is '25', and '25' is odd and between '1' and '41' which meens that I should update my field A.CdeTour should be set to '100' (B.CdeTour).
Let's take another example, so in the second line: the street is still 'well.' but here the number is '50'. '50' is even and between '42' and '84' so the field A.CdeTour should be set to '200' (B.CdeTour).

Here I wrote a query, which doesn't work properly:

DECLARE @Num VARCHAR(4)
UPDATE A
SET @Num = Number, CodeTournee= 
	CASE WHEN @Num % 2 = 0 THEN(
		-- even number of street
		SELECT CdeTour
		FROM B
		WHERE @Num BETWEEN FirstEven AND LastEven 
                AND A.PostCode = B.PostCode 
                AND A.Street = B.Street
	)ELSE(
		-- odd number of street
		SELECT CdeTour
		FROM B
		WHERE @Num BETWEEN FirstOdd AND LastOdd
                AND A.PostCode = B.PostCode
                AND A.Street = B.Street
	)END
FROM A, B

The query runs but the problem is that it doesn't update the field, because it doesn't interpret @num by its value.

In fact, if instead of @num, I hard-code a value it works
... WHERE '0025' BETWEEN FirstOdd AND LastOdd ...

Can someone help me???? I would be very pleased, if someones could give me another way of doing it or a trick in order to avoid this problem.

Thanks in advance.

 

[arrowhouse]

I think the problem is that the @num var is not defined at the time the SQL is excecuted - it looks like your expect the @num to be set = number before the update conditions are enforced - but this is all happening at the same time. Your value of @num at that moment is null.

You could try something along the lines of :
UPDATE A
SET CodeTournee= number
FROM A INNER JOIN B ON
A.PostCode = B.PostCode
AND A.Street = B.Street
AND ((number % 2 = 0 AND number BETWEEN FirstEven AND LastEven) OR
(number % 2 = 1 AND number BETWEEN FirstOdd AND LastOdd))

(I haven't checked it fully but this should point you in the right direction)
or
if you are always updating a single row you could define the @num var in a step before the update.

Good luck.

 

[lflecher]

It was what I thought at the beginning but in fact it doesn't come from that, because when I write:

...
    CASE WHEN @Num % 2 = 0 THEN(
        -- even number of street
        @Num
    )ELSE(
...

I get the folowing results (so @Num has been assigned):

PostCode Street Number CdeTour 
-------- ------ ------ -------
31000     Well.   0025    0025
31000     Well.   0050    0050
31000     Wash.   0010    0010

it's weird isn't it?? Anyway thanks a lot for your time.

 

[arrowhouse]

No because your case statement doesn't cover the case of
% 2 being null.

In the example you give the value of null falls into the else statement & therefore gets set to the value of @num.

You need to add a further check to be certain that the undefined variable is not the problem:


DECLARE @Num VARCHAR(4)
UPDATE A
SET @Num = Number, CodeTournee=
CASE WHEN @Num % 2 = 0 THEN(
-- even number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstEven AND LastEven
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)

CASE WHEN @Num % 2 = 0 THEN (
-- odd number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstOdd AND LastOdd
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)
ELSE(NULL)
END
FROM A, B

I think if you run this you'll get nulls. Willing to eat humble pie if not

 

[arrowhouse]

Humble pie already re typo - you need to change the second case's % 2 = 0 to % 2 = 1 so you get the odd numbers accounted for:


DECLARE @Num VARCHAR(4)
UPDATE A
SET @Num = Number, CodeTournee=
CASE WHEN @Num % 2 = 0 THEN(
-- even number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstEven AND LastEven
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)

CASE WHEN @Num % 2 = 1 THEN (
-- odd number of street
SELECT CdeTour
FROM B
WHERE @Num BETWEEN FirstOdd AND LastOdd
AND A.PostCode = B.PostCode
AND A.Street = B.Street
)
ELSE(NULL)
END
FROM A, B