Solved

Compare row values - oracle 9 sql

Posted on 2008-10-28
7
469 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
Industry Leaders: 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

685 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