Home » SQL & PL/SQL » SQL & PL/SQL » Using LISTAGG in a subquery (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)
Using LISTAGG in a subquery [message #672000] Thu, 27 September 2018 09:00 Go to next message
ssmith001
Messages: 35
Registered: August 2018
Member
Can someone assist in helping me figure out how to join these two queries together? I've just recently found the LISTAGG function which is working great, but I can't figure out how to join these together where query1.CARR_CD = query2.SCAC

Query 1:

SELECT CARR_CD, LISTAGG(EMAL, ', ') WITHIN GROUP (ORDER BY EMAL) "Email_Address"
FROM JDATM_2016PRD.PERS_T WHERE ROLE_TYP = '1001' AND CARR_CD IN ('FXNL','FXFE','UPGF','RDWY','YRCW','YRCF','YRCO','ABFS')
GROUP BY CARR_CD

Query 2:

SELECT X.SCAC_CARRIER_CODE SCAC,
X.SRC_SYS,
X.RQST_ID,
TO_CHAR(X.CREATEDTS,'MM/DD/YYYY') ERR_DT,
X1.*,
X2.*
FROM ABPPBB_2016.CARRIER_EMAIL_RQST x,
XMLTABLE('/rejectedShipments/Item'
PASSING XMLType(EMAIL_DATA,1)
COLUMNS
PO_NUMBER VARCHAR2(30) PATH 'poId',
error_msg VARCHAR2(60) PATH 'errorMessage'
) x1,
XMLTABLE('/MQ212/header'
PASSING XMLType(SRC_MSG,1)
COLUMNS
to_location VARCHAR2(10) PATH 'N104ShpToLoc',
trailer_num VARCHAR2(10) PATH 'MS202TrlNbr'
) x2
WHERE x.src_sys = 'EDI 212'
AND TRUNC(X.CREATEDTS) >= TRUNC(SYSDATE - 10)
AND X2.TO_LOCATION IN ('74','75','78','79','710','715','718','722','723','724','726','728','729','734','2605','2607','70','77','84','87','717','725','781', '1376')
AND X.SCAC_CARRIER_CODE IN ('FXNL','FXFE','UPGF','RDWY','YRCW','YRCF','YRCO','ABFS')
Re: Using LISTAGG in a subquery [message #672001 is a reply to message #672000] Thu, 27 September 2018 09:09 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
You are FUNNY!

We don't have your tables.
We don't have your data.
We don't have your requirements.

What exactly is the expected or desired results since the two queries have different number & datatypes returned?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Using LISTAGG in a subquery [message #672003 is a reply to message #672001] Thu, 27 September 2018 09:17 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Seems simple enough
SELECT X.SCAC_CARRIER_CODE SCAC,
       X.SRC_SYS,
       X.RQST_ID,
       TO_CHAR(X.CREATEDTS,'MM/DD/YYYY') ERR_DT,
       X1.*,
       X2.*,
       LISTAGG(EMAL, ', ') WITHIN GROUP (ORDER BY EMAL) "Email_Address"
FROM ABPPBB_2016.CARRIER_EMAIL_RQST x,
     JDATM_2016PRD.PERS_T pt
     <xml tables>
WHERE ROLE_TYP = '1001' AND CARR_CD IN ('FXNL','FXFE','UPGF','RDWY','YRCW','YRCF','YRCO','ABFS')
AND pt.carr_cd = x.SCAC_CARRIER_CODE
<rest of where clause from query 2>
GROUP BY <all columns>
Re: Using LISTAGG in a subquery [message #672010 is a reply to message #672003] Thu, 27 September 2018 11:27 Go to previous message
ssmith001
Messages: 35
Registered: August 2018
Member
Thanks CookieMonster, I was able to get it to work with your assistance.
Previous Topic: Create collection of nested table
Next Topic: Query for Matching Record --> as much matches
Goto Forum:
  


Current Time: Wed Sep 30 21:40:08 CDT 2020