Post reply

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

  • 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

  • September 29, 2010 at 12:48 pm

    #1229279

    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!

  • September 29, 2010 at 12:52 pm

    #1229283

    i used this and it seems to work

    INNER JOIN

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

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

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