Solved

Compare row values - oracle 9 sql

Posted on 2008-10-28
7
474 Views
Last Modified: 2013-12-19
Please see the attached displaying - 1. the current table data and - 2. the desired output.

This data is looking at clients, assessments undertaken on them, and answers given in these assessments.

If a clients set of answers is replicated on another clients answers, I'd like the set of answers to only appear once and the clients numbers to be listed.

in this example, the question is asking about the clients ethnicity - there are 4 separate answers to the question - START_DATE, END_DATE, CAT_DESC and NOTES. As you can see in the answers given for clients 147 and 8456 in the AVD_DATA (answers column) matches so the clients answers only appear once.

I'm not sure if this is possible. Any help is appreciated.
Answers-Report-1-.xls
0
Comment
Question by:tonMachine100
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 5

Assisted Solution

by:jfmador
jfmador earned 150 total points
ID: 22821324
Hello,

You will need to use aggregation function on your clientId, to do it you will need to convert your client number as a varchar and create an aggregation function under oracle because there is no built-in function to do string aggregation

You can take a look to this web page there is few examples about how to implement it

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php
0
 
LVL 3

Expert Comment

by:richard_crist
ID: 22821961
The EE link below also discusses your question.  It has several comments and a reference to another EE link.  Between jfmador's link and the EE one you may be able to find something that works in your situation.  If you do please accept jfmador's comment, as that is what led me to the EE link (because his comment led to my searching some more).

jfmador, thanks for you comment.  I will find this discussion very useful in the future.  Also, thank you tonMachine100 for bringing this up.   :)
0
 
LVL 5

Expert Comment

by:jfmador
ID: 22822019
Hey Richard you forget to leave your EE link

is it this one ?
http://www.experts-exchange.com/Database/Oracle/9.x/Q_23835552.html
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:richard_crist
ID: 22822047
Sorry!   :P

It is http://www.experts-exchange.com/Databases/Oracle/Q_21429673.html

Thanks for the catch!   :)

0
 
LVL 27

Accepted Solution

by:
sujith80 earned 250 total points
ID: 22828731
Try this.

create or replace function test_func(p_QST_TYPE tbl1.QST_TYPE%type,
                                     p_QST_CODE tbl1.QST_CODE%type,
				     p_AVD_DATA tbl1.AVD_DATA%type,
				     p_QST_DESC tbl1.QST_DESC%type,
				     p_ANV_NAME tbl1.ANV_NAME%type,
				     p_ANV_ID   tbl1.ANV_ID%type)
return varchar2
as
 l_ret_str varchar2(200);
begin
 for rec in(select distinct to_char(client_id) cl_id from <your table>
            where QST_TYPE = p_QST_TYPE
	    and   QST_CODE = p_QST_CODE
	    and   AVD_DATA = nvl(p_AVD_DATA, AVD_DATA)
	    and   QST_DESC = p_QST_DESC 
	    and   ANV_NAME = p_ANV_NAME
	    and     ANV_ID = p_ANV_ID ) loop
  l_ret_str := l_ret_str ||','|| rec.cl_id;
 end loop;
 return ltrim(l_ret_str, ',');
end;
/
 
select test_func(QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID) ids, QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID
from <your table>
group by QST_TYPE, QST_CODE, AVD_DATA, QST_DESC, ANV_NAME, ANV_ID
order by 1;

Open in new window

0
 

Author Closing Comment

by:tonMachine100
ID: 31510693
Thankyou both for your help.
Do you think the same output could be derived from just using sql (ie no pl)? I'm no expert with pl, so i'd find a solution in just sql easier to understand!
thanks again.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22833104
It can be done with sql. Not a direct one, but with some tricky stuff in it. Using hierarchical queries and stuff.
But the sql will get too huge as there are many join columns, which makes it much difficult to maintain the code. This is neater.
0

Featured Post

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question