Home » SQL & PL/SQL » SQL & PL/SQL » CONCAT & SQL (to display rows into columns)
CONCAT & SQL (to display rows into columns) [message #252134] Tue, 17 July 2007 23:01 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Hi Experts,

My concat function is like this

CREATE OR REPLACE FUNCTION rowconcat(q in VARCHAR2) RETURN VARCHAR2 IS
ret VARCHAR2(4000);
hold VARCHAR2(4000);
TYPE cur IS REF CURSOR;
v_cur cur;
BEGIN
OPEN v_cur FOR q;
LOOP
FETCH v_cur INTO hold;
EXIT WHEN v_cur%NOTFOUND;
IF ret IS NULL THEN
ret := hold;
ELSE
ret := ret || ',' || hold;
END IF;
END LOOP;
RETURN ret;
END;


my sql is:

SELECT co_eng_name,
rowconcat('SELECT lic_no_cur FROM mps_v_dup_cust WHERE co_eng_name='||co_eng_name) AS duplicust
FROM mps_v_dup_cust gROUP BY co_eng_name;


my table/view is like this

CO_ENG_NAME CO_CUSTID LIC_NO_CUR
ANTHONY SYE CONSTRUCTION PTE LTD 199905399N YM5368S
ANTHONY SYE CONSTRUCTION PTE LTD 199905399N YM5430U
ANTHONY SYE CONSTRUCTION PTE LTD YK3039R
ANTHONY SYE CONSTRUCTION PTE LTD YK5641M
ANTHONY SYE CONSTRUCTION PTE LTD YK6150G
ANTHONY SYE CONSTRUCTION PTE LTD YL655B
ANTHONY SYE CONSTRUCTION PTE LTD YK9255T
ANTHONY SYE CONSTRUCTION PTE LTD YK9498J
ASIAGROUP LEASING PTE LTD A00734/1989N GBA2806A
ASIAGROUP LEASING PTE LTD A00734/1989N XB6100K
ASIAGROUP LEASING PTE LTD A00734/1989N GY9237K
ASIAGROUP LEASING PTE LTD A00734/1989N GY2531G
ASIAGROUP LEASING PTE LTD A00734/1989N GX9027E
ASIAGROUP LEASING PTE LTD A00734/1989N GX5062D
ASIAGROUP LEASING PTE LTD A00734/1989N GW5836T
ASIAGROUP LEASING PTE LTD A00734/1989N GW7920U

I want to display the LIC_NO_CUR rows into columns, I am getting error message if I run the sql, can anyone help me.

Re: CONCAT & SQL (to display rows into columns) [message #252135 is a reply to message #252134] Tue, 17 July 2007 23:04 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
>I want to display the LIC_NO_CUR rows into columns,

Search this forum for "PIVOT QUERY"

>I am getting error message if I run the sql
Error? What Error? I don't see any error!

Please read & FOLLOW the posting guideline in the STICKY post at the top of this forum!

ROWCONCAT [message #252139 is a reply to message #252135] Tue, 17 July 2007 23:09 Go to previous messageGo to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
ERROR MESSAGE:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression

Re: ROWCONCAT [message #252166 is a reply to message #252139] Wed, 18 July 2007 01:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kumarvk wrote on Wed, 18 July 2007 06:09
ERROR MESSAGE:

ORA-01785: ORDER BY item must be the number of a SELECT-list expression



There is no ORDER in your query. Question Question Question

Regards
Michel
Re: CONCAT & SQL (to display rows into columns) [message #252262 is a reply to message #252134] Wed, 18 July 2007 07:21 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
I bet there is one in the view though Laughing

Previous Topic: deleting records in a table based on a collection
Next Topic: acquired nowait
Goto Forum:
  


Current Time: Mon Dec 05 11:21:45 CST 2016

Total time taken to generate the page: 0.06765 seconds