Solved

Compare row values - oracle 9 sql

Posted on 2008-10-28
7
461 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
  • 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

856 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