SQL Join keeps failing

58975 pts.
Tags:
SQL
SQL join
Hello everyone, This question comes from one of our power users: ToddN2000. I'm trying to join 2 tables and it keeps failing. I did not create the tables and I cannot change them. One has the order defined as NUMERIC(8) the other has the order defined VARCHAR(15). I have tried using CAST, CONVERT, ISNUMERIC and combinations of them with no luck. I'm trying to join a summarized version by division, warehouse, product class, customer to the master. Running the SQL below gives the error: Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to numeric.
select 
SADIV,SAWH,SAPCLS,SABTKY,SAORDER,SASUFX, 
sum(SAEXT$) as csext$sum,
sum(SANET$) as csnet$sum, 
sum(SAFEXT$) as csfext$sum, 
sum(SAFNET$) as csfnet$sum,
CAST(SAORDER as varchar(15)) as cssaorder
      from [SAWh].[SalesManager].[Cognos] as cs
      left join [Corp].[FactoryOrder].[SalesReport] as sr 
      on SAORDER = sr.FactoryOrderNumber
      group by SADIV,SAWH,SAPCLS,SABTKY,SAORDER,SASUFX

Answer Wiki

Thanks. We'll let you know when a new response is added.
1: You may need the Cast on the Join on… (and everywhere SAORDER used)
2: If there is bad data (not numbers) it may be better to do the opposite. Convert Num to Char.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    Thanks for getting to post out there Mike.
    I'm also open to other solutions for the problem above. One table has millions of order detail records. I'm looking to summarize the data and then using the order number VARCHAR(15) and join to an order header where the order number is defined as NUMERIC(8). Bad design, I know, but trying to link 2 different set of data from multiple sources.
    87,900 pointsBadges:
    report
  • CharlieBrowne
    Todd,
    Are you sure all the records contain data that can be converted to numeric? From the message, the 1st thing I think of is alphabetic characters cannot be done. If you can identify those, you should be able to exclude them on the join. Either in your SQL statement or by creating a LF that excludes them. 
    A simple test would be to create a LF that includes only a few order numbers and then do your join of those. If that works, you just need to identify the bad records.
    62,340 pointsBadges:
    report
  • ToddN2000
    Thanks for the tips gentlemen. Seeing as both tables are on a SQL server application I tried adding the Cast on the join and IT WORKED !! Thanks.
     This worked
    select 
    SADIV,SAWH,SAPCLS,SABTKY,SAORDER,SASUFX, 
    sum(SAEXT$) as csext$sum,
    sum(SANET$) as csnet$sum, 
    sum(SAFEXT$) as csfext$sum, 
    sum(SAFNET$) as csfnet$sum,
    CAST(SAORDER as varchar(15)) as cssaorder
          from [SAWh].[SalesManager].[Cognos] as cs
          left join [Corp].[FactoryOrder].[SalesReport] as sr 
          on CAST(SAORDER as varchar(15)) = sr.FactoryOrderNumber
          group by SADIV,SAWH,SAPCLS,SABTKY,SAORDER,SASUFX
    87,900 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: