Oracle help

[Shreya Menon]

All,

We have a report which displays like this

NAME Type COUNT
SETHU APPLES 5
SETHU GRAPES 8
SETHU ONIONS 10
ANAND ORANGES 8
ANAND GRAPES 7
ANAND PEACHES 6
.
..

Basically, the name is getting repeatedly displayed
How can I make it in such a way that it displays Sethu Apples 5
Grapes 6 etc
rather than displaying the name each and every time

We are doing in Oracle 9i please suggest

[ October 29, 2004: Message edited by: Shreya Menon ]

[edited by Jeanne to remove Urgent from subject]
[ October 29, 2004: Message edited by: Jeanne Boyarsky ]

[Jeanne Boyarsky]

Sreya,
I'm moving this to JDBC since it isn't Oracle specific.

[Jeanne Boyarsky]

Sreya,
You need something in that column in the ResultSet. I would recommend using Java to format the report further once you have the result back.

[Srinivasa Raghavan]

Originally posted by Shreya Menon:


Basically, the name is getting repeatedly displayed
How can I make it in such a way that it displays Sethu Apples 5
Grapes 6 etc
rather than displaying the name each and every time

We are doing in Oracle 9i please suggest

Shreya ,
If the output returned by the query is correct then you can use a group by clause.

[Adeel Ansari]

i am worried, if you are using join and not meeting the condition.

if yes then
please use equi-join corretly to join the tables.
else
use group by.

Tip: have a look on group by queries before moving further.

[Shailesh Chandra]

Originally posted by Shreya Menon:
All,

We have a report which displays like this

NAME Type COUNT
SETHU APPLES 5
SETHU GRAPES 8
SETHU ONIONS 10
ANAND ORANGES 8
ANAND GRAPES 7
ANAND PEACHES 6

Shreya ,

I assume that above is the output in your query after group by with name & type.

To display same in java as you expected like

Sethu Apples 5 Grapes 6 ONIONS 10


BUT EVERY TIME TYPES MAY NOT BE SAME... SO OUTPUT MIGHT BE VERY SCATTERED

I am giving you a light idea how to handle it in java



try using HashMap

1. Put the NAME as key of HashMap.
2. Value should be an arrayList of DTO (what I will prefer)

eg:

while(rs.next())
{

MyDTO myDTO = new MyDTO();
myDTO.setName(rs.getString("NAME"));
myDTO.setType(rs.getString("TYPE"));
myDTO.setCount(rs.getInt("Count"));

strName = myDTO.gertName() ;



ArrayLis lst = null;

if(hashMap.containsKey(strName))
{
lst = (ArrayList) hashMap.get(strName);
lst.add(myDTO);

}
else
{
lst = new ArrayList();
lst.add(yourDmyDTOO);
hashMap.put(strName,lst);

}

}
return hashMap;



for displaying result



Iterator it = hashMap.keySet().Iterator();

while(it.hasNext())
{
String Name = (String) it.next();
ArrayList lstReport = (ArrayList) hashMap.get(Name);

out.println("<tr>")
out.println("<td>")
out.println("Name")
out.println("</td>")
for(int i =0; i < lstReport.size();i++)
{
MyDTO myDTO = (MyDTO) lstReport.get(i);
out.println("<td>")
out.println(myDTO.getType())
out.println("</td>")
out.println("<td>")
out.println(myDTO.getCount())
out.println("</td>")

}



}

Hope this help , but i will recommend to handle data at query execution , or verify joins as ADEEL said .
[ October 30, 2004: Message edited by: Shailesh Chandra ]

[Adeel Ansari]

yes Chandra, you are right. its looking like already resulting from group by query.

But i will go towards wrong equi-join, i guess. because these are not looking like the records from the same table. it should be the result of join between two tables.

[Shailesh Chandra]

In your words ......

cent percent agreement

Shailesh

[Shreya Menon]

Hi all,

First of all thanks for the reply.
But I cannot use Java here. this is an existing system which is existing for years. The reports are generated using Oracle SQLPLUS views and procedures. They are spooling the results of Oracle queries and forming reports. Yes they are using group by only.

Please let me know if theres any way to display my results using Oracle rather tnan moving to JAVA ?

Thanks

[Jeanne Boyarsky]

Shreya,
I think Oracle's analytical functions can do something that would be helpful here. I don't remember the details, but it's something you can look into.

I should have left this in Oracle since it turns out that it is Oracle specific I'll move it back there now. (Sorry.)