| Date [message #373695] |
Wed, 02 May 2001 04:35  |
Prasanna
Messages: 43
Registered: April 2001
Location: India
|
Member |
|
|
How do i query the the table with date field to get the following result,
my table data is
01-mar-00
04-mar-00
03-jun-00
10-jul-00
23-jul-00
01-jan-01
04-feb-01
12-apr-01
05-aug-01
result is
2000 MAR JUN JUL
2001 JAN FEB APR AUG
Rgds
Prasanna
|
|
|
|
| Re: Date [message #373706 is a reply to message #373695] |
Wed, 02 May 2001 08:20  |
Hari
Messages: 59
Registered: August 2000
|
Member |
|
|
select distinct to_char(date,'YYYY'), to_char(date,'Mon') from table.
result
2000 MAR
2000 JUN
2000 JUL
2001 JAN
2001 FEB
2001 APR
2001 AUG
u can use break on year and display it only once.
|
|
|
|
| Re: Date [message #373762 is a reply to message #373695] |
Sun, 06 May 2001 23:49 |
honchae
Messages: 1
Registered: May 2001
|
Junior Member |
|
|
i think you can use the FUNCTION of pl/sql and SQL using that.
in this case, we can make a function having return value .
simple example )
CREATE OR REPLACE FUNCTION plsqltest
(v_mgrid IN VARCHAR2)
RETURN VARCHAR2
IS
v_string VARCHAR2(100);
v_tmp VARCHAR2(100);
v_cnt BINARY_INTEGER := 0;
CURSOR tmp_cursor IS
SELECT name
FROM s_emp
WHERE manager_id = v_mgrid;
BEGIN
OPEN tmp_cursor;
LOOP
FETCH tmp_cursor INTO v_tmp;
EXIT WHEN tmp_cursor%NOTFOUND;
v_cnt := v_cnt+1;
IF v_cnt = 1 THEN
v_string := v_tmp;
ELSE
v_string := v_string||','||v_tmp;
END IF;
END LOOP;
CLOSE tmp_cursor;
RETURN (v_string);
END plsqltest;
and then, use it as group-function in sql statement like below,
SELECT manager_id, plsqltest(name)
FROM s_emp
GROUP BY manager_id
hope this helpful, good luck.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
