SQL Syntax

Posted on 2012-08-28
Last Modified: 2012-08-28
select * from #table1 where len(warehouse_id) = 0

UPDATE      #table1
SET warehouse_id =
         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?
Question by:MoreThanDoubled
    LVL 65

    Expert Comment

    by: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, '')))
    LVL 13

    Accepted Solution

    Are you sure about doing two inner joins on different tables? You know that's gonna give you "only" the rows that match the id in "both" tables.

    If you want to update different sets of rows, you can do left outer joins and handle the nulls like jimhorn says, or you can do two updates.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now