Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2017
    Posts
    3

    Answered: Adding another condition onto a Procedure

    Good Day,

    I apologize if this request is not in the right place. I am pretty new to DB2 and was asked to change an existing procedure. I tried adding another condition into my original if statement based on a new variable but when I try to run it the procedure fails

    Here is the procedure I have. I basically want to say that if the customer is CUST_A then instead of making the trace type 1 for the first where statement make it type 'B'. If the customer is anyother customer code then leave the procedure the way it is.


    CREATE OR REPLACE PROCEDURE CUSTOM_ORDER(IN ID INT )
    LANGUAGE SQL
    SPECIFIC SP_CUSTOM_ORDER

    BEGIN
    DECLARE IFStatus INTEGER;
    SET IFStatus = (SELECT INTERFACE_F FROM ORDER WHERE DETAIL_ID = CUSTOM_ORDER.DLID);
    IF IFStatus IS NULL THEN
    DELETE FROM TRACE WHERE DETAIL_NUMBER = CUSTOM_ORDER.DLID AND TRACE_TYPE IN ('2','1');
    FOR TN AS
    SELECT DISTINCT FIELD_1 TRACE_NUMBER
    FROM TLDTL
    WHERE ORDER_ID = CUSTOM_ORDER.DLID AND PICK_ID = 0 AND FIELD_1 <> '' AND FIELD_1 IS NOT NULL DO
    INSERT INTO TRACE (DETAIL_NUMBER, REF_QUALIFIER, TRACE_TYPE, TRACE_NUMBER) VALUES (CUSTOM_ORDER.DLID, 'BOL', '1', TN.TRACE_NUMBER);
    END FOR;
    FOR TN AS
    SELECT DISTINCT FIELD_2 TRACE_NUMBER
    FROM TLDTL
    WHERE ORDER_ID = CUSTOM_ORDER.DLID AND PICK_ID = 0 AND FIELD_2 <> '' AND FIELD_2 IS NOT NULL DO
    INSERT INTO TRACE (DETAIL_NUMBER, TRACE_TYPE, TRACE_NUMBER) VALUES (CUSTOM_ORDER.DLID, '2', TN.TRACE_NUMBER);
    END FOR;
    END IF;
    END

    Thank you very much for any help you can give me...

  2. Best Answer
    Posted by mark.bb

    "Hello,

    According to references to the parameter inside the routine:

    Create or replace procedure custom_order(in dlid int )"


  3. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    something like:

    case when some_column = 'cust_a' then 'b'
    else '1' end = type

  4. #3
    Join Date
    Jan 2017
    Posts
    3
    Thank you for the quick reply.

    I added a condition at the top to get the bill to code and then added the following:
    WHERE ORDER_ID = CUSTOM_ORDER.DLID AND PICK_ID = 0 AND FIELD_1 <> '' AND FIELD_1 IS NOT NULL DO
    CASE WHEN BILL_TO = 'CUST_A' THEN
    INSERT INTO TRACE (DETAIL_NUMBER, REF_QUALIFIER, TRACE_TYPE, TRACE_NUMBER) VALUES (CUSTOM_ORDER.DLID, 'BOL', 'B', TN.TRACE_NUMBER);
    ELSE
    INSERT INTO TRACE (DETAIL_NUMBER, REF_QUALIFIER, TRACE_TYPE, TRACE_NUMBER) VALUES (CUSTOM_ORDER.DLID, 'BOL', '1', TN.TRACE_NUMBER);
    END CASE;

    I get the following failure:
    The creation or revalidation of object "TMWIN.CUSTOM_ORDER" would result in an invalid direct or indirect self-reference. LINE NUMBER=31. SQLSTATE=429C3

  5. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Hello,

    According to references to the parameter inside the routine:

    Create or replace procedure custom_order(in dlid int )
    Regards,
    Mark.

  6. #5
    Join Date
    Jan 2017
    Posts
    3
    That is great, thank you both so much for your help!

Posting Permissions

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