Home » SQL & PL/SQL » SQL & PL/SQL » SQL to filter redundant key field (Oracle 9.2 on Solaris)
SQL to filter redundant key field [message #652841] Mon, 20 June 2016 16:29 Go to next message
raghx
Messages: 35
Registered: March 2006
Location: Bangalore
Member
Hi All,

Below SQL is within a View VW_GET_ACC_EVENTS.
SELECT 
                e.event_id,
                e.event_type_id,
                e.event_status_code,
                r.request_id,
                s.acc_no,
                s.nr_ind,
                s.acc_category_code
           FROM   EVENT e,
                  request_transaction t,
                  request r,
                  account s,
                  request_tx_type x
         WHERE  e.event_type_id = 214
           AND  e.request_tx_id = t.request_tx_id 
           AND  r.request_id = s.request_id
           AND  r.request_id = t.request_id
           AND  t.request_tx_type_id = x.request_tx_type_id
We call the below view as
SELECT * FROM WHERE VW_GET_ACC_EVENTS WHERE event_status_code in ('I','O') and acc_category_code = 'C'
The result we get as below
   	EVENT_ID	EVENT_TYPE_ID	EVENT_STATUS_CODE	REQUEST_ID	ACC_NO	        NR_IND	ACC_CATEGORY_CODE
	160733	        214	        O	                20852	        0123456789	Y	C
	160733	        214	        O	                20852	        0234567899	Y	C
	160733	        214	        O	                20852	        0345678901	Y	C
	160733	        214	        O	                20852	        0455123256	Y	C
	160733	        214	        O	                20852	        0256897123	Y	C
	160734	        214	        O	                20853	        0789789789	Y	C
	160734	        214	        O	                20853	        0456456456	Y	C
	160734	        214	        O	                20853	        0123123123	Y	C
	160734	        214	        O	                20853	        0145145145	Y	C
	160734	        214	        O	                20853	        0256256256	Y	C

The result I want is as below
   	EVENT_ID	EVENT_TYPE_ID	EVENT_STATUS_CODE	REQUEST_ID	ACC_NO	        NR_IND	ACC_CATEGORY_CODE
	160733	        214	        I	                20852	        0123456789	Y	C
		        	        O	                	        0234567899	Y	C
		        	        I	                	        0345678901	Y	C
		        	        O	                	        0455123256	Y	C
		        	        O	                	        0256897123	Y	C
	160734	        214	        O	                20853	        0789789789	Y	C
		        	        I	                	        0456456456	Y	C
		        	        O	                	        0123123123	Y	C
		        	        O	                	        0145145145	Y	C
		        	        I	                	        0256256256	Y	C
So that we can traverse thru the EVENT_ID and populate the ACC_NO details.
Kindly help with SQL please

Report message to a moderator

Re: SQL to filter redundant key field [message #652842 is a reply to message #652841] Mon, 20 June 2016 19:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
If you are using SQL*Plus, you can use the BREAK command. You will need to order by the columns that you want to break on, then break on each. For example, if you have a query like below.

SCOTT@orcl_12.1.0.2.0> select deptno, job, ename, hiredate, sal
  2  from   emp
  3  order  by deptno, job, ename
  4  /

    DEPTNO JOB       ENAME      HIREDATE               SAL
---------- --------- ---------- --------------- ----------
        10 CLERK     MILLER     Sat 23-Jan-1982       1300
        10 MANAGER   CLARK      Tue 09-Jun-1981       2450
        10 PRESIDENT KING       Tue 17-Nov-1981       5000
        20 ANALYST   FORD       Thu 03-Dec-1981       3000
        20 ANALYST   SCOTT      Sun 19-Apr-1987       3000
        20 CLERK     ADAMS      Sat 23-May-1987       1100
        20 CLERK     SMITH      Wed 17-Dec-1980        800
        20 MANAGER   JONES      Thu 02-Apr-1981       2975
        30 CLERK     JAMES      Thu 03-Dec-1981        950
        30 MANAGER   BLAKE      Fri 01-May-1981       2850
        30 SALESMAN  ALLEN      Fri 20-Feb-1981       1600
        30 SALESMAN  MARTIN     Mon 28-Sep-1981       1250
        30 SALESMAN  TURNER     Tue 08-Sep-1981       1500
        30 SALESMAN  WARD       Sun 22-Feb-1981       1250

14 rows selected.

you can break on the deptno and job columns that you have ordered by, like below.
SCOTT@orcl_12.1.0.2.0> break on deptno on job
SCOTT@orcl_12.1.0.2.0> select deptno, job, ename, hiredate, sal
  2  from   emp
  3  order  by deptno, job, ename
  4  /

    DEPTNO JOB       ENAME      HIREDATE               SAL
---------- --------- ---------- --------------- ----------
        10 CLERK     MILLER     Sat 23-Jan-1982       1300
           MANAGER   CLARK      Tue 09-Jun-1981       2450
           PRESIDENT KING       Tue 17-Nov-1981       5000
        20 ANALYST   FORD       Thu 03-Dec-1981       3000
                     SCOTT      Sun 19-Apr-1987       3000
           CLERK     ADAMS      Sat 23-May-1987       1100
                     SMITH      Wed 17-Dec-1980        800
           MANAGER   JONES      Thu 02-Apr-1981       2975
        30 CLERK     JAMES      Thu 03-Dec-1981        950
           MANAGER   BLAKE      Fri 01-May-1981       2850
           SALESMAN  ALLEN      Fri 20-Feb-1981       1600
                     MARTIN     Mon 28-Sep-1981       1250
                     TURNER     Tue 08-Sep-1981       1500
                     WARD       Sun 22-Feb-1981       1250

14 rows selected.

Report message to a moderator

Re: SQL to filter redundant key field [message #652843 is a reply to message #652842] Mon, 20 June 2016 19:34 Go to previous message
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
Senior Member
If you are not using SQL*Plus, then you can mimic the break functionality by using row_number and decode as shown below.

SCOTT@orcl_12.1.0.2.0> select decode (rn1, 1, deptno, null) deptno,
  2  	    decode (rn2, 1, job, null) job,
  3  	    ename, hiredate, sal
  4  from   (select deptno, job, ename, hiredate, sal,
  5  		    row_number() over (partition by deptno order by job) rn1,
  6  		    row_number() over (partition by deptno, job order by ename) rn2
  7  	     from   emp
  8  	     order  by deptno, job, ename)
  9  /

    DEPTNO JOB       ENAME      HIREDATE               SAL
---------- --------- ---------- --------------- ----------
        10 CLERK     MILLER     Sat 23-Jan-1982       1300
           MANAGER   CLARK      Tue 09-Jun-1981       2450
           PRESIDENT KING       Tue 17-Nov-1981       5000
        20 ANALYST   FORD       Thu 03-Dec-1981       3000
                     SCOTT      Sun 19-Apr-1987       3000
           CLERK     ADAMS      Sat 23-May-1987       1100
                     SMITH      Wed 17-Dec-1980        800
           MANAGER   JONES      Thu 02-Apr-1981       2975
        30 CLERK     JAMES      Thu 03-Dec-1981        950
           MANAGER   BLAKE      Fri 01-May-1981       2850
           SALESMAN  ALLEN      Fri 20-Feb-1981       1600
                     MARTIN     Mon 28-Sep-1981       1250
                     TURNER     Tue 08-Sep-1981       1500
                     WARD       Sun 22-Feb-1981       1250

14 rows selected.

Report message to a moderator

Previous Topic: how to export the data from table to the file
Next Topic: Update 7% basic Salary
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 04:18:56 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.