Results 1 to 3 of 3

Thread: Compairing DATA

  1. #1
    Join Date
    Apr 2003
    Posts
    7

    Unanswered: Compairing DATA

    Hi There,
    I'm reading the version number from a table and then compairing it with a variable of the same name Although they both appear the same the comparison <> think's they are diffrent.
    I think the problem might be in that I'm using Oracle Client 9 and the DB is Oracle 8.
    Both are using the same NLS_LANG set up.

    Do I have to do some form of casting/convertion on the data?

    Code and results shown below.

    Cheers Nick

    __________________________________________________ ________
    /* clear buffer then enable buffer so you can output buffer empty at start*/
    execute dbms_output.disable;

    execute dbms_output.enable (100000);

    /* Set the output of the SQL to the SQL Session window*/
    SET SERVEROUTPUT ON

    DECLARE
    Ver_Number varchar2 (30);
    Ver2_Number varchar2 (30) := 'Version 1.0';

    BEGIN

    SELECT TEST_ADDRESS into Ver_Number FROM TEST2.ADDRESS;

    dbms_output.put_line(Ver_Number);
    dbms_output.put_line(Ver2_Number);

    If Ver_Number <> Ver2_Number Then
    DBMS_OUTPUT.PUT_LINE ('Incorrect Version Number');
    DBMS_OUTPUT.PUT_LINE ('Stoping Update/Build');
    END IF;


    END;
    /

    *********** RESULTS **************************

    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.

    Version 1.0
    Version 1.0
    Incorrect Version Number
    Stoping Update/Build

    PL/SQL procedure successfully completed.

  2. #2
    Join Date
    Jan 2003
    Location
    Woking
    Posts
    107

    Re: Compairing DATA

    Originally posted by nickp
    Hi There,
    I'm reading the version number from a table and then compairing it with a variable of the same name Although they both appear the same the comparison <> think's they are diffrent.
    I think the problem might be in that I'm using Oracle Client 9 and the DB is Oracle 8.
    Both are using the same NLS_LANG set up.

    Do I have to do some form of casting/convertion on the data?

    Code and results shown below.

    Cheers Nick

    __________________________________________________ ________
    /* clear buffer then enable buffer so you can output buffer empty at start*/
    execute dbms_output.disable;

    execute dbms_output.enable (100000);

    /* Set the output of the SQL to the SQL Session window*/
    SET SERVEROUTPUT ON

    DECLARE
    Ver_Number varchar2 (30);
    Ver2_Number varchar2 (30) := 'Version 1.0';

    BEGIN

    SELECT TEST_ADDRESS into Ver_Number FROM TEST2.ADDRESS;

    dbms_output.put_line(Ver_Number);
    dbms_output.put_line(Ver2_Number);

    If Ver_Number <> Ver2_Number Then
    DBMS_OUTPUT.PUT_LINE ('Incorrect Version Number');
    DBMS_OUTPUT.PUT_LINE ('Stoping Update/Build');
    END IF;


    END;
    /

    *********** RESULTS **************************

    PL/SQL procedure successfully completed.


    PL/SQL procedure successfully completed.

    Version 1.0
    Version 1.0
    Incorrect Version Number
    Stoping Update/Build

    PL/SQL procedure successfully completed.
    Hi,
    Check the data type TEST_ADDRESS in the table.
    It migth be defined as CHAR(30).
    Which means that spaces are getting padded to the value that
    is being fetched from the table.
    Either redefine your table structure or use a TRIM function as follows

    If TRIM(Ver_Number) <> TRIM(Ver2_Number) Then

    Hope it Helps.
    nn

  3. #3
    Join Date
    Apr 2003
    Posts
    7

    Smile Re: Compairing DATA

    Originally posted by NoviceNo1
    Hi,
    Check the data type TEST_ADDRESS in the table.
    It migth be defined as CHAR(30).
    Which means that spaces are getting padded to the value that
    is being fetched from the table.
    Either redefine your table structure or use a TRIM function as follows

    If TRIM(Ver_Number) <> TRIM(Ver2_Number) Then

    Hope it Helps.

    Thanks the TRIM method worked a treat.

    Cheers Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •