Clash Royale CLAN TAG

How can I apply condition to Insert data in Oracle Forms 6i

I have table DOC_CUST_PRODUCT (DOC_CODE, CUST_CODE, P_CODE)

DOC_CUST_PRODUCT (DOC_CODE, CUST_CODE, P_CODE)

I want to restrict insertion and show message when DOC_CODE has more than 5 different CUST_CODE

DOC_CODE

CUST_CODE

SELECT COUNT(DISTINCT CUST_CODE)
INTO Y
FROM BP_DOC_CUST_PRODUCT
WHERE DOC_CODE = :DOC_CODE;

IF NVL(Y,0) > 4 THEN
MESSAGE('Sorry, Can Not Entry More Than 5 Chemist...');
MESSAGE('Sorry, Can Not Entry More Than 5 Chemist...');


but it doesn't work.

1 Answer
1

Where did you put that code? Should be WHEN-VALIDATE-ITEM trigger on DOC_CODE item.

WHEN-VALIDATE-ITEM

DOC_CODE

If there are two (or more) items named DOC_CODE, Forms doesn't know which one you're referencing - I suggest you to always specify block name with the item name.

DOC_CODE

Code you posted isn't complete - variable declaration is missing, IF doesn't have an END IF. I don't know whether you really didn't do that, or you just didn't post everything you wrote (by the way, how are we supposed to know that?).

IF

END IF

COUNT function can't return NULL as a result, so applying NVL to the variable Y is superfluous.

COUNT

NULL

NVL

Y

The following code should be OK (if you fix what's missing - a block name).

-- WHEN-VALIDATE-ITEM trigger on :BLOCK_NAME.DOC_CODE item

declare
l_count number;
begin
select count(distinct b.cust_code)
into l_count
from bp_doc_cust_product b
where b.doc_code = :block_name.doc_code;

if l_count = 5 then
message('Sorry, ...');
message('Sorry, ...');
end if;
end;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.