Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2006
    Posts
    13

    Unanswered: SQL0401N : operation "=" are not compatible

    i'm executing the below code with the command

    db2 -td! -v -fmysqlcode.sql
    _________________________________

    connect to APPLE USER webuser using webuser!
    CREATE PROCEDURE insert_event_f
    (connector_id_p VARCHAR(40),
    object_key_p VARCHAR(80),
    object_name_p VARCHAR(40),
    object_verb_p VARCHAR(40),
    event_priority_p DECIMAL(5),
    v_SimilarCounts DECIMAL)

    LANGUAGE SQL

    BEGIN
    DECLARE tmp_object_verb VARCHAR(30);
    set tmp_object_verb = object_verb_p;
    set v_SimilarCounts = (select count(*) <-- LINE 16
    FROM webuser.WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_verb = object_verb_p
    AND object_key = object_key_p
    AND event_status = 0);
    <---what could be wrong with this?
    IF object_verb_p = 'Physical Delete' THEN

    DELETE FROM webuser.WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_key = object_key_p
    AND event_status != 3;
    set tmp_object_verb = 'Delete';
    END IF;

    IF (v_SimilarCounts = 0) THEN

    INSERT INTO webuser.WBIA_EVENTS
    ( connector_id,
    object_key,
    object_name,
    object_verb,
    event_priority,
    event_status)
    VALUES
    (connector_id_p,
    object_key_p,
    object_name_p,
    tmp_object_verb,
    event_priority_p,
    0);
    END IF;
    RETURN(1);
    END!
    _______________________________________

    but i get this anoying error:
    _______________________________________
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returnedQL0401N The data types of the operands for the operation "=" are not compatible. LINE NUMBER=16. SQLSTATE=42818

    regards, lehare
    Last edited by lehare; 03-02-06 at 10:08.

  2. #2
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16
    Ehrm... event_status is not character, maybe? I would find it rather funny if it is.

  3. #3
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    try SELECT COUNT(*) INTO v_SimilarCounts FROM webuser.WBIA_EVENTS etc...

    also, I don't know for sure, but wouldn't the != be a problem when using ! as a command terminator?

  4. #4
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16

    ah, that's the problem...

    It's indeed the " ! "
    and so it's not line 16, but line 26. Use <> instead.

    cheers,
    Rob.

  5. #5
    Join Date
    Feb 2006
    Posts
    13
    ................................posted twise
    Last edited by lehare; 03-02-06 at 11:23.

  6. #6
    Join Date
    Feb 2006
    Posts
    13
    i changed the code to

    connect to APPLE USER webuser using webuser@
    CREATE PROCEDURE insert_event_f
    (connector_id_p VARCHAR(40),
    object_key_p VARCHAR(80),
    object_name_p VARCHAR(40),
    object_verb_p VARCHAR(40),
    event_priority_p DECIMAL(5),
    v_SimilarCounts INTEGER)
    LANGUAGE SQL

    BEGIN
    DECLARE tmp_object_verb VARCHAR(30);
    set tmp_object_verb = object_verb_p;

    select count(*) INTO v_SimilarCounts
    FROM WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_verb = object_verb_p
    AND object_key = object_key_p
    AND event_status = 0;
    IF object_verb_p = 'Physical Delete' THEN

    DELETE FROM WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_key = object_key_p
    AND event_status <> 3;
    set tmp_object_verb = 'Delete';
    END IF;

    IF (v_SimilarCounts = 0) THEN

    INSERT INTO WBIA_EVENTS
    ( connector_id,
    object_key,
    object_name,
    object_verb,
    event_priority,
    event_status)
    VALUES
    (connector_id_p,
    object_key_p,
    object_name_p,
    tmp_object_verb,
    event_priority_p,
    0);
    END IF;
    RETURN(1);

    END@

    ______________________

    used the @ [as a terminator], and replaced line 26 from ' != ' to ' <> ' and changed line 14 to (select count(*) INTO etc ...

    but i still get EXACTLY the same error

    by the way i'm using windows 2000 [fixpac 4], db2 version 8.1.8

  7. #7
    Join Date
    Feb 2006
    Location
    Utrecht, Netherlands
    Posts
    16

    Start eliminating

    Maybe it's not the content of line 16; it could be something else.
    If you (for test purposes) leave out "AND event_status = 0"
    and retry, is it still protesting, and does it now point to line number 15?

    Very often the nature of the error message is misleading - please check!
    cheers,
    Rob.

  8. #8
    Join Date
    May 2002
    Location
    Philadelphia, PA
    Posts
    14
    Post the DDL, it could be a type conversion thing.

  9. #9
    Join Date
    Mar 2006
    Location
    UK
    Posts
    8
    save below code in a file as file.sql

    and run db2 -tvf file.sql

    I hope this will work



    connect to APPLE USER webuser using webuser;
    CREATE PROCEDURE insert_event_f
    (connector_id_p VARCHAR(40),
    object_key_p VARCHAR(80),
    object_name_p VARCHAR(40),
    object_verb_p VARCHAR(40),
    event_priority_p DECIMAL(5),
    v_SimilarCounts DECIMAL)

    LANGUAGE SQL

    BEGIN
    DECLARE tmp_object_verb VARCHAR(30);
    set tmp_object_verb = object_verb_p;
    set v_SimilarCounts = (select count(*) FROM webuser.WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_verb = object_verb_p
    AND object_key = object_key_p
    AND event_status = 0);
    IF object_verb_p = 'Physical Delete' THEN

    DELETE FROM webuser.WBIA_EVENTS
    WHERE object_name = object_name_p
    AND object_key = object_key_p
    AND event_status != 3;
    set tmp_object_verb = 'Delete';
    END IF;

    IF (v_SimilarCounts = 0) THEN

    INSERT INTO webuser.WBIA_EVENTS
    ( connector_id,
    object_key,
    object_name,
    object_verb,
    event_priority,
    event_status)
    VALUES
    (connector_id_p,
    object_key_p,
    object_name_p,
    tmp_object_verb,
    event_priority_p,
    0);
    END IF;
    RETURN(1);
    END;

Posting Permissions

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