2

So I have this query

select * 
FROM    
    [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets i                         WITH (NOLOCK)           

    INNER JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].customer_store cs                     WITH (NOLOCK) 
                                                                        ON i.Company_code = cs.Company_code
                                                                        AND i.Division_code = cs.Division_code
                                                                        AND i.Customer_Number = cs.Customer_Number
                                                                        AND i.ShipTo=cs.store_number
    JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].PickTickets_Packing ps1               WITH (NOLOCK) 
                                                                        ON i.Company_Code=ps1.Company_Code 
                                                                        AND i.Division_Code=ps1.Division_Code 
                                                                        AND i.PickTicket_Number=ps1.PickTicket_Number
    JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].Orders o                              WITH (NOLOCK) 
                                                                        ON i.Company_Code=o.Company_Code 
                                                                        AND i.Division_Code=o.Division_Code 
                                                                        AND i.Control_Number=o.Control_Number
    LEFT JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].Country cr                            WITH (NOLOCK)
                                                                        ON cs.country  =cr.country 

    LEFT JOIN 
    Packslip P                                                          ON I.COMPANY_CODE=P.CLIENTNAME

    LEFT JOIN 
    Moret_shipper sh                                                    ON I.COMPANY_CODE=sh.CLIENTNAME

    LEFT JOIN 
    CUSTOMER cu                                                         ON i.Customer_Number=cu.CUST_NUM

    LEFT JOIN 
    (       
        SELECT packslip FROM PICKHEAD (NOLOCK) 
        WHERE CLIENTNAME='03'
    ) td3                                                               ON I.PickTicket_number=td3.packslip

    JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].Picktickets_stage pst1                WITH (NOLOCK) 
                                                                        ON i.Company_Code=pst1.Company_Code 
                                                                        AND i.Division_Code=pst1.Division_Code 
                                                                        AND i.PickTicket_Number=pst1.PickTicket_Number 
                                                                        AND pst1.Stage_code='940READY'
    LEFT JOIN 
    (
        SELECT packslip FROM rf_log_all (NOLOCK) 
        WHERE action='DELETESO' AND clientname='03'
    ) td4                                                               ON I.PickTicket_number=cast(td4.packslip as numeric)

If I run it just like that, everything seems to work fine.

However as soon as I start adding the where clause, I start getting a weird conversion error.

Here is my where clause:

WHERE  
    i.company_code='03' 
    AND ps1.Packed_Status='E' 
    AND i.Warehouse_Code in ('DAYTO','SANFE','ALLLM')
    AND td3.packslip is null 
    AND td4.packslip is null

With that I get the Error converting data type nvarchar to numeric.

If I comment out the td3 and td4 checks, it works. 

WHERE  
    i.company_code='03' 
    AND ps1.Packed_Status='E' 
    AND i.Warehouse_Code in ('DAYTO','SANFE','ALLLM')
    --AND td3.packslip is null 
    --AND td4.packslip is null

However, it also works with those left in but the company code commented out. 

WHERE  
    --i.company_code='03' AND
     ps1.Packed_Status='E' 
    AND i.Warehouse_Code in ('DAYTO','SANFE','ALLLM')
    AND td3.packslip is null 
    AND td4.packslip is null

Does anyone know what might be causing this error. This is a query that we've been using for a while and this problem only started recently. If I knew what specific line is causing this error, I could try to fix it, but I'm not even sure of that.

Improve this question
6
  • 3
    Bad habits : Putting NOLOCK everywhere Commented Jul 8, 2019 at 18:07
  • @nathan What datatype is packslip? Adding your table schemas would be helpful. Commented Jul 8, 2019 at 18:11
  • 1
    Without sample data or DDL of your tables we can only guess here. What you say about your WHERE and which work (and which don't) doesn't really make sense though, in the case of packslip IS NULL the data type packslip is meaningless. I would more expect the issue to be with i.company_code='03'. Really we need DDL of all your tables, but best I can say is that somewhere you are comparing a numerical data type to a string datatype. (ON a different note, do you *really need every column back from your query here? Seems a bit excessive seeing as some will be duplicated by the JOIN.)* Commented Jul 8, 2019 at 18:12
  • It's difficult to say without knowing the datatypes. It might be that SQL Server is ignoring some of the joins until additional Where-clause is added Commented Jul 8, 2019 at 18:12
  • @Larnu I don't need every column, but the problem persists even when specifying only the columns I need. Packslip type is NVARCHAR. I also agree the company code could be the issue. Commented Jul 8, 2019 at 18:14

1 Answer 1

Reset to default
1

Obviously, nothing in the where clause is causing this error, because all the comparisons are string comparisons (or type-safe such as comparing to NULL).

So, what is happening is that some of the JOIN conditions are causing problems. That means that you are mixing types. Your question doesn't have enough information to determine which ones.

Why does this occur with some WHERE conditions but not others? Because SQL Server rearranges the query plan to be optimal for the query you have written. In different query plans, the offensive comparison might occur after filtering (in which case no error occurs) or before filtering (in which case an error occurs).

Reading the query plan probably will not help you. You really want to find the comparison that is mixing types -- and that might take a bit of work and digging through the table structures.

If I had to guess, I would speculate that the offending line of code is:

ON I.PickTicket_number = cast(td4.packslip as numeric)

And that packslip cannot always be converted to a numeric value. This is easily fixed by using try_cast():

ON I.PickTicket_number = try_cast(td4.packslip as numeric)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Considering the OP has just commented that packslip is an nvarchar, you're very likely on the right course here.
I think that fixed the issue! Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.