strange error on stored procedure after update to version 7.6.06.10

[Former Member]

Hi,

in past version MaxDB 7.6.03 i created a stored procedure with follow SQL statement inside:

TRY
            SELECT SUM(QTDE) QTDE_SAIDA
               FROM VENDOR_CONCES.MVIEW_MOVPRODUTOS mviewS
               WHERE mviewS.MY_EMPRESA = :OID_EMPRESA AND
                     mviewS.MY_PRODUTO_DEF = :OID_PRODUTO_DEF AND
                     mviewS.GERA_ESTOQUE = 'S' AND
                     mviewS.TIPO = 'S' AND
                     mviewS.DATA BETWEEN :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE AND
                     EXISTS(SELECT * FROM VENDOR_CONCES.MVIEW_MOVPRODUTOS mviewE WHERE  
                        mviewE.my_Empresa = mviewS.my_Empresa AND
                        mviewE.tipo = 'E' AND
                        mviewE.data <= mviewS.data AND
                        mviewE.my_Produto = mviewS.my_Produto AND
                        mviewE.gera_Estoque = 'S' AND 
                        mviewE.gera_Financeiro != 'N'
                     );
            FETCH INTO :QTDE_SAIDA;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error ao contar Saidas');

if execute that procedure with new version 7.6.06.10 i get an error:

(at 1834): Data types must be compatible.

the line of error is this:

mviewS.DATA BETWEEN :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE AND

if i remove the EXISTS part that procedure works, but i need to use EXISTS there.

ps: MVIEW_MOVPRODUTOS is a table, not a view, i preceed tables with MVIEW for tables created by triggers, to simulate Materialized Views.

ps2: DATA field is DATE field on english.

any idea what can be wrong?

best regards.

Clóvis

[Former Member · Accepted solution]

Hi,

would you mind sending the definition of :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE?

The line of error is this between?

Does the procedure work if this between is not in the select?

And mviewE.gera_Financeiro is a character-column?

Beside this... I remember some trouble with select * in exists...

Perhaps you could try to do some exist ( select 1 from ...

instead of your exists ( select * from

Elke

[Former Member · Accepted solution]

Hi, Elke

you are right, changing SELECT * to SELECT oid works, follow the full procedure with changes:

CREATE DBPROC SP_ESTOQUE_ATUAL_DATA_PROD_DEF (
    IN OID_EMPRESA VARCHAR(8),
    IN OID_PRODUTO_DEF VARCHAR(8),
    IN DTA_ESTOQUE TIMESTAMP,
    OUT ESTOQUE_ATUAL VARCHAR(200))
AS
    VAR MAX_DATA_ESTOQUE_INICIAL TIMESTAMP;
        QTDE_ESTOQUE_INICIAL FIXED(10,3);
        QTDE_SAIDA FIXED(10,3);
        QTDE_ENTRADA FIXED(10,3);
    BEGIN
        TRY
            SELECT MAX(DATA) MAX_DATA_ESTOQUE_INICIAL
               FROM VENDOR_CONCES.ESTOQUEINICIAL
               WHERE MY_EMPRESA = :OID_EMPRESA AND
                   MY_PRODUTO = :OID_PRODUTO_DEF AND
                   DATA <= :DTA_ESTOQUE;
            FETCH INTO :MAX_DATA_ESTOQUE_INICIAL;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error em MAXDATAEINI');

        TRY
            IF MAX_DATA_ESTOQUE_INICIAL IS NULL THEN BEGIN
                SET MAX_DATA_ESTOQUE_INICIAL = '1900-01-01 00:00:00.000';
                SET QTDE_ESTOQUE_INICIAL = 0;
            END ELSE BEGIN
                SELECT QTDE QTDE_ESTOQUE_INICIAL
                   FROM VENDOR_CONCES.ESTOQUEINICIAL
                   WHERE MY_EMPRESA = :OID_EMPRESA AND
                       MY_PRODUTO = :OID_PRODUTO_DEF AND
                       DATA BETWEEN :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE;
                FETCH INTO :QTDE_ESTOQUE_INICIAL;
            END;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error em Carregamento do Estoque Inicial');

        TRY
            SELECT SUM(QTDE) QTDE_SAIDA
               FROM VENDOR_CONCES.MVIEW_MOVPRODUTOS mviewS
               WHERE mviewS.MY_EMPRESA = :OID_EMPRESA AND
                     mviewS.MY_PRODUTO_DEF = :OID_PRODUTO_DEF AND
                     mviewS.GERA_ESTOQUE = 'S' AND
                     mviewS.TIPO = 'S' AND
                     mviewS.DATA BETWEEN :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE AND
                     EXISTS(SELECT oid FROM VENDOR_CONCES.MVIEW_MOVPRODUTOS mviewE WHERE  
                        mviewE.my_Empresa = mviewS.my_Empresa AND
                        mviewE.tipo = 'E' AND
                        mviewE.data <= mviewS.data AND
                        mviewE.my_Produto = mviewS.my_Produto AND
                        mviewE.gera_Estoque = 'S' AND 
                        mviewE.gera_Financeiro != 'N'
                     );
            FETCH INTO :QTDE_SAIDA;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error ao contar Saidas');

        TRY
            IF QTDE_SAIDA IS NULL THEN
                SET QTDE_SAIDA = 0;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error ao zerar saida');

        TRY
            SELECT SUM(QTDE) QTDE_ENTRADA
               FROM VENDOR_CONCES.MVIEW_MOVPRODUTOS
               WHERE MY_EMPRESA = :OID_EMPRESA AND
                   MY_PRODUTO_DEF = :OID_PRODUTO_DEF AND
                   GERA_ESTOQUE = 'S' AND
                   TIPO = 'E' AND
                   DATA BETWEEN :MAX_DATA_ESTOQUE_INICIAL AND :DTA_ESTOQUE;
            FETCH INTO :QTDE_ENTRADA;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error ao contar entradas');

        TRY
            IF QTDE_ENTRADA IS NULL THEN
                SET QTDE_ENTRADA = 0;
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error ao zera entrada');

        TRY
            SET ESTOQUE_ATUAL = CHR(QTDE_ESTOQUE_INICIAL + QTDE_ENTRADA - QTDE_SAIDA);
        CATCH
            IF $rc <> 100 THEN STOP ($rc, 'unexpected error');
    END;

about the error, I discovered the line using database studio, as I droped the old procedure and tried to recreate, database studio says that error is at BETWEEN expression, but you find the right problem with select * in exists.

best regards

Clóvis