Home » SQL & PL/SQL » SQL & PL/SQL » ORACLE PL/SQL QUERIES
ORACLE PL/SQL QUERIES [message #265114] Wed, 05 September 2007 08:02 Go to next message
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 #265116 is a reply to message #265114] Wed, 05 September 2007 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially the section about IM speak.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: ORACLE PL/SQL QUERIES [message #265138 is a reply to message #265116] Wed, 05 September 2007 09:12 Go to previous message
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
Previous Topic: Package Security....
Next Topic: Count difference
Goto Forum:
  


Current Time: Thu Feb 06 23:15:26 CST 2025