| Selecting and passing values for a query [message #474443] |
Mon, 06 September 2010 07:50  |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
hi guys,
I have a form in which the user selects values above from a LOV and then selects a run query button which then uses the values selected in the LOV to refine the query and output the appropriate values.
What i have done in the pre-query trigger is: (the block i am querying is called cars where the block i am selecting values from the LOV is called selection)
:cars.make := :selection.make; (repeated for other fields)
this worked fine untill i introduced a from and to date in my form in which i passed the data through to the query like this:
declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;
Now i have added the above bit of code the from and to date work fine, but when a car make is selected it still bring back every car make and not just the one selected.
Anyone know what went wrong or how to fix this? is it possibly because i am setting the where clause in the block property? perhaps i need to now incoperate my other selections into this where clause?
are the two blocks exclusive to eachother? and if i put the whole code into the block property I assume i will get problems. E.G. the user leaves a selection criteria blank (if they want to query all car models they wouldnt select one from the list) i assume the query would only return back values which contain no car model which would be 0 records.
Any advice is appreciated.
|
|
|
|
|
|
|
|
| Re: Selecting and passing values for a query [message #474447 is a reply to message #474443] |
Mon, 06 September 2010 08:24  |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
in my pre-query trigger. my trigger looks like this:
declare
v_dates_between varchar2(1000);
begin
v_dates_between := 'reg_date between ''' || :selection.from_date || ''' and ''' || :selection.to_date;
Set_Block_Property( 'cars', DEFAULT_WHERE, v_dates_between ) ;
:cars.make := :selection.make;
|
|
|
|
| Re: Selecting and passing values for a query [message #474448 is a reply to message #474447] |
Mon, 06 September 2010 08:41 |
cookiemonster
Messages: 13938
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ought to work. If it was the other way around it might not.
Pre-query should work like enter-query mode, whatever values you assign in it should be appended to the where clause.
Try using get_block_property(<block_name>, last_query) to check exactly what forms is sending to the server.
|
|
|
|
| Re: Selecting and passing values for a query [message #474451 is a reply to message #474448] |
Mon, 06 September 2010 09:37 |
ahazin
Messages: 39
Registered: August 2010
|
Member |
|
|
Thanks, I done as you suggested and the query appears to be correct but it seems to bring back the same 3 records no matter what is input, (even though they dont match the critera being enterd, so the problem must lie else where). I will try to figure out what it is now.
WHERE standard_date between '06-SEP-10' and '04-OCT-10' and ( UPPER(MAKE) = 'TEST' and (MAKE LIKE 'te%' or MAKE LIKE 'tE%' or MAKE LIKE 'Te%' or MAKE LIKE 'TE%'))
and it alwyas brings back the same 3 models which dont even begin with T.
Thank You
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
