where clause in the button for Database items [message #624222] |
Fri, 19 September 2014 04:33 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi all,
master relationship blocks are XXC_HISTORY,HISTORY_DETAILS and Details Data Block HISTORY_DETAILS
Join condition HISTORY_DETAILS.HISTORY_ID = XXC_HISTORY.HISTORY_ID
1)XXC_HISTORY
2)HISTORY_DETAILS both are database blocks
and i need to create LOV's from XXC_HISTORY block items , items are Program_Name, Creation_date and need to add FIND button for XXC_HISTORY
Can you please how to create & what code need to be add ?
Please help
|
|
|
|
|
|
|
|
|
|
|
Re: where clause in the button for Database items [message #624267 is a reply to message #624246] |
Fri, 19 September 2014 14:34 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
mist598
1)can i create JOIN Between Non-datebase and database block
Try it and you'll see.
Quote:
I have non-database and database block both are different tables XXC_HISTORY,HISTORY_DETAILS
HISTORY_ID's are the JOIN condition.
Red & blue don't fit together. If one of them is a non-database block and another one is a database block, then they BOTH can't be based on underlying tables - just one can.
Or, if they are based on tables, then both of them ARE database blocks.
Quote:
I need to take LOV's & FIND Button in the non-database block (...) If i select program_name,creation_date (from the LOV's) need to diaplay at the database block
If they are in a non-database block, master-detail relationship won't work (i.e. you can't even create it). It means that you'll have to query the detail block manually, by setting ONETIME_WHERE (using SET_BLOCK_PROPERTY) which would use values you select by LoVs. And, of course, EXECUTE_QUERY.
|
|
|
Re: where clause in the button for Database items [message #624274 is a reply to message #624222] |
Sat, 20 September 2014 00:47 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi ,
1)It is custom form
2) XXC_HISTORY,HISTORY_DETAILS 2 database blocks
3)having master detail realtionship with HISTORY_ID column's on the both tables
4) need to create LOV on program_name,creation_date columns on the XXC_HISTORY database block and also add one find button in the same block
5) if i click on the FIND Button need to display data on HISTORY_DETAILS block
and columns are HISTORY_ID,TABLE_NAME,CREATION_DATE
Created LOV on the program_name,creation_date columns on the XXC_HISTORY database block, the LOV's showing like a "display Item" see the below picture
![/forum/fa/12176/0/](/forum/fa/12176/0/)
Please help me how to do this?
-
Attachment: image1.png
(Size: 0.89KB, Downloaded 942 times)
[Updated on: Sat, 20 September 2014 00:48] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
Re: where clause in the button for Database items [message #624335 is a reply to message #624290] |
Mon, 22 September 2014 01:55 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/a59247482a11edb9544247f102223e8d?s=64&d=mm&r=g) |
mist598
Messages: 1195 Registered: February 2013 Location: Hyderabad
|
Senior Member |
|
|
Hi Littlefoot..
DECLARE
finalstr VARCHAR2(5000);
dfinalstr VARCHAR2(5000);
CURSOR c1
IS
SELECT DISTINCT id
FROM xxc_history
WHERE name = :block.name
AND trunc(creation_date) BETWEEN :block.date_from AND :block.date_to;
BEGIN
FOR c_rec IN c1 LOOP
IF c_rec.id IS NOT NULL THEN
v_id := c_rec.id;
finalstr := 'WHERE 1=1';
finalstr := finalstr ||' AND id = ' ||v_id;
Go_block('xxc_DETAILS');
dfinalstr := Get_block_property('xxc_DETAILS', default_where );
Set_block_property('xxc_DETAILS', default_where, finalstr) ;
Execute_query();
END IF;
END LOOP;
END;
I have written the above code into the FIND Button ,From the cursor between 2 dates i am getting 22 records , but after clicked on the Button i am getting only 9 Records
can you please help me what is the issue in my code?
[Updated on: Mon, 22 September 2014 01:56] Report message to a moderator
|
|
|
Re: where clause in the button for Database items [message #624339 is a reply to message #624335] |
Mon, 22 September 2014 03:29 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
A cursor C1 finds 22 distinct IDs from XXC_HISTORY table.
If EXECUTE_QUERY returns only 9 rows into a form block, it means that XXC_DETAILS table doesn't contain records related to all IDs from the XXC_HISTORY table.
|
|
|