CODEWITHSUNDEEP
sql-serversql-server-2014
SchmitzIT
SchmitzIT

Reputation: 9552

Query evaluation path

Update: Had the order of the two queries wrong. First one works, the second one does not.

I'm experiencing some odd SQL Server behavior that has me stumped, and I'm hoping someone can help me figure out what's going.

My query needs to pull data from a number of tables, including the products table. The schema we work with is pretty rigid, and recently we added some new products that in their name contain a "recurrence" value (i.e. we bill the customer every 3, 6, or 12 months, and that number is only available in text, in the product name. This bites, but that's how it is).

I add a WHERE clause to filter out the 3 specific products, and then run the following piece of code to obtain the quantity recurrence-factor:

CAST(RTRIM(SUBSTRING(e.NAME, CHARINDEX('-', e.NAME) +2 , 2)) as int)

Nothing overly fancy, and with the WHERE clause specifically filtering only those 3 products, we ought to be good. I then cast the retrieved value to an integer to be used in a calculation.

However, seemingly random, the query will complain about two letter combinations not being possible to convert to an int value.

It was my assumption that by using the WHERE clause to remove unwanted products, other products would never be evaluated to begin with. If the above code is the only thing in the query, the query runs. If we add e.NAME as the only other field, I get an error:

Conversion failed when converting the nvarchar value 'ni' to data type int.

I attempted to also filter out unwanted products by hard-coding the products into the JOIN (which shouldn't really matter), but it did not help.

Execution plans and updating statistics on the table did not reveal anything worthwhile.

This throws the error:

SELECT
    CAST(RTRIM(SUBSTRING(e.NAME, CHARINDEX('-', e.NAME) +2 , 2)) AS int) AS BillingFrequency
FROM 
    orders a
        JOIN order_items b ON a.ORDER_ID = b.ORDER_ID
        JOIN order_item_options c ON b.ORDER_ITEM_ID = c.ORDER_ITEM_ID
        JOIN prices d ON c.PRICE_ID = d.PRICE_ID AND d.TO_OPTION_ID IN (189, 190, 191)
        JOIN product_options_vw e ON d.TO_OPTION_ID = e.OPTION_ID AND e.OPTION_ID IN (189, 190, 191)
WHERE
    e.OPTION_ID IN (189, 190, 191)

This works:

SELECT
     e.NAME
    , CAST(RTRIM(SUBSTRING(e.NAME, CHARINDEX('-', e.NAME) +2 , 2)) AS int) AS BillingFrequency
FROM 
    orders a
        JOIN order_items b ON a.ORDER_ID = b.ORDER_ID
        JOIN order_item_options c ON b.ORDER_ITEM_ID = c.ORDER_ITEM_ID
        JOIN prices d ON c.PRICE_ID = d.PRICE_ID AND d.TO_OPTION_ID IN (189, 190, 191)
        JOIN product_options_vw e ON d.TO_OPTION_ID = e.OPTION_ID AND e.OPTION_ID IN (189, 190, 191)
WHERE
    e.OPTION_ID IN (189, 190, 191)

Upvotes: 1

Views: 82

Answers (1)

TheGameiswar
TheGameiswar

Reputation: 28900

I see both queries are same with exception of adding one more column in second query and it is strange that the second one works for you which shouldn't be.i recommend using try cast and try below steps

Try cast throws null,if conversion fails and you can see

Try_CAST(RTRIM(SUBSTRING(e.NAME, CHARINDEX('-', e.NAME) +2 , 2)) AS int)

You can see all columns with null which might be causing the issue.further error message clearly says ..

Conversion failed when converting the nvarchar value 'ni' to data type int.

So you can try using only below part and see which one gives you an output of ni

RTRIM(SUBSTRING(e.NAME, CHARINDEX('-', e.NAME) +2 , 2))

Upvotes: 2

Related Questions