Home » Developer & Programmer » Forms » Dynamically passing where condition
| Dynamically passing where condition [message #514438] |
Mon, 04 July 2011 13:44  |
 |
goparaju
Messages: 54
Registered: June 2011
|
Member |
|
|
Hi Experts,
I am working on 6i reports.I have below scenario to achieve.
I have FIND window and MAIN Window.
FIND Window: contains EMPNO, ENAME,and FIND button.
MAIN WINDOW: EMPNO, ENAME, SAL, JOB
If i enter something in FIND window, by clicking FIND button, it will populate data in MAIN window.I have below query in WHEN BUTTON PRESSED TRIGGER.
declare
cursor c1 is select empno, ename, job, sal from emp
where empno=nvl(:FIND.empno,empno)
and ename=nvl(:FIND.ename,ename)
begin
--here i am populating data into MAIN window block
end;
It's working fine.
REQUIREMENT: i want to pass where statement dynamically.
Let us say if i pass only empno in FIND window, i want to pass 'WHERE empno=:FIND.empno' to existing select statement [I will keep select statement same without where condition]
If i pass empno and ename both, i want to pass 'WHERE empno=:FIND.empno and ename=:FIND.ename.
Could somebody help me how to pass where condition to existing select statement based on parameters we pass in FIND window.
Kindly help.
Thanks in advance.
|
|
|
|
| Re: Dynamically passing where condition [message #514444 is a reply to message #514438] |
Mon, 04 July 2011 15:17  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As far as I understood, you'd like to display the same result (records) in both form and report. Therefore, you'd like to pass form's WHERE clause to the report. If that's so, you could use GET_BLOCK_PROPERTY built-in and its LAST_QUERY property. Also, check SYSTEM.LAST_QUERY system variable.
Don't just "blindly" pass its value to the report - display it in the form, first, so that you'd see what it really contains. You might need to adjust it a little bit. Somewhere in the back of my mind there's the "255 characters limit" (i.e. if the WHERE clause is longer than that, you might need to split it to several pieces, pass all of them to the report, put them back together in the report and, finally, use it.
Anyway: once you manage to pass it successfully, the simplest way to use it in the report is a lexical parameter. If you didn't use it yet, check Reports Help - it's described in there. Also, search this board - I created an example or two previously.
|
|
|
|
|
|
| Re: Dynamically passing where condition [message #514475 is a reply to message #514468] |
Tue, 05 July 2011 01:11 |
|
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
You mistyped it, I misunderstood it.
It appears that you are filling the MAIN block manually (using a cursor loop you mentioned). Why is that so? It would be much easier if you kept it a data block and simply executed a query. There would be no need for "dynamic where conditions" or whatever - you'd just (in PRE-QUERY trigger) set:main.empno := :find.empno;
:main.ename := :find:ename;
Or, if you insist on some more typing, you'd set MAIN block's ONETIME_WHERE (or, possibly, DEFAULT_WHERE) property using SET_BLOCK_PROPERTY built-in.
What you are saying is that you'd want to create a WHERE condition dynamically and append it to cursor's SELECT statement. Well, as far as I can tell, that's not easy. In Reports, you'd use lexical parameter. I don't know what to use in Forms. From my point of view, "dynamically" here stands for "too complex" and, if I were you, I'd either leave it as it is (really, if it works OK, why fixing something that isn't broken?) or redesign a form so that it works as I explained at the beginning of this message.
|
|
|
|
| Re: Dynamically passing where condition [message #514491 is a reply to message #514475] |
Tue, 05 July 2011 03:04 |
|
goparaju
Messages: 54
Registered: June 2011
|
Member |
|
|
Hi,
Thanks for your reply. My FIND window gets data from some other table, not from emp, and populates data into MAIN window EMP table block.
My problem is, i have 10 nvls in WHERE condition.If i don't pass parameters, the query is taking very long time because of NVL functions.For that, i thought of passing where condition dynamically,so that it will improve performance.
As per your suggestion, passing where condition dynamically is not advisable. Could you suggest me to improve the performance my query, instead of using NVL, can i use something else.
Below is my actually query which triggers when i press the FIND button in FIND window.
SELECT ooha.order_number
,ooha.cust_po_number
,oola.line_number
,we.wip_entity_name
FROM oe_order_headers_all ooha
,oe_order_lines_all oola
,mtl_system_items_b msib
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='ALLOY')a
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='GRADE')b
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='FORM') c
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='FINISH')d
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='TEMPER')e
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='DIM1')f
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='DIM2')g
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='DIM3')h
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='DIM4')i
,wip_reservations_v wrv
,wip_entities we
WHERE ooha.header_id=oola.header_id
AND oola.ordered_item=msib.segment1
AND msib.inventory_item_id=a.inventory_item_id
AND a.inventory_item_id=b.inventory_item_id
AND a.inventory_item_id=c.inventory_item_id
AND a.inventory_item_id=d.inventory_item_id
AND a.inventory_item_id=e.inventory_item_id
AND a.inventory_item_id=f.inventory_item_id
AND a.inventory_item_id=g.inventory_item_id
AND a.inventory_item_id=h.inventory_item_id
AND a.inventory_item_id=i.inventory_item_id
AND ooha.order_number=wrv.segment1
AND wrv.wip_entity_id=we.wip_entity_id
AND a.element_name=NVL(:ALLOY,a.element_name)
AND b.element_name=NVL(:GRADE,b.element_name)
AND c.element_name=NVL(:FORM,c.element_name)
AND d.element_name=NVL(:FINISH,d.element_name)
AND e.element_name=NVL(:TEMPER,e.element_name)
AND f.element_name=NVL(:DIM1,f.element_name)
AND g.element_name=NVL(:DIM2,g.element_name)
AND h.element_name=NVL(:DIM3,h.element_name)
AND i.element_name=NVL(:DIM4,i.element_name)
GROUP BY ooha.order_number
,ooha.cust_po_number
,oola.line_number
,we.wip_entity_name;
User may or may not pass parameters, if parameters are not passed, it will scan the entire table,query is taking long time.
Any other solution which acts like NVL and gives better performance.
Thanks.
CM: added [code] tags
[Updated on: Tue, 05 July 2011 03:46] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Dynamically passing where condition [message #514504 is a reply to message #514499] |
Tue, 05 July 2011 04:05 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Couple of points:
1) Can you please read and follow: How to use [code] tags and make your code easier to read?
2) Looking at the full query I would suggest your main problem is the data model - it's not third normal form. grade, finish, temper etc should all be columns in the same row. If they were it'd improve things immensely.
3) If you can't change the data model then you probably do need to do things dynamically, but you should change the from as well as the where.
So if the user hasn't entered a grade you should remove inline view b entirely from the query. Likewise for the others c to i.
|
|
|
|
| Re: Dynamically passing where condition [message #514509 is a reply to message #514504] |
Tue, 05 July 2011 04:16 |
cookiemonster
Messages: 13959
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Is that query even correct?
,(select inventory_item_id,element_name from mtl_descr_element_values where element_name='GRADE')b
.........
AND b.element_name=NVL(:GRADE,b.element_name)
Only way that's going to find any records is if the user enters the value 'GRADE' in :grade or leaves it blank.
Likewise :form must be 'FORM' or blank, and so on.
Seems wrong to me. Think you're missing a column - element_value perhaps?
|
|
|
|
| Re: Dynamically passing where condition [message #514757 is a reply to message #514438] |
Wed, 06 July 2011 05:47 |
dude4084
Messages: 222
Registered: March 2005
Location: Mux
|
Senior Member |
|
|
Hi here is a idea for you.
Your original query
select empno, ename, job, sal from emp
where empno=nvl(:FIND.empno,empno)
and ename=nvl(:FIND.ename,ename)
Define a query string variable like this
query_string := 'select empno, ename, job, sal from emp
where 1=1'
now check values of :Find.empno and :Find.ename (and more) as follow
if :FIND.empno is not null then
query_string := query_string || ' and empno=:FIND.empno'
end if;
if :FIND.ename is not null then
query_string := query_string || ' and ename=:FIND.ename'
end if;
Your SQL string is complete which you can check by
Message(query_string);
pause;
Now you can execute query_string by ???? method.
??? = research needed by me 
Good Luck
--Dude
|
|
|
|
Goto Forum:
Current Time: Thu Nov 28 15:53:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
