Results 1 to 2 of 2
  1. #1
    Juan López Guest

    Question Answered: problems looking for a tuple

    I have this:
    Code:
    create temp table tabla3 ( articulo char(10), cantidad decimal(8,2) )
    
    bla bla bla bla .....
    
    foreach pp_cur2 into p_tabla1.* 
      select * from tabla3 where articulo = p_tabla1.campo1 
      if STATUS = NOTFOUND then 
        insert into tabla3 values (p_tabla1.campo1 ,totalcantidad) 
      else 
        update tabla3 set cantidad = cantidad + totalcantidad 
          where articulo = p_tabla1.campo1
      end if 
    end foreach
    it doesn´t work, cause i have a lot of tuples with the same articulo. i also have put:
    Code:
    initialize elarticulo to null 
    select articulo into elarticulo 
      from tabla3
      where articulo = p_tabla1.campo1 
    if articulo is null then insert .....
    else update .... 
    end if
    this have an error number 201 could anybody help me?

  2. Best Answer
    Posted by Paul G. Brown

    "Hi Juan!

    As a general rule of thumb, using a more "relational" approach to this kind of problem results in better performing, more maintainable code. Specifically, in this case using correlated queries rather than lopping cursors and conditional branching.

    I gather from your question that your schema looks something like this:
    Code:
    CREATE TABLE p_tabla1
      ( . campo1 decimal(8,2) . );
    and
    Code:
    CREATE TEMP TABLE tabla3 
      ( articulo char(10), cantidad decimal(8,2) );
    What you want to do can (I think) be accomplished with three queries in all engines. (It can be accomplished in two in XP, where we lift the shortcoming in the 5, 7 and 9 versions of IFMX's SQL that says you can't mention the target table in a sub-query, but I digress.)
    Code:
    CREATE PROCEDURE Foo ( totalcantidad decimal(8,2) )
    
    UPDATE tabla3 SET cantidad = cantidad + totalcantidad 
    WHERE EXISTS (SELECT 1 FROM p_tabla1 
                    WHERE tabla3.articulo = p_tabla1.campo1);
    
    SELECT p_tabla1.campo1, totalcantidad 
      FROM p_tabla1 WHERE NOT EXISTS (SELECT 1 FROM tabla3 
                                       WHERE tabla3.articulo = p_tabla1.campo1) 
        INTO TEMP tabla4;
    
    INSERT INTO tabla3 SELECT * FROM tabla4;
    
    END PROCEDURE;
    What you're doing in the looping code is logically equivalent to marking a class roll by asking everyone who is *absent* to raise their hands (reasoning, probably quite correstly, that there are likely to be fewer absentees than students present). The more 'relational' approach introduced here will be faster, and it is a cleaner, clearer implementation of the operation. Note that you'll also have to adjust for the NULL case; rows in tabla3 WHERE articulo IS NULL will never be updated, or DELETEd if you're using another query to accomplish that. Another good rule of thumb is to declare columns to always be NOT NULL.

    Anyhow, hope this helps."


  3. #2
    Paul G. Brown Guest
    Hi Juan!

    As a general rule of thumb, using a more "relational" approach to this kind of problem results in better performing, more maintainable code. Specifically, in this case using correlated queries rather than lopping cursors and conditional branching.

    I gather from your question that your schema looks something like this:
    Code:
    CREATE TABLE p_tabla1
      ( . campo1 decimal(8,2) . );
    and
    Code:
    CREATE TEMP TABLE tabla3 
      ( articulo char(10), cantidad decimal(8,2) );
    What you want to do can (I think) be accomplished with three queries in all engines. (It can be accomplished in two in XP, where we lift the shortcoming in the 5, 7 and 9 versions of IFMX's SQL that says you can't mention the target table in a sub-query, but I digress.)
    Code:
    CREATE PROCEDURE Foo ( totalcantidad decimal(8,2) )
    
    UPDATE tabla3 SET cantidad = cantidad + totalcantidad 
    WHERE EXISTS (SELECT 1 FROM p_tabla1 
                    WHERE tabla3.articulo = p_tabla1.campo1);
    
    SELECT p_tabla1.campo1, totalcantidad 
      FROM p_tabla1 WHERE NOT EXISTS (SELECT 1 FROM tabla3 
                                       WHERE tabla3.articulo = p_tabla1.campo1) 
        INTO TEMP tabla4;
    
    INSERT INTO tabla3 SELECT * FROM tabla4;
    
    END PROCEDURE;
    What you're doing in the looping code is logically equivalent to marking a class roll by asking everyone who is *absent* to raise their hands (reasoning, probably quite correstly, that there are likely to be fewer absentees than students present). The more 'relational' approach introduced here will be faster, and it is a cleaner, clearer implementation of the operation. Note that you'll also have to adjust for the NULL case; rows in tabla3 WHERE articulo IS NULL will never be updated, or DELETEd if you're using another query to accomplish that. Another good rule of thumb is to declare columns to always be NOT NULL.

    Anyhow, hope this helps.

Posting Permissions

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