Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Unanswered: Execute and conditional statements

    Hi everyone.

    I'm trying to make a conditional statement inside a execute stayment, Is this posible??. I put what i'm trying to do, thanks for your help.


    Code:
     
    DECLARE VLI_CR1REG, VLI_CR2REG, VLV_CR1COMP, VLV_CR2COMP VARCHAR(5);
    
    SET VLV_QUERY = ('BEGIN ATOMIC  DECLARE VLV_QUERY VARCHAR(1000);' ||
    'IF ' || VLI_CR2REG || ' ' || VLV_CR1COMP || ' ' || VLI_CR1REG ||' AND ' || VLI_CR1REG ||' ' || VLV_CR2COMP || ' ' ||VLI_CR2REG ||' THEN ' ||
    'INSERT INTO SESSION.TEMP_REGLASNUME VALUES(0);'||
    'ELSE ' || 
    'INSERT INTO SESSION.TEMP_REGLASNUME VALUES(1);' ||
    'END IF; END;');
    EXECUTE IMMEDIATE VLV_QUERY;
    Last edited by ricci; 12-21-11 at 13:10.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    why not have your if statement set the value for the insert rather than a literal?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ricci View Post
    Is this posible??.
    I think the easiest way to figure that out is to try, no? Your particular statement will likely fail, because it contains a delimiter (the semicolon at the end), but generally speaking this is certainly possible.

  4. #4
    Join Date
    Sep 2011
    Posts
    57
    Hi dav1mo.

    I can´t do beacause is dynamic, I mean I can inserte any kind of commparison it's a rule game.

    Hi n_i.

    I've try it but the matter is what you said, de semicolon. I've try to run it but the same error, because find a semicolon and take this as a limiter but if I don't use it it's like is needing it.

    Best regards.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by ricci View Post
    Hi everyone.

    I'm trying to make a conditional statement inside a execute stayment, Is this posible??. I put what i'm trying to do, thanks for your help.


    Code:
     
    DECLARE VLI_CR1REG, VLI_CR2REG, VLV_CR1COMP, VLV_CR2COMP VARCHAR(5);
    
    SET VLV_QUERY = ('BEGIN ATOMIC  DECLARE VLV_QUERY VARCHAR(1000);' ||
    'IF ' || VLI_CR2REG || ' ' || VLV_CR1COMP || ' ' || VLI_CR1REG ||' AND ' || VLI_CR1REG ||' ' || VLV_CR2COMP || ' ' ||VLI_CR2REG ||' THEN ' ||
    'INSERT INTO SESSION.TEMP_REGLASNUME VALUES(0);'||
    'ELSE ' || 
    'INSERT INTO SESSION.TEMP_REGLASNUME VALUES(1);' ||
    'END IF; END;');
    EXECUTE IMMEDIATE VLV_QUERY;
    (1) Why did you declared VLV_QUERY inside set the value to it?

    (2) I thought that combining two insert statement into one was possible and better, like
    Code:
    DECLARE VLV_QUERY VARCHAR(1000);
    
    SET VLV_QUERY
        = 'INSERT INTO SESSION.TEMP_REGLASNUME VALUES('
          || ' CASE WHEN ' || VLI_CR2REG || ' ' || VLV_CR1COMP || ' ' || VLI_CR1REG
               || '  AND ' || VLI_CR1REG || ' ' || VLV_CR2COMP || ' ' || VLI_CR2REG
               || ' THEN 0 ELSE 1 END )';
    (3) SESSION.TEMP_REGLASNUME looks one column temporary table.
    And you inserted 0 or 1.
    Why did you used temporary table rather than a declared variable?

    If you want pass the value to another stored procedure,
    you can pass the value as a parameter for the procedure.

    And I thought that passing as a parameter is better for debugging/maintenance,
    because it make clear the procedures using the value.
    If passed as a temporary table,
    you might influence unexpectedly to a procedure which is using the value outside your knowledge,
    at the time of modifying this procedure.
    Last edited by tonkuma; 12-21-11 at 21:56.

  6. #6
    Join Date
    Sep 2011
    Posts
    57

    Thanks

    Hi tonkuma.

    I'm grateful for your help, It works, it's exactly what i needed.

    Thanks a lot.

    Best regards.

Posting Permissions

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