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
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
