From: wDevil wDevil (wwdevilgmail.com)
Date: Mon May 15 2006 - 06:45:28 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

I can use QUERIES whith functions in the DBPROC? For example:
---------------------------------------------------------------------------------------
CREATE PUBLIC FUNCTION DBADMIN.FRANK (ID_PAGE INT,ID_AUC INT)
RETURNS INT AS
    VAR ID_REK INT; ALL_SUM FLOAT; ALL_VIEW INT;
TRY
SELECT SUM(R_SUM) FROM DBADMIN.BI_REK_AUC WHERE ID_AUCTION=:id_auc;
FETCH INTO :ALL_SUM ;

SELECT SUM(NUM_VIEW) FROM DBADMIN.BI_RANK WHERE ID_PAGE=:id_page OR ID_PAGE=0;
FETCH INTO :ALL_VIEW;

SELECT BI_REKLAMA.ID
    FROM DBADMIN.BI_REK_AUC,
                DBADMIN.BI_RANK,
                DBADMIN.BI_CAMP,
                DBADMIN.INV_ACCOUNTS,
                DBADMIN.BI_REKLAMA
    WHERE ROWNO<=1 AND
                  BI_REK_AUC.ID_AUCTION=:id_auc AND
                  BI_RANK.ID_PAGE(+)=:id_page AND
                  BI_RANK.ID_REKLAMA(+)=BI_REK_AUC.ID_REKLAMA AND
                  (BI_REK_AUC.R_SUM/:ALL_SUM>=
                  BI_RANK.NUM_VIEW/:ALL_VIEW OR
BI_REK_AUC.R_SUM/:ALL_SUM>=0) AND
                BI_REKLAMA.ID=BI_REK_AUC.ID_REKLAMA AND
                 BI_CAMP.ID=BI_REKLAMA.ID_CAMP AND
                 BI_CAMP.IS_ACTIVE=1 AND
                 DATE(NOW())>=DATE(BI_CAMP.DT_CREATE) AND
                 DATE(NOW())<=DATE(BI_CAMP.DT_END) AND
                 DATE(NOW())>=DATE(BI_REKLAMA.DT_CREATE) AND
                 DATE(NOW())<=DATE(BI_REKLAMA.DT_END) AND
                 INV_ACCOUNTS.ID=BI_CAMP.ID_ACCOUNT AND
                 INV_ACCOUNTS.BALANCE>BI_REK_AUC.R_SUM;
                 FETCH INTO :ID_REK;
  CATCH
  IF $RC <> 100 THEN STOP ($RC, 'unexpected error');
RETURN ID_REK;
------------------------------------------------------------------------------------------------------
Query:
SELECT BI_REKLAMA.ID AS ID_REK,
               BI_REK_AUC.R_SUM,
               BI_AUCTION.ID AS ID_AUC,
               BI_PAGE.ID AS ID_PAGE,
               BI_ZONE.ID AS ID_ZONE,
               BI_ZONE.ID_PAY_TYPE,
               BI_REKLAMA.ID_TYPE_REK AS REK_TYPE,
               BI_CAMP.ID_ACCOUNT,
               BI_CAMP.ID AS ID_CAMP,
               BI_PROMO.ID_TYPE_PROMO AS PROMO_TYPE,
               BI_REF_KEYWORD.ID AS ID_KWR,
               BI_REF_KEYWORD.KEYWORDS
FROM DBADMIN.BI_REKLAMA,
             DBADMIN.BI_REK_AUC,
             DBADMIN.BI_AUCTION,
             DBADMIN.BI_PZ_AUC,
             DBADMIN.BI_PAGE_ZONE,
             DBADMIN.BI_PAGE,
             DBADMIN.BI_ZONE,
             DBADMIN.BI_CAMP,
             DBADMIN.BI_PROMO,
             DBADMIN.BI_AUC_KEYW,
             DBADMIN.BI_REF_KEYWORD
