OraFAQ Forum: Forms » passing check box values to WHERE clause

Home » Developer & Programmer » Forms » passing check box values to WHERE clause (oracle form 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

passing check box values to WHERE clause [message #599593]

Sat, 26 October 2013 11:32

anoopmanagoli
Messages: 20
Registered: August 2012
Location: india

Junior Member

Hi,

I have created a Data block - 'CONTACTS' (Database data block)
and has database item - 'Code', 'Descr'

The number of records displayed is set to 5.

Value When checked - 'Y'
Value When Unchecked - 'N'
Check box mapping of other values - 'unchecked'

The requirement is when i check one or multiple checkboxes, i should pass the 'Code' item values to the WHERE clause.

Right now whenver i am trying to do so, only the current record value is copied to the WHERE clause.

I have tried using basic loop as well as while loop but things havmt worked.

Below is a basic code which will work for one record, request to guide me with muliple checkbox ticked.

IF :contacts.cb = 'Y' THEN

IF p_where is null then

p_where := :contacts.code;
else
p_where := p_where ||','||:contacts.code;

end if;
end if;

p_where:= 'where code in ('||p_where||')';

Regards.
Anoop.

Report message to a moderator

Re: passing check box values to WHERE clause [message #599595 is a reply to message #599593]

Sat, 26 October 2013 13:00

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

I have tried using basic loop as well as while loop but things havmt worked.

Would you mind to post that attempt? What does "things haven't worked" actually mean? Did you get an error? If so, which one? If not, what happened? Because, it is difficult to debug code you can't see.

By the way, have a look at CHECKBOX_CHECKED built-in, maybe you'll find it useful.

Report message to a moderator

Re: passing check box values to WHERE clause [message #599610 is a reply to message #599595]

Sat, 26 October 2013 14:20

anoopmanagoli
Messages: 20
Registered: August 2012
Location: india

Junior Member

Hi,

I am writing the code inside 'WHEN BUTTON PRESSED'. My main objective is to return the count of records based
based on several conditions and one among them is CODE which is can be single or multiple based on the checkbox checked.

Below is the code I had tried, which is incorrect :

BEGIN
GO_BLOCK ('CONTACT');

WHILE :CONTACT.CB = 'Y'
LOOP

IF p_where is NULL THEN

p_where := :CONTACT.CODE;

ELSE

p_where := p_where ||','||:CONTACT.CODE;

END IF;

NEXT_RECORD;

END LOOP;

END;

Report message to a moderator

Re: passing check box values to WHERE clause [message #599613 is a reply to message #599610]

Sat, 26 October 2013 14:38

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

WHILE condition is wrong; you need to loop through ALL records in a block. The way you put it now, it is possible that the loop won't even start (if the first record's checkbox value is 'N'). Try to utilize :SYSTEM.LAST_RECORD (it returns 'TRUE' or 'FALSE', depending on whether you're in the last record or not), such as

loop
  <do whatever you do here>
  exit when :system.last_record = 'TRUE';
  next_record;
end loop;

Report message to a moderator

Re: passing check box values to WHERE clause [message #599614 is a reply to message #599613]

Sat, 26 October 2013 15:41

anoopmanagoli
Messages: 20
Registered: August 2012
Location: india

Junior Member

Hi,

But the next_record refers to the UNTICKED CHECKBOX as well and thereby returning all the rows eventhough if it is not checked.

IF :contact.cb = 'Y' THEN
LOOP

IF p_where is null then

p_where := :contact.code;

else

p_where := p_where ||','||:contact.code;

end if;

exit when :system.last_record = 'TRUE';
next_record;

END LOOP;

end if;

MESSAGE ( 'p_where :'||p_where);
MESSAGE (' ');

END;

And Even if i write the LOOP before the first IF, it return me the current record value and move to the last record.
please guide me where am i wrong.

Report message to a moderator

Re: passing check box values to WHERE clause [message #599631 is a reply to message #599614]

Sun, 27 October 2013 04:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That's even worse! It doesn't do anything unless CB = 'Y' in the first record.

Here's how I'd do that (example based on Scott's DEPT table; I'm putting DEPTNO into a P_WHERE variable):

declare
  p_where varchar2(500);
begin
  go_block('dept');
  first_record;

  loop
    if checkbox_checked('dept.cb') then
       p_where := p_where ||','|| :dept.deptno;
    end if;
    
    exit when :system.last_record = 'TRUE';
    next_record;
  end loop;

  message('P_WHERE = ' || ltrim(p_where, ','));
  message('P_WHERE = ' || ltrim(p_where, ','));
end;

Attachment: form_cb.JPG
(Size: 29.82KB, Downloaded 6480 times)

Report message to a moderator

Re: passing check box values to WHERE clause [message #599635 is a reply to message #599631]

Sun, 27 October 2013 08:45

anoopmanagoli
Messages: 20
Registered: August 2012
Location: india

Junior Member

Yes had certainly had missed the FIRST_RECORD and things went worst as the earlier code didnt do anything unless CB = 'Y' in the first record.

So i got the point and made the corrections. Thank you very much

Report message to a moderator

Previous Topic:	copy values from dynamic list
Next Topic:	when-validate trigger

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 15:53:33 CST 2025