Home » Developer & Programmer » Forms » execute_query between two text items (forms 6i and 11g)
execute_query between two text items [message #659200] Mon, 09 January 2017 07:18 Go to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
hi experts..
i need help regarding 6i and 11g forms
i need to execute_query between two given text items and also show 2nd block.
in this pic. i tried show all records between 6000 and 11000.but not executed


please experts do some help.

./fa/13410/0/


[mod-edit: imaged inserted into message body by bb]
  • Attachment: 6i.JPG
    (Size: 86.09KB, Downloaded 145 times)

[Updated on: Mon, 09 January 2017 23:09] by Moderator

Report message to a moderator

Re: execute_query between two text items [message #659205 is a reply to message #659200] Mon, 09 January 2017 09:26 Go to previous messageGo to next message
joy_division
Messages: 4711
Registered: February 2005
Location: East Coast USA
Senior Member
Many of us are prevented from viewing attachments. Please post it inline.
So, with that being said, I do not know what you want to do.
Do you want a user to input two different numbers and display all rows where a column value falls between those two numbers? If so, then use SET_BLOCK_PROPERTY with DEFAULT_WHERE.
Re: execute_query between two text items [message #659207 is a reply to message #659205] Mon, 09 January 2017 09:44 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
yes i want to get all rows falls between those two numbers. i need trigger.
Re: execute_query between two text items [message #659209 is a reply to message #659207] Mon, 09 January 2017 10:02 Go to previous messageGo to next message
cookiemonster
Messages: 12625
Registered: September 2008
Location: Rainy Manchester
Senior Member
So add one and write the code.
Pre-query with set_block_property as Joy said.

If you're having a specific problem getting it to work post the code you tried and we'll help out.
Re: execute_query between two text items [message #659210 is a reply to message #659209] Mon, 09 January 2017 10:06 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
:global.salary:=:salary1 between :salary2;
execute_query(no_validate);


between is no run i need code
Re: execute_query between two text items [message #659218 is a reply to message #659210] Mon, 09 January 2017 13:07 Go to previous messageGo to next message
joy_division
Messages: 4711
Registered: February 2005
Location: East Coast USA
Senior Member
zaibi wrote on Mon, 09 January 2017 11:06
:global.salary:=:salary1 between :salary2;
execute_query(no_validate);


between is no run i need code
Of course that won't work. It doesn't even make sense.

You have to build a where clause and use SET_BLOCK_PROPERTY as I have said. You can use Forms Help to see how to use SET_BLOCK_PROPERTY with either DEFAULT_WHERE or ONETIME_WHERE.

Sample where clause is:
 'where some_column between '||:some_value||' and '||:some_other_value||''
.

[Updated on: Mon, 09 January 2017 13:08]

Report message to a moderator

Re: execute_query between two text items [message #659219 is a reply to message #659218] Mon, 09 January 2017 15:18 Go to previous messageGo to next message
Littlefoot
Messages: 21042
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I guess there are two blocks: a control block (which contains two text items, let's call them "sal_from" and "sal_to") and a database block (which is based on Scott's EMP table).

A simple (perhaps not the best) option is:
  • Set control block's navigation style to "change data block".
  • Create WHEN-NEW-BLOCK-INSTANCE trigger on the EMP block as
    if :cond.sal_from is not null and :cond.sal_to is not null then
       set_block_property('emp', onetime_where, 'sal between :cond.sal_from and :cond.sal_to');
       execute_query;
    end if;

    @JD: you don't have to concatenate values, put everything into a pair of single quotes.
Run the form, see how it behaves.
Re: execute_query between two text items [message #659220 is a reply to message #659219] Mon, 09 January 2017 16:37 Go to previous messageGo to next message
joy_division
Messages: 4711
Registered: February 2005
Location: East Coast USA
Senior Member
Thanks Littlefoot. I sometimes forget when I have to "build" the statement and when I can just use it. I usually just do it one way, try running it, only to find out I did it the wrong way, and then fix it. I never learn from my mistakes.

I think a lot of times I am using many conditions, with conditional code to build parts of the where clause, and that's when I have to concatenate. Or not.

[Updated on: Mon, 09 January 2017 16:40]

Report message to a moderator

Re: execute_query between two text items [message #659223 is a reply to message #659220] Mon, 09 January 2017 20:45 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
BEGIN
Set_Block_Property('employees',Default_Where, ' salary Between '||:salary1||' AND '||:salary2);
END;

its work but i want to add new condition.
select * from employees where dept_id=:dept_id and salary between :salary1 and :salary2;
Re: execute_query between two text items [message #659227 is a reply to message #659223] Tue, 10 January 2017 00:06 Go to previous messageGo to next message
Littlefoot
Messages: 21042
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What's stopping you?
Re: execute_query between two text items [message #659229 is a reply to message #659227] Tue, 10 January 2017 02:51 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
i want to add new condition with betweeen operator
Re: execute_query between two text items [message #659230 is a reply to message #659229] Tue, 10 January 2017 02:55 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
sir i need drop down combo list item trigger.
which i press any alphabets then show related data in drop down combo list item.
i have one column (Country) and table name is (Student_detail).so what the trigger code and also trigger name.
Re: execute_query between two text items [message #659232 is a reply to message #659230] Tue, 10 January 2017 03:34 Go to previous messageGo to next message
Littlefoot
Messages: 21042
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As of new condition: once again: what's stopping you to add it to existing condition? Just add it.

set_block_property('emp', onetime_where, 'deptno = :cond.deptno and sal between :cond.sal_from and :cond.sal_to');

As of drop down combo list item: we've discussed it recently, here ("on change trigger to filter").
Re: execute_query between two text items [message #659253 is a reply to message #659232] Tue, 10 January 2017 10:10 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
DECLARE
c_where VARCHAR2(100);
Blk_Id BLOCK;

BEGIN

c_where := 'last_name like '''|| :your block.last_name_search||'%''';

Blk_Id := find_block('your block');
Set_block_Property (Blk_Id , default_where, c_where);
execute_query;

END;

this code not running.

i have F_name(text_item) and table name is employees so what changes will be done???
  • Attachment: ee.JPG
    (Size: 70.50KB, Downloaded 29 times)

[Updated on: Tue, 10 January 2017 10:12]

Report message to a moderator

Re: execute_query between two text items [message #659254 is a reply to message #659253] Tue, 10 January 2017 10:20 Go to previous messageGo to next message
cookiemonster
Messages: 12625
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define not running - not giving the results you expect? giving an error?
Re: execute_query between two text items [message #659255 is a reply to message #659254] Tue, 10 January 2017 10:31 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
in this code cant understand last_name and :your block.last_name__search.
Re: execute_query between two text items [message #659256 is a reply to message #659255] Tue, 10 January 2017 10:32 Go to previous messageGo to next message
zaibi
Messages: 11
Registered: January 2017
Junior Member
can you give me .fmb file which have drop down(combo box)???
Re: execute_query between two text items [message #659261 is a reply to message #659253] Tue, 10 January 2017 12:41 Go to previous messageGo to next message
joy_division
Messages: 4711
Registered: February 2005
Location: East Coast USA
Senior Member
zaibi wrote on Tue, 10 January 2017 11:10
DECLARE
c_where VARCHAR2(100);
Blk_Id BLOCK;

BEGIN

c_where := 'last_name like '''|| :your block.last_name_search||'%''';

Blk_Id := find_block('your block');
Set_block_Property (Blk_Id , default_where, c_where);
execute_query;

END;

this code not running.

i have F_name(text_item) and table name is employees so what changes will be done???

Well, if you really have literal code that says
Blk_Id := find_block('your block');
then it will not work.

I also told you that attached images are not helpful.

What is the use of Blk_Id. Why not just use the block name. I doubt it is an unknown.
Re: execute_query between two text items [message #659273 is a reply to message #659255] Wed, 11 January 2017 03:50 Go to previous message
cookiemonster
Messages: 12625
Registered: September 2008
Location: Rainy Manchester
Senior Member
zaibi wrote on Tue, 10 January 2017 16:31
in this code cant understand last_name and :your block.last_name__search.
So that's sample code you've copied from somewhere else?

last_name would be the name of a column in the table the block is based on.
:your block.last_name_search would be the name of an item in the datablock.

If you don't have columns/items/blocks with those names then you need to change the names to match columns/items/blocks you do have.
Previous Topic: Calendar Issue in multiple records in oracle 10g
Next Topic: Oracle Forms - Enable Disable Textbox Based On Radio Button Selection
Goto Forum:
  


Current Time: Mon Mar 27 07:50:00 CDT 2017

Total time taken to generate the page: 0.10424 seconds