Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    4

    Unanswered: Identical queries (almost) with different results - Procedure X DB2 Command Editor

    I do a query that returns results (dates) when I run via different procedure.
    When I run via the command editor for DB2 Control Center the result is correct
    Someone has an explanation of the reason for this be occurring and a possible solution to the problem?

    follows the code of my procedure:

    CREATE OR REPLACE PROCEDURE VALMINIMO(IN PINI CHAR(10), IN PFIM CHAR(10), IN PMARCA VARCHAR(100) )
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL

    BEGIN
    DECLARE RESULSELECT CURSOR WITH RETURN FOR

    SELECT DATE(O.DATAINI) AS INICIO,
    DATE(O.DATAFIM) AS FIM,
    O.PRECO AS PRECO ,
    P.DESCRICAO AS PRODUTO
    FROM
    OFERTA O,
    PRODUTO P,
    MARCA M
    WHERE
    M.DESCRICAO=PMARCA
    AND M.CODMARCA=P.CODMARCA
    AND O.CODPROD=P.CODPROD
    AND O.PRECO<M.PMINIMO
    AND O.DATAINI BETWEEN CHAR(PINI) AND CHAR(PFIM)
    AND O.DATAFIM BETWEEN CHAR(PINI) AND CHAR(PFIM)
    ORDER BY O.CODOFER;
    OPEN RESULSELECT;
    RETURN;
    END

    Results via procedure call:
    CALL "TOFERTAS".VALMINIMO('2012-11-20','2012-11-25','SONY')

    INICIO FIM PRECO PRODUTO
    ---------- ---------- ------ -------------
    2013-10-11 2013-11-11 24.16 PRODUTO_49534
    2013-11-11 2013-12-11 16.10 PRODUTO_43042
    2013-10-11 2013-12-11 299.97 PRODUTO_83467
    2013-08-11 2013-12-11 171.90 PRODUTO_18936
    2013-09-11 2014-01-11 40.08 PRODUTO_96330
    2013-09-11 2014-01-11 24.90 PRODUTO_37073
    2013-08-11 2013-10-11 132.78 PRODUTO_21793
    2013-09-11 2013-12-11 167.69 PRODUTO_48780
    2013-08-11 2014-01-11 118.70 CELULAR52451
    2013-09-11 2014-01-11 10.67 PRODUTO_93312
    2013-11-11 2014-01-11 8.87 PRODUTO_78953
    2013-08-11 2013-12-11 116.69 PRODUTO_32797
    2013-09-11 2014-01-11 13.19 PRODUTO_93312
    2013-08-11 2013-12-11 222.06 PRODUTO_52790
    2013-09-11 2013-12-11 26.37 PRODUTO_37073
    2013-08-11 2014-01-11 103.92 PRODUTO_9594
    2013-09-11 2013-11-11 31.78 PRODUTO_89024
    2013-09-11 2013-11-11 22.49 PRODUTO_43042
    2013-08-11 2013-09-11 199.24 PRODUTO_92961
    2013-11-11 2014-01-11 193.62 PRODUTO_92961

    Here I do the same query in the Control Center or Data Studio SQL Routine Dev.

    SELECT O.dataini AS INICIO,
    O.datafim AS FIM,
    O.preco AS PRECO,
    P.descricao AS PRODUTO
    FROM oferta O,
    produto P,
    marca M
    WHERE M.descricao = 'SONY'
    AND M.codmarca = P.codmarca
    AND O.codprod = P.codprod
    AND O.preco < M.pminimo
    AND O.dataini BETWEEN '2012-11-20' AND '2012-11-25'
    AND O.datafim BETWEEN '2012-11-20' AND '2012-11-25'
    ORDER BY O.CODOFER;

    Return OK is:

    INICIO FIM PRECO PRODUTO
    ---------- ---------- ------ -------------
    2012-11-22 2012-11-23 24.16 PRODUTO_49534
    2012-11-23 2012-11-24 16.10 PRODUTO_43042
    2012-11-22 2012-11-24 299.97 PRODUTO_83467
    2012-11-20 2012-11-24 171.90 PRODUTO_18936
    2012-11-21 2012-11-25 40.08 PRODUTO_96330
    2012-11-21 2012-11-25 24.90 PRODUTO_37073
    2012-11-20 2012-11-22 132.78 PRODUTO_21793
    2012-11-21 2012-11-24 167.69 PRODUTO_48780
    2012-11-20 2012-11-25 118.70 CELULAR52451
    2012-11-21 2012-11-25 10.67 PRODUTO_93312
    2012-11-23 2012-11-25 8.87 PRODUTO_78953
    2012-11-20 2012-11-24 116.69 PRODUTO_32797
    2012-11-21 2012-11-25 13.19 PRODUTO_93312
    2012-11-20 2012-11-24 222.06 PRODUTO_52790
    2012-11-21 2012-11-24 26.37 PRODUTO_37073
    2012-11-20 2012-11-25 103.92 PRODUTO_9594
    2012-11-21 2012-11-23 31.78 PRODUTO_89024
    2012-11-21 2012-11-23 22.49 PRODUTO_43042
    2012-11-20 2012-11-21 199.24 PRODUTO_92961
    2012-11-23 2012-11-25 193.62 PRODUTO_92961

    My DB2 is DB2 v9.7.500.4299 special_27924 , 64 Bit for Windows

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Rodrigo VR, I am not sure of the Datatypes but you might try changing the CHAR function:
    Code:
    AND O.DATAINI BETWEEN CHAR(PINI) AND CHAR(PFIM)
    AND O.DATAFIM BETWEEN CHAR(PINI) AND CHAR(PFIM)
    To a DATE function:
    Code:
    AND O.DATAINI BETWEEN DATE(PINI) AND DATE(PFIM)
    AND O.DATAFIM BETWEEN DATE(PINI) AND DATE(PFIM)

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    As stealth mentioned what we all think is the culprit. What do you get when you run the query on its own, with:

    AND O.dataini BETWEEN CHAR('2012-11-20') AND CHAR('2012-11-25')
    AND O.datafim BETWEEN CHAR('2012-11-20') AND CHAR('2012-11-25')

    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another doubt I had was,
    are created VALMINIMO and called "TOFERTAS".VALMINIMO same?
    Wasn't the VALMINIMO created on other schema than "TOFERTAS"?

Posting Permissions

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