NEED TO CONVERT AN INTERGER TO A STRING THEN USE AS A JOIN TO THE TABLE.

Question

Post reply

NEED TO CONVERT AN INTERGER TO A STRING THEN USE AS A JOIN TO THE TABLE.

sharonmtowler

Default port

Points: 1495
More actions
September 29, 2010 at 12:42 pm

#227218

I have a stored procedure where i am trying to join an order table to a customer table by cust #
(dbo.OCUSMA ON dbo.OORDDETL.ORACTD = dbo.OCUSMA.OKCUNO)- MY JOIN
in the order table the customer is an interger (BPW5_2_Staging.dbo.OORDDETL.ORACTD)
in the customer table the customer is a string (dbo.OCUSMA.OKCUNO)
i am using the following stored proc.
when i run the report it get a CONVERSION FAILED WHEN CONVERTING A VARCHAR TO INT.
how can i convert it then use it to join to? do i have to declare it first? IF SO HOW DO I DO THAT?
alter PROCEDURE dbo.CR_CREDIT_LIMIT_REVIEW
AS
SELECT
BPW5_2_Staging.dbo.OORDHDRA.DIVNA AS COMPANY,
BPW5_2_Staging.dbo.OORDHDRA.ORODAT AS ORDER_DATE,
BPW5_2_Staging.dbo.OORDHDRA.ORRTYP AS ORDER_TYPE,
BPW5_2_Staging.dbo.OORDHDRA.ORNUMA AS ORDER_NUMB,
BPW5_2_Staging.dbo.OORDDETL.ORSEQD AS LINE_#,
(BPW5_2_Staging.dbo.OORDDETL.ORSIZ1 +BPW5_2_Staging.dbo.OORDDETL.ORSIZ2 +BPW5_2_Staging.dbo.OORDDETL.ORSIZ3+BPW5_2_Staging.dbo.OORDDETL.ORSIZ4
+BPW5_2_Staging.dbo.OORDDETL.ORSIZ5+BPW5_2_Staging.dbo.OORDDETL.ORSIZ6+BPW5_2_Staging.dbo.OORDDETL.ORSIZ7+BPW5_2_Staging.dbo.OORDDETL.ORSIZ8
+BPW5_2_Staging.dbo.OORDDETL.ORSIZ9) AS UNITS,
BPW5_2_Staging.dbo.OORDDETL.orprce AS COST,
BPW5_2_Staging.dbo.OORDDETL.ORACTD AS ORACT,
BPW5_2_Staging.dbo.OORDDETL.ORSHPD AS START_DATE,
dbo.OCUSMA.OKCUNO,
dbo.OCUSMA.OKCUNM,
dbo.OCUSMA.OKCRLM,
dbo.OCUSMA.OKTDIN
FROM
dbo.OORDHDRA INNER JOIN
dbo.OORDDETL ON dbo.OORDHDRA.ORNUMA = dbo.OORDDETL.ORNUMD
AND dbo.OORDHDRA.CONOA = dbo.OORDDETL.CONOD AND
dbo.OORDHDRA.DIVNA = dbo.OORDDETL.DIVND
INNER JOIN
dbo.OCUSMA ON dbo.OORDDETL.ORACTD = dbo.OCUSMA.OKCUNO
WHERE BPW5_2_Staging.dbo.OORDDETL.ORSTAT not in('S','C','R')
ORDER BY BPW5_2_Staging.dbo.OORDDETL.ORACTD

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you've definitely got some data that cannot be converted to int, ie empty string or 'N/a' that someone data entered;

I'd suggest changing the join and the WHERE statement as follows to exclude thos offending rows:

INNER JOIN

dbo.OCUSMA ON dbo.OORDDETL.ORACTD = CONVERT(int,dbo.OCUSMA.OKCUNO)

WHERE dbo.OCUSMA.OKCUNO NOT LIKE '%[^0-9]%'

AND BPW5_2_Staging.dbo.OORDDETL.ORSTAT not in('S','C','R')

ORDER BY BPW5_2_Staging.dbo.OORDDETL.ORACTD

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sharonmtowler Default port Points: 1495 More actions · Answer 2

i used this and it seems to work

INNER JOIN

dbo.OCUSMA ON CAST(dbo.OORDDETL.ORACTD As VARCHAR(12)) = dbo.OCUSMA.OKCUNO