Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compare row values - oracle 9 sql

Posted on 2008-10-28
7
Medium Priority
?
483 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 600 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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 1000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

661 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