ORCL - Pivot

Sometime users request converting multiple rows to columns for same ID, like the following:

with l as

 (select '333' id, 'email1' email, 1 xsequence  from dual

  union  select '333' id, 'email2' email, 2 xsequence  from dual

  union  select '333' id, 'email3' email, 3 xsequence   from dual)

select * from l;

333 email1 1

333 email2 2

333 email3 3

Sometimes users are OK with comma concatenated like this (with listagg function):

333 email1,email2,email3 (one column)

And most time users want like this: 

333 email1 email2 email3 (3 columns)

So in order to get that, I used to do an old way: first rank() them and doing case then group max, like the following:

select n.id, max(email1) e1, max(email2) e2, max(email3) e3

  from (select m.id,  case  when rn = 1 then m.email  end email1,

               case when rn = 2 then m.email end email2,

               case when rn = 3 then m.email end email3

          from m) n  group by id

By using Pivot, it will cut the CASE and MAX, just like this:

select *  from m pivot (max(email) for rn in(1, 2, 3))

with l as

 (select '333' id, 'email1' email, 1 xsequence

    from dual

  union

  select '333' id, 'email2' email, 2 xsequence

    from dual

  union

  select '333' id, 'email3' email, 3 xsequence

    from dual),

m as

 (select distinct l.id, l.email,

                  rank() over(partition by l.id order by l.xsequence) rn  

    from l)

select *  from m pivot (max(email) for rn in(1, 2, 3))

If you want to select them for other joined query, you could set the column alias to get rid of the number(rn), since you can not pick/select the column started with numbers.

select *   from m

pivot (max(email) for rn in(1 as e1, 2 as e2, 3 as e3))

I made a video to show the process, thanks for watching!

You can online search for Pivot documentation and examples.

"Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.

In this syntax, following the PIVOT keyword are three clauses:

• pivot_clause specifies the column(s) that you want to aggregate. The pivot_clause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivot_in_clause.
• pivot_for_clause specifies the column that you want to group or pivot.
• pivot_in_clause defines a filter for column(s) in the pivot_for_clause. The aggregation for each value in the pivot_in_clause will be rotated into a separate column."

ORCL - MATCH_RECOGNIZE

From time to time, collages ask me about doing max consecutive count, let's say we want to know a donor most continuous donation year total, we used to do the hard way* until we tried using the match_recognize ().

Courtesy to https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1, my favorite Oracle knowledge website.

"Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible."

So here is the new baby:))

select max(runs)
from
(select *  
from   (select distinct g.gift_donor_id, g.gift_year_of_giving from gift g where g.gift_donor_id = '00000xxxxx')
match_recognize ( 
  order by gift_year_of_giving 
  measures  
    first(gift_year_of_giving) as first_run, 
    last(gift_year_of_giving) as last_run, 
    count(*) as runs, 
    match_number() as grp 
  pattern ( strt consecutive* ) 
  define  
    consecutive as gift_year_of_giving = ( prev (gift_year_of_giving ) + 1 ) 
))

From Oracle:

What does this query do? The following explains each line in the MATCH_RECOGNIZE clause:

• PARTITION BY divides the data from the Ticker table into logical groups where each group contains one stock symbol.
• ORDER BY orders the data within each logical group by tstamp.
• MEASURES defines three measures: the timestamp at the beginning of a V-shape (start_tstamp), the timestamp at the bottom of a V-shape (bottom_tstamp), and the timestamp at the end of the a V-shape (end_tstamp). The bottom_tstamp and end_tstamp measures use the LAST() function to ensure that the values retrieved are the final value of the timestamp within each pattern match.
• ONE ROW PER MATCH means that for every pattern match found, there will be one row of output.
• AFTER MATCH SKIP TO LAST UP means that whenever you find a match you restart your search at the row that is the last row of the UP pattern variable. A pattern variable is a variable used in a MATCH_RECOGNIZE statement, and is defined in the DEFINE clause.
• PATTERN (STRT DOWN+ UP+) says that the pattern you are searching for has three pattern variables: STRT, DOWN, and UP. The plus sign (+) after DOWN and UP means that at least one row must be mapped to each of them. The pattern defines a regular expression, which is a highly expressive way to search for patterns.
• DEFINE gives us the conditions that must be met for a row to map to your row pattern variables STRT, DOWN, and UP. Because there is no condition for STRT, any row can be mapped to STRT. Why have a pattern variable with no condition? You use it as a starting point for testing for matches. Both DOWN and UP take advantage of the PREV() function, which lets them compare the price in the current row to the price in the prior row. DOWN is matched when a row has a lower price than the row that preceded it, so it defines the downward (left) leg of our V-shape. A row can be mapped to UP if the row has a higher price than the row that preceded it.

*Hard way:):

with years as
(
select distinct g.id, g.giving_year
from gift, allocation a
where g.gift_associated_allocation = a.allocation_code and a.athletics_ind = 'Y'--upper(a.short_name) like '%ATHLETICS%'
order by g.id, g.gift_year
)
select id,   ayear from_yr, byear to_year, yearcon
from
(
  select
    a.id,
    a.year ayear,
    b.year byear,
    (b.year - a.year)+1 yearcon,
    dense_rank() over (partition by a.id order by (b.year - a.year) desc) rank
  from
    years a
    join years b on a.id = b.id and b.year > a.year
  where
    b.year - a.year =
      (select count(*)-1
         from years a1
        where a.id = a1.id
             and a1.year between a.year and b.year)
)
where rank = 1 and yearcon>=10
order by 4 desc

ORCL - CONTINUEIF

About 17 years ago, my 1st IT job is maintaining a sql load program for hospital medical records at Alberta Health. Today we have an imodule routine to fetch the records and load into database.

My colleague had some trouble with the data with embedded line breaks before ending the recordes, and ask me if I could help. I could not help to jump on my "first love" :)) special feeling about sqlloader:))

sqlloader offer two possibilities :  - courtesy to http://gerardnico.com/wiki/!

• if you have a special character at the end of each line: the stream record format

Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line. The newline is no longer special.

• if you don't have a special character at the end of each line: the CONTINUEIF statement.

Here is the sample data for us:

"100045678","address is
super street
18999 Edmonton Alberta
T6R4Y3" ............................................
"2","the degree is MBA
UT
1998" ............................................

 

The each field inside data looks enclosed with double quotes "xxx", so we could simple add the CONTINUEIF to control the end of records.

 

From Oracle:

Using CONTINUEIF to Assemble Logical Records

Use CONTINUEIF if the number of physical records to be combined varies. The CONTINUEIF clause is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if a pound sign (#) were in byte position 80 of the first record. If any other character were there, the second record would not be added to the first.
The full syntax for CONTINUEIF adds even more flexibility:

Description of the illustration continueif.gif

Table 8-2 describes the parameters for the CONTINUEIF clause.

Therefore we ask the loader to continue the record if see a break line not followed by a double quote, which means it's not the end of records!

LOAD DATA
INFILE 'giftdata.dat'
CONTINUEIF LAST != '"'
INTO TABLE conversion
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
"ID",
"DESCRIPTION" CHAR(500)
)

Cheers!

ORCL - REGEXP_REPLACE

I really like the listagg function, but it can not be used with distinct.

Sometimes I want remove the duplicate values return form listagg function and I don't like to use subquery, etc.

Finally I found a great solution using regular expression! really cool and I thought maybe useful to someone:))


select '1223-1223-1223-1345' t1, rtrim( REGEXP_REPLACE('1223-1223-1223-1345', '([^-]*)(-\1)+($|-)', '\1\3'), '-') t2

from dual;

1223-1223-1223-1345 will be 1223-1345, but be sure the list is order by the item!

Here is a example for me:

select regexp_replace( (listagg(a.short_name, ',' ) within group (order by a.short_name)), '([^,]*)(,\1)+($|,)','\1\3')

from gift g, allocation a where g.gift_donor_id = l.gift_donor_id and g.gift_associated_allocation = a.allocation_code and a.alloc_school = 'LS') distinct_allocs      

Explanation is following: Courtesy to Srinivasan - bean farmer 

http://srinisqlwork.blogspot.com/2016/12/sql-remove-duplicates-in-listagg-result.html

Understanding the solution with example:

select

REGEXP_REPLACE( 'English,English,English,Hindi,Hindi,Kannada,Kannada,Kannada,Tamil,Tamil,Telugu,Telugu','([^,]*)(,\1)+($|,)'   ,'\1\3')

from dual

1. Understanding regExMatch expression: '([^,]*)(,\1)+($|,)'

There are three groups in above expression

group 1: ([^,]*) : match all or no characters till comma   

match Result: English

group 2: (,\1)+ : \1 - stands for first group which is Kannada, so it becomes (,English)+, meaning match one or more occurrences of  ',English'.

match Result: ,English,English

group 3: ($|,) : $ stands for 'end of string', | stands or. So it says match either end of string or a comma

match Result: ,    (this is comma which is following group2 match: ,English,English)

2. Understanding regExReplace expression '\1\3':

\1\3 represent the group number which are used in regExMatch expression.

replace Result: English,

REGEXP_REPLACE

Syntax

Description of the illustration regexp_replace.gif

Purpose

REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, "Oracle Regular Expression Support".

• source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
• pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".
• replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to "Oracle Regular Expression Support", Table C-1.
• position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
• occurrence is a nonnegative integer indicating the occurrence of the replace operation:
  
  • If you specify 0, then Oracle replaces all occurrences of the match.
  • If you specify a positive integer n, then Oracle replaces the nth occurrence.
• match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:
  
  • 'i' specifies case-insensitive matching.
  • 'c' specifies case-sensitive matching.
  • 'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.
  • 'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
  • 'x' ignores whitespace characters. By default, whitespace characters match themselves.
  If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
  If you omit match_parameter, then:
  
  • The default case sensitivity is determined by the value of the NLS_SORT parameter.
  • A period (.) does not match the newline character.
  • The source string is treated as a single line.

ORCL - FORMAT_ERROR_BACKTRACE

Having a few packages to send email around, often I find exception error is hard to trace since I was just using SQLerrm function in exception, which only shows the error without telling the source.

1st I thought I could look for ways to find where exactly the error occurs, such as showing function/procedure name, then after a little online search, I found dbms_utility package has a nice function: FORMAT_ERROR_BACKTRACE, which displays the error line number, better than function/procedure name!
 

This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of the SQLERRM function, but not subject to the same size limitation.

Syntax

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE 
  RETURN VARCHAR2;

Return value

 

NICE! Also if you want to look at full error stack, check FORMAT_ERROR_STACK function.

 

To use, just simply add such line (red) as the below:

 

EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line (dbms_utility.FORMAT_ERROR_BACKTRACE);
      V_MSG_SUB_ERROR := V_MSG_SUB_ERROR;
      V_MSG_ERROR     := V_MSG_ERROR || SQLERRM || '

' || dbms_utility.FORMAT_ERROR_BACKTRACE || V_MSG_SIG;
      UTL_MAIL.SEND(SENDER     => EMAIL_FROM,
                    RECIPIENTS => EMAIL_ERROR_TO,
                    CC         => EMAIL_BCC,
                    SUBJECT    => V_MSG_SUB_ERROR,
                    MESSAGE    => V_MSG_ERROR,
                    MIME_TYPE  => 'text/html');
  END;

"In a real-world application, the error backtrace could be very long. Generally, debuggers and support people don't really want to have to deal with the entire stack; they are mostly going to be interested in that top-most entry. The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. In this case, it is necessary to parse the backtrace string and retrieve just the top-most entry." - by Steven Feuerstein 

 

ORCL - LISTAGG

Very handy Listagg Function

 

During my daily routine, I constantly need to display multiple values for single row, I have to write a few lines of code until the LISTAGG function!

 

 

http://docs.oracle.com/cd/E14072_01/server.112/e10592/functions087.htm

 

Examples from oracle

 

The following single-set aggregate example lists all of the employees in Department 30 in the hr.employees table, ordered by hire date and last name:

 

SELECT LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
    MIN(hire_date) "Earliest"
    FROM employees
    WHERE department_id = 30;

Emp_list                                                     Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares            07-DEC-02

 

 

ORCL - Restart Database and EM

Back from a week vacation and found that the database is not working as well the ORCLE EM. What a pity for me as a newbie DBA without any mentor or help inside office. The good thing is I have Intenet to look for help....

OK, after half day working around, here I record the steps after server is accidently shutdown:

1. my bad is not setup swap space persistent, so I have to swap -a to make enough space, will do the persistent setup for swap space.
swap -a /export/home/oracle/orcl_swap then swap -l to check

2. Start the listener: lsnrctl start

3. Set up the ORACLE_SID, could add the line in .profile: export ORACLE_SID=orcl

4. Set up the ORACLE_HOSTNAME and UNQNAME for ORALE EM
export ORACLE_HOSTNAME=solaris
export ORACLE+UNQNAME=orcl
OM will look inside folder solaris_orcl

5. Startup database
sqlplus / as sysdba then startup

6. Start Oracle EM
emctl start dbconsole

Thanks to the link ORACLE-BASE.com:
http://www.oracle-base.com/articles/misc/basic-enterprise-manager-troubleshooting.php

ORCL - Data Pump

We need move 10g production data into 11g test so we could test advance system upgrade, there are three upgrade methods offered to upgrade database from 10g to 11g.

1. Database Upgrade Assistant (DBUA)
2. Manual Upgrade (Oracle provided scripts)
3. Export/Import (exp/imp, or expdp/impdp)

Using data pump looks promising, I prefer using Oracle EM, rather than manually following the steps:

1 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp
2 copy dump file to new server
3 create database in new server with same tablespaces as source database -- IS IT NECESSARY?! (if different structure)
4 impdp user/pass full=y directory =test_dir logfile=exp.log dumpfile=exp.dmp

I used EM and finally got this error:

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOOK_FOR_OBJECT 

looks some problem with temp tablespace:

assign the temp tablespace to the schema owner as Oracle is probably using whatever default TEMP tablespace is specified for the database.
SQL> alter user advance temporary tablespace TEMP;

At leaset I was able to login through PL/SQL developer.

helpful links:

Oracle Doc: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#autoId45

http://oraclepoint.com/oralife/2010/07/28/upgrade-oracle-database-from-10g-to-11g-with-data-pump-3/

ORCL - Installation

I decide to blog this after some frustration on installing Oracle 11g on Solaris 10. (For Solaris 11 you need some support account to get the Oracle 11g).

Intenet has a lot of information which sometimes could mislead you if you dont read carefully :(. I will recommend to read the official manual.

1st intall Oracle-Solaris 10, pretty straight forawrd, login as root.

Preinstallation Requirements:
PACKAGE: check all needed packages: pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibC SUNWlibms SUNWsprot \ SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt get missing package from installaton DVD if missing any:
pkgadd -d /cdrom/sol*/Solaris*/Product SUNWi1cs SUNWi15cs
GROUP AND USER:
# groupadd oinstall
# groupadd dba
# useradd -d /export/home/oracle -m -s /usr/bin/bash -g oinstall -G dba oracle
# passwd -r files oracle
KERNEL PARAMETERS (IMPORTANT!)
Set Resource Control to Oralce Recommended Value
# projadd -U oracle -K "project.max-shm-memory=(priv,6g,deny)" group.dba
# projmod -sK "project.max-sem-nsems=(privileged,256,deny)" group.dba
# projmod -sK "project.max-sem-ids=(privileged,100,deny)" group.dba
# projmod -sK "project.max-shm-ids=(privileged,100,deny)" group.dba
This will make them persist, confirm by cat /etc/project
NOTE: When you use the prctl command (Resource Control) to change system parameters, you do not have to restart the system for these parameter changes to take effect. However, the changed parameters do not persist after a system restart.
Make sure oracle user is associated with the project (group.dba). This is missing in documents, and will possible produce "out of memory" error.
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=3(default)
# usermod -K project=group.dba oracle
# id -p oracle uid=100(oracle) gid=100(oinstall) projid=100(group.dba)  
SWAP (IMPORTANT!)
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
# mkfile 4096m /export/home/oracle/orcl_swap
# swap -a /export/home/oracle/orcl_swap
# swap -l swapfile dev swaplo blocks free
/dev/dsk/c0t0d0s1 30,65 8 1092408 1092408
/export/home/oracle/orcl_swap - 8 8388600 8388600
Now wh have enough swap space.
if you need persistent swap, add line inside /etc/vfstab.
INSTALL ORACLE Downlaod Oracle 11g and create necessary folders.
# mkdir -p /export/home/oracle/product/11.2.0/db_1
# mkdir /export/home/oracle/tmp
# chown -R oracle:oinstall /export/home/oracle
Configure profile for oracle user with enviroment variables:
vi/gedit /export/home/oracle/.profile
export ORACLE_BASE=/export/home/oracle
export ORACLE_HOME=$ORACLE_HOME/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export TMP=/export/home/oracle/tmp
export TMPDIR=/export/home/oracle/tmp
export DISPLAY=0:0
Unzip and install
# unzip solaris.x64_11gR2_database_1of2.zip
# unzip solaris.x64_11gR2_database_2of2.zip
# cd databse
# ./runInstaller

After 5 time installation I am feeling OK with Oracle installation. Next step I woild like to copy 10g production data into 11g new server.

ORCL - How cool it is!

在oracle中把连串字符转变为table fields，原来要做loop很麻烦，现在用expression就行了。


真爽！特此记录，怕以后忘了，呵呵。

if A_PROSPECTIDS is not null

then

-- remove from prospects_main any not in list of ids:

T_UPDATESQL := 'delete from prospects_main pm where pm.id_number

not in (( select lpad(trim(regexp_substr(a_prospectids,'[^,]+{1}',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(a_prospectids,'[^,]*'))+1 ) ';

execute immediate T_UPDATESQL;

commit;

exception

when others then

RAISE_APPLICATION_ERROR(-20999,

priority_prospects: Error removing prospects: ' ||

sqlcode || ': ' || sqlerrm);

end;

end if;


另外在用动态的SQL （dynamic SQL)时，变量要用加串，两个双引号相当于一个单引号：

lpad(trim(regexp_substr(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]+{1}' || '''' || ',1,level)), 10, 0) ids from dual

connect by level <= length(regexp_replace(' ||''''|| t_formattedids ||'''' || ',' || '''' || '[^,]*' || '''' || '))+1

ORCL - Remove ^M in VI

Our management loves to use MS office products, Word, Excel, Sharepoint, to name a few...Our dev is Oracel/Unix, good thing we picked Oracle, since that is the main stream of univeristy? (BTW, I am working in university external relation department).

Quite often, we need get excel file to import into Oracle database and massaging data. Most colleagues use some gui developer tools such as Toad, PL/SQL Developer to do the job, which are pretty good. I like the Oracle external table (Worked a few years on SQL loader :).

To get file from local (of course Windows) to unix server, the PSCP comes handy! It's from Putty, maintained by a small team based in Cambridge, England, and easy command line. There are also some GUI tools like WinSCP, etc.

But sometimes when I convert excel into csv (comma, tab delimited), the external table will not work due to the stupid ( I should not say this :( )^M at the end of lines. UNIX treats the end of line differently than other operating systems. Sometimes when editing files in both Windows and UNIX environments, a CTRL-M character is visibly displayed at the end of each line as ^M in vi.

Good thing that Vi powerful replace command, :%s/^V^M//g, make sure ^V^M are ctrl-v and ctrl M, not copying/pasting :) . It looks not showing on VIM - GUI vi.

The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).

Just another comment: I don't know why some IT people, especially the network/helpdesk IT guys like windows stuff so much (acutally I love that too, but just dont give up on *nix:), really user friendly? They will do anything to eacpae from any possibility of *nix stuff. Sigh... Are we thinking about converting data from Oracle to SQL server to work on Sharepoint stuff? Maybe it's good for performance.

We are IT people, we should handle everything and anything :)))

ORCL - sys_connect_by_path

I really want to share this cool use of sys_connect_by_path function!

To deal the unknown length of concatenating string, I need to concatenate multiple rows into 1 string without decaring at certain length such as varchar2(X).

So here is the magic, hope you like it...

select ltrim(sys_connect_by_path(names, ','),',') jonit_entities from (
select names, row_number() over (order by names) rn, count(*) over () cnt
from (select entity.pref_mail_name names
from prospect_entity P, entity
where P.Prospect_Id = &id
and P.ID_NUMBER = entity.id_number
order by P.PRIMARY_IND desc)
)
where rn = cnt
start with rn = 1
connect by prior rn = rn-1;

cheers, for detail information about sys_connect_by_path, please check here.

ORCL - Lag/Lead Over

In normalized form, the data is display as the row format, such as the following:

12:28:14 PM SQL> select id_number, degree_code from degrees where id_number = 'xxxx';

ID_NUMBER DEGREE_CODE
---------- -----------
xxxxxxxxxx PHD
xxxxxxxxxx ENG
xxxxxxxxxx MSC

But we want the result displasy as one row as different columns, basically we want the data de-normlized.

A Little SQL

If we know the identity for the degree code then we can construct a SQL statement that will handle this type of query using Rotate/Pivot Query.

First we want to get such identity, so we use the windows over function to assign the row number to identify the degree code:

12:28:50 PM SQL> select id, row_number() over (partition by id order by code) rn, code from degrees;
ID RN CODE
---------- ---------- -----------
000000xxxx 1 NUSSS
000000xxx1 1 EDBSS
000000xxx2 1 MAAAA
000000xxx2 2 PHDSS
000000xxx3 1 BCZZZ
000000xxx3 2 SCESS

The we can change the row to colum display using the code shown below:
12:35:47 PM SQL>
12:40:20 PM SQL> select id,
2 (select d1.code from
3 (select id, row_number() over (partition by id order by code) rn, code
4 from table_name) d1
5 where d1.id = A.id and rn =1) degree1,
6 (select d1.code from
7 (select id, row_number() over (partition by id order by code) rn, code
8 from table_name) d1
9 where d1.id = A.id and rn =2) degree2,
10 (select d1.code from
11 (select id, row_number() over (partition by id order by code) rn, code
12 from table_name) d1
13 where d1.id = A.id and rn =3) degree3,
14 (select d1.code from
15 (select id, row_number() over (partition by id order by code) rn, code
16 from table_name) d1
17 where d1.id = A.id and rn =4) degree4
18 from (select id from table_name group by id) A
19 /

Then we achieve what we want!

But if you use the Lag/lead over analytic function, it is even better!

SQL> select id, d1, d2, d3, d4 from
2 (select id, sequence, degree_code d1, lead(degree_code,1) over (order by sequence) d2,
3 lead(degree_code,2) over (order by sequence) d3, lead(degree_code,3) over (order by sequence) d4
4 from tale_name
5 where id= '0000000xxx')
6 where sequence = 1
7 /

ID D1 D2 D3 D4
---------- ----- ----- ----- -----
0000000xxx PHD ENG MSC

SQL> here we go! Analytic functions rock for 80% case!