This week's book giveaway is in the OCMJEA forum.
We're giving away four copies of OCM Java EE 6 Enterprise Architect Exam Guide and have Paul Allen & Joseph Bambara on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes sql Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "sql" Watch "sql" New topic
Author

sql

phani ravinutala
Greenhorn

Joined: Jun 25, 2009
Posts: 26


I have a table log_tbl with column EMP_RESULT




I want to display the output below:




Can anyone help me to query the above table

thanks

John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Kal,

how much do you know about sql?

As a hint: try combining substr() and count(*)/group by:

substr

count(*)
Group Functions



John
phani ravinutala
Greenhorn

Joined: Jun 25, 2009
Posts: 26
Here is my query




I got the below output:


But I want the below output:





Can any one help me to modify my above query?

Thanks
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Kal,

you can try this:



If the length of the part you want to group by varies you can e.g.try to use INSTR to find the positionof the "ID:".


John
phani ravinutala
Greenhorn

Joined: Jun 25, 2009
Posts: 26
Thanks for your reply.

I have one more question suppose, if i have two tables deptlog_tbl and log_tbl log_tbl with data like below


deptlog_tbl




How would i query to join the two tables to get the below output?




thanks





John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Kal,

first a short querstion: Is this a kind of legacy db your're working with?

If not:

It's a little strange to have a table with only one column where the data is structured by "tags".
For your problem it would be much easier if your log_tbl would look like this:

DEPT_CODE NUMBER(4) NOT NULL
MSG VARCHAR2(40) NOT NULL
TRACKING_ID VARCHAR2(200)

where DEPT_CODE and MSG are the foreign key to deptlog_tbl.


But let's work with the structure you described in your post.


First of all I do not think you're really joining the two tables, at least I can't see anything in your result which you can't select directly from table log_tbl.

Without joining the problem looks pretty much like the one from the beginning of this thread. Maybe a problem could be that some strings are shorter than the substring you are using.


You should have a look at the Oracle string functions:

Oracle String Functions

The result maybe something like this:



I hope I did understand what you want to do...


John
 
 
subject: sql