ORACLE PL/SQL QUERIES [message #265114] |
Wed, 05 September 2007 08:02  |
darr
Messages: 1 Registered: September 2007 Location: trinidad
|
Junior Member |
|
|
i have created a form with six fields i want the user to be able to query by either one or more or even a combination of fields. I cannot use the default query because i want the query to return all the data into a grid view i created in a separate data block and return a count as well.
what i am trying to accomplish is to select distinct records and group them together and return a count as well, so every record with the same information is grouped together and there is a counter showing the number of similar records so when i update one record all the related records gets updated.
john doe 25 records
jane doe 1 record
bob james 105 records
the problem that i am having is with the WHERE CLAUSE, if i have to write the SELECT statement for each field then it is too much because the user can query by any possible combination.
I created a query string which checks to see which field has data and create a final string based on the fields with data which then queries the database and returns the values perfectly but it does not populate the grid view properly so i am now using a CURSOR but i cannot use the query string created in this cursor does not work can anyone tell me how i can accomplish the task using the CURSOR. i need to query by any field or any combination of fields there are 6 fields. i need a simple WHERE CLAUSE.
or i dont know if late binding can work there? if it can plz let me knw asap
Best Regards
Darr
|
|
|
|
Re: ORACLE PL/SQL QUERIES [message #265138 is a reply to message #265116] |
Wed, 05 September 2007 09:12  |
 |
Soumen Kamilya
Messages: 128 Registered: August 2007 Location: Kolkata
|
Senior Member |

|
|
I have a solution but not too good logic.
First choose the maximum rowid of that record which is group by.
Then update these records using rowid.
This looks like
For data:
create table test as
(select 1 col1, 'soumen' col2 from dual
union all
select 1 col1, 'soumen' col2 from dual
union all
select 1 col1, 'soumen' col2 from dual
union all
select 2 col1, 'jay' col2 from dual
union all
select 2 col1, 'jay' col2 from dual);
For retriving the data:
select * from(
select col1,col2,(select max(rowid) from test b where a.col1=b.col1 and a.col2=b.col2) rid
from test a)
group by col1,col2,rid
for Updating the data:
update test set col1=4
where (col1,col2) in(select * from test where rowid='&RID');
Cheers
Soumen
|
|
|