asked on

SQL Syntax

select * from #table1 where len(warehouse_id) = 0

UPDATE      #table1
SET warehouse_id =
CASE
       WHEN LEN(RTRIM(x.warehouse_id)) > 0 then x.warehouse_id
       WHEN LEN(RTRIM(x.whse_id)) > 0 then x.whse_id
       WHEN LEN(RTRIM(n.warehouse_id)) > 0 then n.warehouse_id
       ELSE '' END
FROM      #table1 WITH (NOLOCK)
INNER JOIN (SELECT distinct warehouse_id, whse_id, order_id
FROM order WITH (NOLOCK)
       WHERE (len(warehouse_id)> 0 OR len(whse_id) > 0)) x
       ON #table1.order_id = x.order_id
       INNER JOIN (SELECT distinct warehouse_id, order_id
              FROM netwrk WITH (NOLOCK)
              WHERE len(warehouse_id)> 0) n
       ON n.order_id = #table1.order_id
WHERE      len(RTRIM(#table1.warehouse_id)) = 0

i have a temp table (#table1), that i am trying to update the warehouse id based on a lookup in two other tables. for some reason i am not getting the expected results. What am i doing wrong?

Jim Horn

How are you handling NULL values?

SELECT LEN(RTRIM(NULL)) returns a NULL, which is not evaluate to > 0.

For all of these comparisons you'll need to do something like...

LEN(RTRIM(COALESCE(whatever, '')))

ASKER CERTIFIED SOLUTION

Member_2_861731

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial