Post reply

Difficulties with the WHERE clause

  • January 10, 2018 at 1:17 pm

    #342294

    I am interacting with an ERP database. The field, Ord_No in all the tables in the database is defined as a Char(8). The ERP program pads the ORD_NO with spaces.
    E.g. Ord_no = '11' is stored as '      11'. When the program passes the Ord_No as 11, I have several choices.

    1. Declare @OrdNo as char(8) = '      11' ; Select Cus_No From OEOrdhdr_SQL where Ord_No = @OrdNo
    2. Declare @OrdNo as char(8) = '11'  ;Select Cus_No From OEOrdhdr_SQL Where Ltrim(Rtrim(Ord_No) = @OrdNo
    Is there a more efficient way to structure the query? If not can I assume that option 1 is the better of these two?

    This may seem like a trivial question. What I haven't shared is that this is a new structure for Ord_no . This happened after an upgrade. The production Database has to interact with legacy Databases which store Ord_no as '00000011' (The older structure, padding with 0's). Ultimately I am want to understand all the options so that I can construct better Joins between the tables in residing different database with different structures for Ord_no.

    Thanks,
    pat

  • January 10, 2018 at 1:34 pm

    #1975381

    mpdillon - Wednesday, January 10, 2018 1:17 PM

    I am interacting with an ERP database. The field, Ord_No in all the tables in the database is defined as a Char(8). The ERP program pads the ORD_NO with spaces.
    E.g. Ord_no = '11' is stored as '      11'. When the program passes the Ord_No as 11, I have several choices.

    1. Declare @OrdNo as char(8) = '      11' ; Select Cus_No From OEOrdhdr_SQL where Ord_No = @OrdNo
    2. Declare @OrdNo as char(8) = '11'  ;Select Cus_No From OEOrdhdr_SQL Where Ltrim(Rtrim(Ord_No) = @OrdNo
    Is there a more efficient way to structure the query? If not can I assume that option 1 is the better of these two?

    This may seem like a trivial question. What I haven't shared is that this is a new structure for Ord_no . This happened after an upgrade. The production Database has to interact with legacy Databases which store Ord_no as '00000011' (The older structure, padding with 0's). Ultimately I am want to understand all the options so that I can construct better Joins between the tables in residing different database with different structures for Ord_no.

    Thanks,
    pat

    Your first option (although you can't see the spaces due to forum formatting) is the best. That way, you'll be able to properly use the indexes available in the tables.
    Remember that you should rarely affect the parameters, but you should never (unless strictly necessary due to bad design) alter the values in the columns for a WHERE or JOIN clause.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 10, 2018 at 1:38 pm

    #1975383

    mpdillon - Wednesday, January 10, 2018 1:17 PM

    I am interacting with an ERP database. The field, Ord_No in all the tables in the database is defined as a Char(8). The ERP program pads the ORD_NO with spaces.
    E.g. Ord_no = '11' is stored as '      11'. When the program passes the Ord_No as 11, I have several choices.

    1. Declare @OrdNo as char(8) = '      11' ; Select Cus_No From OEOrdhdr_SQL where Ord_No = @OrdNo
    2. Declare @OrdNo as char(8) = '11'  ;Select Cus_No From OEOrdhdr_SQL Where Ltrim(Rtrim(Ord_No) = @OrdNo
    Is there a more efficient way to structure the query? If not can I assume that option 1 is the better of these two?

    This may seem like a trivial question. What I haven't shared is that this is a new structure for Ord_no . This happened after an upgrade. The production Database has to interact with legacy Databases which store Ord_no as '00000011' (The older structure, padding with 0's). Ultimately I am want to understand all the options so that I can construct better Joins between the tables in residing different database with different structures for Ord_no.

    Thanks,
    pat

    You want to avoid functions on fields in filters, because they are not SARGable.  In other words, you want to avoid #2, because of the LTRIM(RTRIM(Ord_No)).

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • January 10, 2018 at 2:14 pm

    #1975395

    Thank you for confirming what I suspected.
    Pat

  • January 10, 2018 at 2:47 pm

    #1975398

    Or change OrdNo to int and avoid all that ridiculous mess!

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply