OraFAQ Forum: SQL & PL/SQL » Using LISTAGG in a subquery

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using LISTAGG in a subquery [message #672000]

Thu, 27 September 2018 09:00

ssmith001
Messages: 37
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')

Report message to a moderator

Re: Using LISTAGG in a subquery [message #672001 is a reply to message #672000]

Thu, 27 September 2018 09:09

BlackSwan
Messages: 26766
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

Report message to a moderator

Re: Using LISTAGG in a subquery [message #672003 is a reply to message #672001]

Thu, 27 September 2018 09:17

cookiemonster
Messages: 13904
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>

Report message to a moderator

Re: Using LISTAGG in a subquery [message #672010 is a reply to message #672003]

Thu, 27 September 2018 11:27

ssmith001
Messages: 37
Registered: August 2018

Member

Thanks CookieMonster, I was able to get it to work with your assistance.

Report message to a moderator

Previous Topic:	Create collection of nested table
Next Topic:	Query for Matching Record --> as much matches

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun May 28 17:35:26 CDT 2023