wDevil wrote : >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? I think you are doing nothing wrong. Instead there seems to be a bug in the MaxDB kernel. Please send me a vtrace containing the call of your test procedure. For vtrace have a look to http://sapdb.2scale.net/maxdb-wiki/VTrace Best Regards, Thomas -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
