Home » SQL & PL/SQL » SQL & PL/SQL » positional notation and rownum
icon6.gif  positional notation and rownum [message #359774] Tue, 18 November 2008 03:00 Go to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Em...;
Sorry for everyone;

I thought noone would reply, so I deleted the comment.
It was my fault.


Question was this.


If I have a table named ELLIE and the data is like this.

seq  name    comment     date
---------------------------------------
1   Roy     hello      2008-11-15
2   David    good morning 2008-11-16
3   Luna    hey!       2008-11-17
4   Dan     alo--      2008-11-18

I wans to get the value at 2nd row & 3rd column.
How can I get the result 'good morning' without using column name?
I mean... NOT 'select comment from ellie where rownum=2',
but like this, 'select ----(3, 2) from ellie' or something else.

Is there any?



[mod-edit: Apparently the original poster deleted the original message, then repeated it below, so the above is what was repeated below, which is presumably similar to the original post.]

[Updated on: Tue, 18 November 2008 18:28] by Moderator

Report message to a moderator

Re: positional notation & rownum [message #359782 is a reply to message #359774] Tue, 18 November 2008 03:50 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Not sure what you are asking for.

May be your requirement is something like below:


We can use the below query to get the odd record of the table --

select rn from 
(
SELECT rownum rn FROM EMP
)

where mod(rn,2) != 0

We can use the below query to get the even record of the table

select rn from 
(
SELECT rownum rn FROM EMP
)

where mod(rn,2) = 0



Regards,
Oli

[Updated on: Tue, 18 November 2008 03:50]

Report message to a moderator

Re: this post was deleted [message #359783 is a reply to message #359774] Tue, 18 November 2008 03:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Yes you can. All you have to do is generate a row number based on your sort criteria and select the column you are interested. To generate a row number search for row_number analytic function in oracle reference manual.

Hope this helps.

Regards

Raj
Re: positional notation & rownum [message #359786 is a reply to message #359782] Tue, 18 November 2008 03:54 Go to previous messageGo to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Thank you so much for your reply.
And sorry to say this...
er.. that's not what I wanted... T_T

Have a nice day. Smile
Re: this post was deleted [message #359787 is a reply to message #359783] Tue, 18 November 2008 03:57 Go to previous messageGo to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Thank you for kind reply. I was almost give up the problem.

Um... I know how to use rownum, but I couldn't find how to use column number... Would you please give me more hint for that, if you can?
Re: this post was deleted [message #359796 is a reply to message #359774] Tue, 18 November 2008 04:09 Go to previous messageGo to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Em...;
Sorry for everyone;

I thought noone would reply, so I deleted the comment.
It was my fault.


Question was this.


If I have a table named ELLIE and the data is like this.

seq  name    comment     date
---------------------------------------
1   Roy     hello      2008-11-15
2   David    good morning 2008-11-16
3   Luna    hey!       2008-11-17
4   Dan     alo--      2008-11-18

I wans to get the value at 2nd row & 3rd column.
How can I get the result 'good morning' without using column name?
I mean... NOT 'select comment from ellie where rownum=2',
but like this, 'select ----(3, 2) from ellie' or something else.

Is there any?
Re: this post was deleted [message #359798 is a reply to message #359796] Tue, 18 November 2008 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2nd row => WHERE
3rd col => SELECT

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: this post was deleted [message #359924 is a reply to message #359796] Tue, 18 November 2008 18:23 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
The column name can be selected from the user_tab_columns view using the column_id to determine which column. You can then use that column name to select the data dynamically. Before you can select a row you must order the rows. The following demonstration defaults to ordering by the first column. I have demonstrated three different methods below using functions. You can then call those functions in a manner similar to what you have requested, passing the numeric values for column and row and returning the corresponding value. The first method uses just dynamic sql to return the value. The second method loads the values for the desired column into an ordered collection, then returns the row. The third method requires that the table be loaded into an array, then you can select various values from that array by position without reloading the table, until you wish to select from another table. I don't know which of these might help you, since you have not stated the purpose. I have demonstrated with your ellie table and the dept and emp demo tables.

-- test data:
SCOTT@orcl_11g> SELECT * FROM ellie ORDER BY 1
  2  /

       SEQ NAME       COMMENT_COL     DATE_COL
---------- ---------- --------------- ----------
         1 Roy        hello           2008-11-15
         2 David      good morning    2008-11-16
         3 Luna       hey!            2008-11-17
         4 Dan        aloha           2008-11-18


-- first method using dynamic sql only:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_val
  2    (p_col IN NUMBER,
  3  	p_row IN NUMBER,
  4  	p_tab IN VARCHAR2,
  5  	p_ord IN NUMBER DEFAULT 1)
  6    RETURN	 VARCHAR2
  7  AS
  8    v_col	 VARCHAR2(30);
  9    v_ord	 VARCHAR2(30);
 10    v_val	 VARCHAR2(32767);
 11  BEGIN
 12    SELECT column_name
 13    INTO   v_col
 14    FROM   user_tab_columns
 15    WHERE  UPPER (table_name) = UPPER (p_tab)
 16    AND    column_id = p_col;
 17    --
 18    SELECT column_name
 19    INTO   v_ord
 20    FROM   user_tab_columns
 21    WHERE  UPPER (table_name) = UPPER (p_tab)
 22    AND    column_id = p_ord;
 23    --
 24    EXECUTE IMMEDIATE
 25  	  'SELECT the_val
 26  	   FROM   (SELECT '   || v_col || ' AS the_val,
 27  			      ROW_NUMBER () OVER (ORDER BY ' || v_ord || ') AS rn
 28  		   FROM '     || p_tab || ')
 29  	   WHERE  rn = :b_row'
 30    INTO v_val USING p_row;
 31    --
 32    RETURN v_val;
 33  END get_val;
 34  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- 2nd row of 3rd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'ellie') FROM DUAL
  2  /

GET_VAL(3,2,'ELLIE')
--------------------------------------------------------------------------------
good morning

SCOTT@orcl_11g> -- 3rd row of 2nd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (2, 3, 'ellie') FROM DUAL
  2  /

GET_VAL(2,3,'ELLIE')
--------------------------------------------------------------------------------
Luna

SCOTT@orcl_11g> -- 2nd row of 3rd column of dept table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'dept') FROM DUAL
  2  /

GET_VAL(3,2,'DEPT')
--------------------------------------------------------------------------------
DALLAS

SCOTT@orcl_11g> -- 2nd row of 3rd column of emp table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'emp') FROM DUAL
  2  /

GET_VAL(3,2,'EMP')
--------------------------------------------------------------------------------
SALESMAN


-- second method using collection:
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION get_val
  2    (p_col	      IN NUMBER,
  3  	p_row	      IN NUMBER,
  4  	p_tab	      IN VARCHAR2,
  5  	p_ord	      IN NUMBER DEFAULT 1)
  6    RETURN		 VARCHAR2
  7  AS
  8    v_col		 VARCHAR2(30);
  9    v_ord		 VARCHAR2(30);
 10    TYPE mytabtype IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
 11    my_columns	 mytabtype;
 12  BEGIN
 13    SELECT column_name
 14    INTO   v_col
 15    FROM   user_tab_columns
 16    WHERE  UPPER (table_name) = UPPER (p_tab)
 17    AND    column_id = p_col;
 18    --
 19    SELECT column_name
 20    INTO   v_ord
 21    FROM   user_tab_columns
 22    WHERE  UPPER (table_name) = UPPER (p_tab)
 23    AND    column_id = p_ord;
 24    --
 25    EXECUTE IMMEDIATE
 26  	 'SELECT ' || v_col || ' FROM ' || p_tab || ' ORDER BY ' || v_ord
 27    BULK COLLECT INTO my_columns;
 28    RETURN my_columns(p_row);
 29  END get_val;
 30  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> -- 2nd row of 3rd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'ellie') FROM DUAL
  2  /

GET_VAL(3,2,'ELLIE')
--------------------------------------------------------------------------------
good morning

SCOTT@orcl_11g> -- 3rd row of 2nd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (2, 3, 'ellie') FROM DUAL
  2  /

GET_VAL(2,3,'ELLIE')
--------------------------------------------------------------------------------
Luna

SCOTT@orcl_11g> -- 2nd row of 3rd column of dept table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'dept') FROM DUAL
  2  /

GET_VAL(3,2,'DEPT')
--------------------------------------------------------------------------------
DALLAS

SCOTT@orcl_11g> -- 2nd row of 3rd column of emp table when ordered by 1st column:
SCOTT@orcl_11g> SELECT get_val (3, 2, 'emp') FROM DUAL
  2  /

GET_VAL(3,2,'EMP')
--------------------------------------------------------------------------------
SALESMAN


-- third method using load of table into collection followed by multiple selects:
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE your_pkg
  2  AS
  3    TYPE mytabtype IS TABLE OF VARCHAR2(255) INDEX BY BINARY_INTEGER;
  4    TYPE myarray   IS TABLE OF mytabtype INDEX BY BINARY_INTEGER;
  5    my_columns	 myarray;
  6    empty_array	 myarray;
  7    --
  8    PROCEDURE load_table
  9  	 (p_tab 	IN VARCHAR2,
 10  	  p_ord 	IN NUMBER DEFAULT 1);
 11    --
 12    FUNCTION get_val
 13  	 (p_col 	IN NUMBER,
 14  	  p_row 	IN NUMBER)
 15  	 RETURN 	   VARCHAR2;
 16  END your_pkg;
 17  /

Package created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> CREATE OR REPLACE PACKAGE BODY your_pkg
  2  AS
  3    PROCEDURE load_table
  4  	 (p_tab 	IN VARCHAR2,
  5  	  p_ord 	IN NUMBER DEFAULT 1)
  6    IS
  7  	 v_ord		   VARCHAR2(30);
  8  	 i		   NUMBER := 0;
  9    BEGIN
 10  	 my_columns := empty_array;
 11  	 --
 12  	 SELECT column_name
 13  	 INTO	v_ord
 14  	 FROM	user_tab_columns
 15  	 WHERE	UPPER (table_name) = UPPER (p_tab)
 16  	 AND	column_id = p_ord;
 17  	 --
 18  	 FOR r IN
 19  	   (SELECT column_name
 20  	    FROM   user_tab_columns
 21  	    WHERE  UPPER (table_name) = UPPER (p_tab))
 22  	 LOOP
 23  	   i := i + 1;
 24  	   EXECUTE IMMEDIATE
 25  	     'SELECT ' || r.column_name || ' FROM ' || p_tab || ' ORDER BY ' || v_ord
 26  	   BULK COLLECT INTO my_columns(i);
 27  	 END LOOP;
 28    END load_table;
 29    --
 30    FUNCTION get_val
 31  	 (p_col 	IN NUMBER,
 32  	  p_row 	IN NUMBER)
 33  	 RETURN 	   VARCHAR2
 34    IS
 35    BEGIN
 36  	 RETURN my_columns(p_col)(p_row);
 37    END get_val;
 38  END your_pkg;
 39  /

Package body created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> EXEC your_pkg.load_table ('ellie')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> -- 2nd row of 3rd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT your_pkg.get_val (3, 2) FROM DUAL
  2  /

YOUR_PKG.GET_VAL(3,2)
--------------------------------------------------------------------------------
good morning

SCOTT@orcl_11g> -- 3rd row of 2nd column of ellie table when ordered by 1st column:
SCOTT@orcl_11g> SELECT your_pkg.get_val (2, 3) FROM DUAL
  2  /

YOUR_PKG.GET_VAL(2,3)
--------------------------------------------------------------------------------
Luna

SCOTT@orcl_11g> -- 2nd row of 3rd column of dept table when ordered by 1st column:
SCOTT@orcl_11g> EXEC your_pkg.load_table ('dept')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT your_pkg.get_val (3, 2) FROM DUAL
  2  /

YOUR_PKG.GET_VAL(3,2)
--------------------------------------------------------------------------------
DALLAS

SCOTT@orcl_11g> -- 2nd row of 3rd column of emp table when ordered by 1st column:
SCOTT@orcl_11g> EXEC your_pkg.load_table ('emp')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SELECT your_pkg.get_val (3, 2) FROM DUAL
  2  /

YOUR_PKG.GET_VAL(3,2)
--------------------------------------------------------------------------------
SALESMAN

SCOTT@orcl_11g> 


Re: this post was deleted [message #359927 is a reply to message #359798] Tue, 18 November 2008 18:54 Go to previous messageGo to next message
elliekim
Messages: 8
Registered: November 2008
Junior Member
I'm really sorry about the format.
but I saw the replies after I deleted the post and the version.
And thank you for upload my post...
Re: this post was deleted [message #359928 is a reply to message #359924] Tue, 18 November 2008 18:57 Go to previous message
elliekim
Messages: 8
Registered: November 2008
Junior Member
Thank you so much on this reply.
I was making a package, exporting the whole tables and the datas into csv file, and I couldn't write out the data.
( I could create the csv files, named with each table names, and write out the headers they have.)
I'm sure this code will help me, so I will execute and study this code right now.
Thank you again. Very Happy
Previous Topic: Please help in converting a script to query for getting Net Entitlement (merged)
Next Topic: store table name in a variable
Goto Forum:
  


Current Time: Fri Dec 09 19:33:14 CST 2016

Total time taken to generate the page: 0.25931 seconds