 Newbie needs help with wm_concat [message #681249] |
Tue, 30 June 2020 05:41  |
 |
Operator101
Messages: 3
Registered: June 2020
|
Junior Member |
|
|
Hello all,
I'm very new to SQL (no training, just what I have glimpsed from tutorials on a very basic level) and I have been given the task to get a SQL query up to date.
Right now a Oralce DB 10g is in use, which might be updated in the foreseeable future.
The query is using a command (WM_concat) that will not be supported with Oralce 12c. I'd like to replace wm_concat with a supported command like listagg, which is available with Oralce 11g R2(?).
But with no "testbed" I'm having a hard time figuring it out by myself.
column TREFFER format A1000
select
WMSYS.WM_CONCAT(distinct 'TREFFER='||wf.SUBMIT_DATE||'|'||wf.form_id||'|'||wf.SITEKEY)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='gender' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='form_country' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='form_language' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='camp' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='givenname' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='surname' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='company' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='street' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='housenr' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='postalcode' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='city' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='country' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='contact' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='email' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='phone' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='mail-when' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='phone-when' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='phone_mail_post_contact_allowed' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='help' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='materials' THEN PVALUE END)||'|'||
WMSYS.WM_CONCAT(CASE WHEN PKEY='message' THEN replace(replace(PVALUE,CHR(13),''),CHR(10),' ') END)||'|' as TREFFER
from m_webform_data wfd1
JOIN M_WEBFORM wf ON wfd1.form_id = wf.form_id
AND wf.NAME = 'form_de_de_sliding_systems_morethanaview_contact_consultation'
group by wfd1.FORM_ID order by wfd1.FORM_ID;
Best,
Andres
|
|
|
|
| Re: Newbie needs help with wm_concat [message #681251 is a reply to message #681249] |
Tue, 30 June 2020 06:55  |
 |
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Here is the documentation page of LISTAGG.
Here's an example:
SQL> select d.dname,
2 listagg(e.ename,',') within group (order by empno) emplist
3 from dept d join emp e on e.deptno = d.deptno
4 group by d.dname
5 order by d.dname
6 /
DNAME EMPLIST
-------------- --------------------------------------------------------
ACCOUNTING CLARK,KING,MILLER
RESEARCH SMITH,JONES,SCOTT,ADAMS,FORD
SALES ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
3 rows selected. |
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
)