WHERE BI_REK_AUC.ID_REKLAMA=BI_REKLAMA.ID AND
             BI_REK_AUC.ID_AUCTION=BI_AUCTION.ID AND
             BI_PZ_AUC.ID=BI_AUCTION.ID_PZ_AUC AND
             BI_PAGE_ZONE.ID=BI_PZ_AUC.ID_PAGE_ZONE AND
             BI_PAGE.ID=BI_PAGE_ZONE.ID_PAGE AND
             BI_ZONE.ID=BI_PAGE_ZONE.ID_ZONE AND
             BI_CAMP.ID=BI_REKLAMA.ID_CAMP AND
             BI_PROMO.ID_REKLAMA(+)=BI_REKLAMA.ID AND
             BI_REKLAMA.ID=DBADMIN.FRANK(bi_page.id,bi_auction.id) AND
             BI_REF_KEYWORD.ID=BI_AUC_KEYW.ID_KEYWORD AND
             BI_AUC_KEYW.ID_AUCTION=BI_AUCTION.ID
--------------------------------------------------------------------------------------------
All work. But if i do:
------------------------------------------------------------------------------
CREATE DBPROC test (IN id_page INT,OUT ID_REK INT)
AS
SELECT BI_REKLAMA.ID AS ID_REK,
               BI_REK_AUC.R_SUM,
               BI_AUCTION.ID AS ID_AUC,
               BI_PAGE.ID AS ID_PAGE,
               BI_ZONE.ID AS ID_ZONE,
               BI_ZONE.ID_PAY_TYPE,
               BI_REKLAMA.ID_TYPE_REK AS REK_TYPE,
               BI_CAMP.ID_ACCOUNT,
               BI_CAMP.ID AS ID_CAMP,
               BI_PROMO.ID_TYPE_PROMO AS PROMO_TYPE,
               BI_REF_KEYWORD.ID AS ID_KWR,
               BI_REF_KEYWORD.KEYWORDS
FROM DBADMIN.BI_REKLAMA,
             DBADMIN.BI_REK_AUC,
             DBADMIN.BI_AUCTION,
             DBADMIN.BI_PZ_AUC,
             DBADMIN.BI_PAGE_ZONE,
             DBADMIN.BI_PAGE,
             DBADMIN.BI_ZONE,
             DBADMIN.BI_CAMP,
             DBADMIN.BI_PROMO,
             DBADMIN.BI_AUC_KEYW,
             DBADMIN.BI_REF_KEYWORD
WHERE BI_REK_AUC.ID_REKLAMA=BI_REKLAMA.ID AND
             BI_REK_AUC.ID_AUCTION=BI_AUCTION.ID AND
             BI_PZ_AUC.ID=BI_AUCTION.ID_PZ_AUC AND
             BI_PAGE_ZONE.ID=BI_PZ_AUC.ID_PAGE_ZONE AND
             BI_PAGE.ID=BI_PAGE_ZONE.ID_PAGE AND
             BI_ZONE.ID=BI_PAGE_ZONE.ID_ZONE AND
             BI_CAMP.ID=BI_REKLAMA.ID_CAMP AND
             BI_PROMO.ID_REKLAMA(+)=BI_REKLAMA.ID AND
             BI_REKLAMA.ID=DBADMIN.FRANK(:id_page,bi_auction.id) AND
             BI_REF_KEYWORD.ID=BI_AUC_KEYW.ID_KEYWORD AND
             BI_AUC_KEYW.ID_AUCTION=BI_AUCTION.ID;
FETCH INTO :ID_REK;
----------------------------------------------------------------------------------------
After CALL TEST(1124,:id_rek);

Auto Commit: On, SQL Mode: Internal, Isolation Level: Committed
 Integrity constraint violation;-8006 POS(55) Data types must be
compatible:P1,UNKNOWN,FIXED.

 I replace "BI_REKLAMA.ID=DBADMIN.FRANK(:id_page,bi_auction.id) AND"
-> "BI_REKLAMA.ID=DBADMIN.FRANK(1124,bi_auction.id) AND" in the
procedure. Error is not alert, but procedure return empty result.
If i delete from query
"BI_REKLAMA.ID=DBADMIN.FRANK(:id_page,bi_auction.id) AND" - procedure
work.
What i do wrong?

--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]