Home » SQL & PL/SQL » SQL & PL/SQL » Newbie needs help with wm_concat (Oracle Database 10g Release 10.2.0.5.0 - Production; PL/SQL Release 10.2.0.5.0 - Production; "CORE 10.2.0.5.0 Production"; TNS for Linux: Version 10.2.0.5.0 - Production; NLSRTL Version 10.2)
icon5.gif  Newbie needs help with wm_concat [message #681249] Tue, 30 June 2020 05:41 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
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.
Re: Newbie needs help with wm_concat [message #681253 is a reply to message #681251] Tue, 30 June 2020 09:23 Go to previous messageGo to next message
Operator101
Messages: 3
Registered: June 2020
Junior Member
Michel Cadot wrote on Tue, 30 June 2020 06:55

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Isn't there a way to edit my post? At least I don't see an edit button (but neither did I see the code button Confused )
I'll make sure to get the format right next time.

Best,
Andrés
Re: Newbie needs help with wm_concat [message #681254 is a reply to message #681253] Tue, 30 June 2020 09:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
This forum does NOT allow changes to previous posts.
It is OK to simply make another entry to this thread showing updated details
Re: Newbie needs help with wm_concat [message #681256 is a reply to message #681253] Tue, 30 June 2020 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can select the part you want to format and click on the code button /forum/fa/14344/0/ in the "Formatting Tools" line.

[Updated on: Tue, 30 June 2020 11:25]

Report message to a moderator

Re: Newbie needs help with wm_concat [message #681297 is a reply to message #681256] Mon, 06 July 2020 05:48 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
If you want to convert queries and test out code then download the free express edition of the oracle database. No license is required to use this version in training or production because of built in limitations

3 Pluggable Databases
2 CPUs for foreground processes
2GB of RAM (SGA and PGA combined)
12GB of user data on disk (irrespective of compression factor)

The database will load and run on a standard workstation without degrading the rest of the applications. The link to download follows

https://www.oracle.com/database/technologies/xe-downloads.html

[Updated on: Mon, 06 July 2020 05:49]

Report message to a moderator

Re: Newbie needs help with wm_concat [message #681298 is a reply to message #681297] Mon, 06 July 2020 05:51 Go to previous message
Operator101
Messages: 3
Registered: June 2020
Junior Member
Bill B wrote on Mon, 06 July 2020 05:48
If you want to convert queries and test out code then download the free express edition of the oracle database. No license is required to use this version in training or production because of built in limitations


The database will load and run on a standard workstation without degrading the rest of the applications. The link to download follows

https://www.oracle.com/database/technologies/xe-downloads.html
Thanks! Will definitely try that.
Previous Topic: DB link issue in cloned databases on same host
Next Topic: To store table row count in the same table's comment
Goto Forum:
  


Current Time: Thu Mar 28 12:26:50 CDT 2024