OraFAQ Forum: Forms » how to write select Query in the Button to display data into the another block

Home » Developer & Programmer » Forms » how to write select Query in the Button to display data into the another block

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

how to write select Query in the Button to display data into the another block [message #624649]

Wed, 24 September 2014 07:56

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

Button_1
---------
Go_Block('Dept');
--need to write select query
select deptno from dept;
execute_query();

Dept block
----------
In the canvas(take as normal text)

deptno
-----
10
20
30
40

Button_2

if Button_2 clikced on the 10 row and clicked on the button_2

then go_block('emp');
--display 10 deptno data(records) only

and

How to implement this Query in the Button_2 and display data into the EMP Block
SELECT *
FROM   (SELECT a.ID,
               b.table_name,
               b.creation_date,
               RANK() OVER (PARTITION BY b.table_name ORDER BY b.creation_date desc) as date_ranking
         FROM  xxc_history a,
                    xxc_history_details b
        WHERE  a.ID = b.ID
               AND name = ' Data Program')
WHERE   date_ranking = 1

Assume in place of xxc_history take Dept table and xxc_history_details tale EMP table

[Updated on: Wed, 24 September 2014 07:58]

Report message to a moderator

Re: how to write select Query in the Button to display data into the another block [message #624683 is a reply to message #624649]

Wed, 24 September 2014 10:21

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

Since you are using Analytic Functions (AF) in your query, you can't easily execute this query in Forms because the Forms PL/SQL Engine doesn't support AF. However, if you were to make your EMP block based on a From Clause Query (FCQ) then you could use your AF in the query because the FCQ query is stored as a string in Forms and sent to the database to be parsed and executed - hence allowing you to use your AF in the query. There are numerous examples on the internet on how to base a block on a FCQ - a simple Google search on "Oracle Forms how to from clause query" will give you numerous examples.

The alternative to using a FCQ would be to create a view in your database and then base your block on the view.

Hope this helps.

Craig...

Report message to a moderator

Re: how to write select Query in the Button to display data into the another block [message #624748 is a reply to message #624683]

Thu, 25 September 2014 04:27

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Thanks for reply..

And another Requirement

Conditions:-
1) when i double clicked on the Item Value and it navigates to the Another Block & display the deptno=10's data only
Example:-
if i double click on the deptno 10 value then need to display that 10th dept data only

And

2) if i select(text_item only) deptno=10 value and clicked on the FIND Button , need to pass(dynamically in the Find Button Query) and display that 10th department related data only

Example if select 10 then need to pass that value like a parameter in the FIND Query

select * from (select * form emp
               where deptno=:BLOCK.deptno);

How to do this please help me?

[Updated on: Thu, 25 September 2014 07:17]

Report message to a moderator

Re: how to write select Query in the Button to display data into the another block [message #624773 is a reply to message #624748]

Thu, 25 September 2014 08:50

CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA

Senior Member

It seems to me that you are making this harder than it needs to be. If I remember correctly (it's been a little while since I've needed to use a FCQ block), you should be able to create a "Relationship" between your Parent block (the FCQ based) and your Child block, so long as the child block is a Based Table block. When you navigate to the desired record in your Parent block - the relationship will automatically display the corresponding child record; no code needed! Smile

If your Child block is not a base table block, then you would need to write code in a When-Mouse-Doubleclick (WMD) trigger and your Find Button's When-Button-Pressed (WBP) trigger to alter the query of the child block to display records WHERE deptno = 10. Since you need the same code in more than one trigger, I recommend you create a Forms Program Unit (Procedure) that does all the work and simply call this procedure from your two triggers. Your code will look something like:

/* Code is untested - for demonstration purposes only */
/* Code is based on using Forms 10g - since you didn't list your version. */
PROCEDURE Display_Department (p_dept_no NUMBER) IS
   v_sql   VARCHAR2(2000);
BEGIN
   IF ( p_dept_no IS NULL ) THEN 
      Clear_Message;
      Message('You must provide a Department Number.');
      Message(' ');
      RAISE Form_Trigger_Failure;
   END IF;

   GO_BLOCK('DEPARTMENT');
   v_sql := 'deptno = '||P_DEPT_NO;
   Set_Block_Property('DEPARTMENT', ONETIME_WHERE, v_sql);
   Execute_Query;
END Display_Department;

Now, in your WMD and WBP triggers you simply call the "Display_Department" procedure.

Craig...

Report message to a moderator

Re: how to write select Query in the Button to display data into the another block [message #624808 is a reply to message #624773]

Fri, 26 September 2014 00:10

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi Craig,

1)History(Parent), History_details(child) are the blocks

2)in the History block i have written "From clause Query" like select distinct name form table
3)And i had FIND Button in the History Block , navigates to the History_details and display the records on that Block

Conditions:-
1) if i doubled click on the History records(name column values) then go to the History_details Block and display that Name column value records.

Assume i am clicking on the 10 and display the 10 related records only

And Also

2) Select name column value , let say 10 and clicked on the FIND Button , display that particular Record.

And also i need to add below code in the FIND Button

select * from (select * form xxc_history_details
               where deptno=:History.name);

Thanks in advanced

Report message to a moderator

Previous Topic:	oracle forms 6i schema builder not working with 10g database
Next Topic:	combobox - jump to item of a big list

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 11:09:19 CDT 2024