Lov's doesn't display proper records [message #602900] |
Tue, 10 December 2013 04:38 ![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,
When i select LOV and click on find button it displays first record that related column and when i scroll down it displays another column records. For ex:- i select org_code as BRW and click on find it displays related records and next when drag the scroll down or Pg Dn arrow , it selected DET(org_code) records , but i have 300 records on BRW .
For this i have written post-query on DB Block .Please suggest me.
Thank you in advanced.
|
|
|
Re: Lov's doesn't display proper records [message #602921 is a reply to message #602900] |
Tue, 10 December 2013 06:52 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You will need to explain this in a lot more detail (something you should know after 250+ posts).
We don't know what blocks you have in the form or the relationship between them.
We don't know what code is in the find button.
We don't know what code is in the post-query.
It's also not clear what the form is currently doing and what you expect it to do.
|
|
|
Re: Lov's doesn't display proper records [message #602924 is a reply to message #602921] |
Tue, 10 December 2013 07:03 ![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 cookiemonster,
Sorry for that, please find the below code and no relationship between them. HOPE_FORECAST_CONTROL is the control block (Non-DB)
Find:
DECLARE
finalstr VARCHAR2(5000);
dfinalstr VARCHAR2(5000);
BEGIN
finalstr := 'WHERE 1=1';
IF :HOPE_FORECAST_CONTROL.ORG_CODE IS NOT NULL THEN
finalstr := finalstr || 'AND ORGANIZATION_CODE =' ||( ':HOPE_FORECAST_CONTROL.ORG_CODE' );
END IF;
IF :HOPE_FORECAST_CONTROL.FISCAL_YEAR IS NOT NULL THEN
finalstr := finalstr || 'AND FISCAL_YEAR =' || ( ':HOPE_FORECAST_CONTROL.FISCAL_YEAR' );
END IF;
IF :HOPE_FORECAST_CONTROL.ITEM_NUMBER IS NOT NULL THEN
finalstr := finalstr || 'AND ITEM_NUMBER =' || ( ':HOPE_FORECAST_CONTROL.ITEM_NUMBER' );
END IF;
IF :HOPE_FORECAST_CONTROL.PRODUCT_CATEGORY IS NOT NULL THEN
finalstr := finalstr || 'AND PRODUCT_CATEGORY =' || ( ':HOPE_FORECAST_CONTROL.PRODUCT_CATEGORY' );
END IF;
IF :HOPE_FORECAST_CONTROL.PRODUCT_SUB_CATEGORY IS NOT NULL THEN
finalstr := finalstr || 'AND PRODUCT_SUB_CATEGORY =' || ( ':HOPE_FORECAST_CONTROL.PRODUCT_SUB_CATEGORY' );
END IF;
IF :HOPE_FORECAST_CONTROL.CUSTOMER_NAME IS NOT NULL THEN
finalstr := finalstr || 'AND CUSTOMER_NAME =' || ( ':HOPE_FORECAST_CONTROL.CUSTOMER_NAME' );
END IF;
IF :HOPE_FORECAST_CONTROL.SALES_CHANNEL IS NOT NULL THEN
finalstr := finalstr || 'AND SALES_CHANNEL =' || ( ':HOPE_FORECAST_CONTROL.SALES_CHANNEL' );
END IF;
Go_block('HOPE_FORECAST_DATA');
dfinalstr := Get_block_property('HOPE_FORECAST_DATA', default_where);
Set_block_property('HOPE_FORECAST_DATA', default_where, finalstr);
Execute_query();
END;
POST-QUERY
DECLARE
CURSOR c1 IS
SELECT organization_code,
budget_entity,
fiscal_year,
item_number,
product_category,
product_sub_category,
customer_name,
sales_channel
FROM hope.hope_forecast_data
WHERE organization_code = :HOPE_FORECAST_CONTROL.ORG_CODE
OR fiscal_year = :HOPE_FORECAST_CONTROL.FISCAL_YEAR
OR item_number = :HOPE_FORECAST_CONTROL.ITEM_NUMBER
OR product_category = :HOPE_FORECAST_CONTROL.PRODUCT_CATEGORY
OR product_sub_category = :HOPE_FORECAST_CONTROL.PRODUCT_SUB_CATEGORY
OR customer_name = :HOPE_FORECAST_CONTROL.CUSTOMER_NAME
OR sales_channel = :HOPE_FORECAST_CONTROL.SALES_CHANNEL;
BEGIN
OPEN c1;
FETCH c1 INTO
:HOPE_FORECAST_CONTROL.ORG_CODE, :HOPE_FORECAST_CONTROL.BUDGET_ENTITY, :HOPE_FORECAST_CONTROL.FISCAL_YEAR, :HOPE_FORECAST_CONTROL.ITEM_NUMBER,
:HOPE_FORECAST_CONTROL.PRODUCT_CATEGORY, :HOPE_FORECAST_CONTROL.PRODUCT_SUB_CATEGORY, :HOPE_FORECAST_CONTROL.CUSTOMER_NAME, :HOPE_FORECAST_CONTROL.SALES_CHANNEL;
CLOSE c1;
END;
For Ex:- i select org_code(BRW) lov and click on find , it displays first record that are related to org_code(BRW) it is fine, but when i scroll down it displays org_code of 'DET', but i have 200 hundred records are there related to org_code of BRW.
why it shows like that could you please please help me on this please
Please suggest me on this
Thank you
|
|
|
Re: Lov's doesn't display proper records [message #602926 is a reply to message #602924] |
Tue, 10 December 2013 07:13 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
So I take it the db datablock is based on hope_forecast_data?
In which case why does that post-query trigger exist at all?
Execute_query tells the form to issue a select for the DB datablock populate the block with the data.
You then appear to be overwriting that data with the data post-query gets.
Plus the post-query select is just plain wrong.
Say you enter a org_code of BRW and fiscal year of 2013.
The select run by execute_query will only return rows matching both of those conditions.
The select run by post-query will return data that matches either of those conditions.
Just delete the post-query trigger.
|
|
|
|
Re: Lov's doesn't display proper records [message #602931 is a reply to message #602928] |
Tue, 10 December 2013 07:53 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Tue, 10 December 2013 13:34
Quote:Just delete the post-query trigger
The DB Table, it contains all columns of the lov's , that lov columns are from D/F tables and no relationship b/w 2 blocks, means in lov's i wrote d/f tables queries and that all columns are also in the DB Block Table
What does D/F mean?
mist598 wrote on Tue, 10 December 2013 13:34
2)But, when i select fiscal_year and click on find button it displays records but except org_code
Not sure what you mean by that.
mist598 wrote on Tue, 10 December 2013 13:34
& some times mandatatory select both ,
Not sure what you mean by that.
mist598 wrote on Tue, 10 December 2013 13:34
but i want the records only when i select any one of the lov's
or that.
|
|
|
|
Re: Lov's doesn't display proper records [message #602934 is a reply to message #602933] |
Tue, 10 December 2013 08:10 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
mist598 wrote on Tue, 10 December 2013 14:03
Quote:But, when i select fiscal_year and click on find button it displays records but except org_code
Quote:Not sure what you mean by that.
You suggest me that delete the post-query , after that i ran the form and suppose when i select the fiscal_year lov , it displays records except the org_code.
Are you saying the org_code field in the DB block is blank after you execute query?
mist598 wrote on Tue, 10 December 2013 14:03
Quote:& some times mandatatory select both ,
if i am delete the post-query , suppose if select the year lov and click on the find button the cursor point points to the org_code and when i select both & click on find then only it displays records, but i want only when i select any one of the lov's then only to display
I seem to recall asking you about this before and you never answered - is the org_code field in the control block set to required?
|
|
|
|
Re: Lov's doesn't display proper records [message #602938 is a reply to message #602937] |
Tue, 10 December 2013 08:21 ![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) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Assuming org_code is a db item in the db block then you have some code somewhere that's blanking it out. Find it.
As for cursor going to org_code in the header if you haven't supplied a value - again you've got some code doing that to you, find it.
Run the form in debug mode to see what triggers fire in each case.
|
|
|
|
|
|