| Query Process Help [message #351947] |
Fri, 03 October 2008 15:49  |
greendba
Messages: 22
Registered: September 2008
Location: Toronto
|
Junior Member |
|
|
Hello...
I've built an app that contains a report search tool.
You punch in your search criteria in the search fields, hit submit and it produce results.
The results are produced with the following pl/sql quoted at the end of the message. This element works fine.
I also have a but to clear the search fields... that's no problem as well.
My problem is that I would like to include a button "SHOWALLRECORDS" that, when clicked will query table and return all results. I've tried to incorporate this into the PL/SQL below with no luck.
Any suggestions would be appreciated.
Thank You
PL/SQL BELOW
DECLARE
v_p2_searchgeoid varchar2(200);
v_p2_searchwater varchar2(200);
v_p2_searchstreet varchar2(200);
query VARCHAR(32767);
wexists BOOLEAN;
BEGIN
query := 'select * from "AWBINORDER" '
;
wexists := false;
v_p2_searchgeoid := NULL;
v_p2_searchwater := NULL;
v_p2_searchstreet := NULL;
If :REQUEST in ('SHOWALLRECORDS') then
query := query;
end if;
if v('P2_SEARCHSTREET')is null then
v_p2_searchstreet := NULL;
ELSIF (wexists) THEN
v_p2_searchstreet := ' AND STREET_NAME like ' || chr(39) || upper(Trim(:P2_SEARCHSTREET)) || '%' || chr(39) ;
else v_p2_searchstreet := ' awbinorder.stname = :P2_SEARCHSTREET ';
wexists := true;
end if;
--
if v('P2_SEARCHGEOID')is null then
v_p2_searchgeoid := NULL;
ELSIF (wexists) THEN
v_p2_searchgeoid := ' AND GEO_ID like ' || chr(39) || upper(Trim(:P2_SEARCHGEOID)) || '%' || chr(39) ;
else v_p2_searchgeoid := ' awbinorder.GEO_ID = :P2_SEARCHGEOID ' ;
wexists := true;
end if;
--
if v('P2_SEARCHWATER')is null then
v_p2_searchwater := NULL;
ELSIF (wexists) THEN
v_p2_searchwater := ' AND WATERACCTNUM like ' || chr(39) || upper(Trim(:P2_SEARCHWATER)) || '%' || chr(39) ;
else v_p2_searchwater := ' awbinorder.wateracctnum = :P2_SEARCHWATER ';
wexists := true;
end if;
--
IF (wexists) THEN
query := query || ' WHERE ' || v_p2_searchgeoid || v_p2_searchwater || v_p2_searchstreet;
else query := query || ' WHERE awbinorderuid is null ';
END IF;
return query;
END;
|
|
|
|
|
|
| Re: Query Process Help [message #352204 is a reply to message #351966] |
Mon, 06 October 2008 10:41  |
greendba
Messages: 22
Registered: September 2008
Location: Toronto
|
Junior Member |
|
|
Apologies for the rule violation.
I managed to trace the problem. It was PL/SQL coding order and then linking the request to the a branch forcing the page to reload.
Thanks to all who assisted.
Cheers
Eric
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
