Avatar of brianmfalls
brianmfalls
Flag for United States of America

asked on 

How do I access the data returned from Oracle's 'LISTAGG' function?

I have a simple query that is returning a list of record id's.  This is my first go using the LISTAGG function, so I'm unsure how to reference the data directly.  My question is two fold and should yield two answers.  First, how do I save the comma delimited list as a PHP variable?  Second, can I add the query into another query as the 'where' to delete all records with the id's returned from the LISTAGG query?  If so (to either or), how?  Thanks in advance.  :)

My query using LISTAGG:
$sql = sprintf("
	SELECT LISTAGG(LINK_ID, ',')
		WITHIN GROUP (ORDER BY LINK_ID)
	FROM LINK_TABLE
	WHERE PAGE_ID = '%s'
	",
	$PAGE_ID
);
$links = $readDB->runQuery($sql);
header('Content-type: application/json');
echo json_encode($links);

Open in new window


The query above yields the following:
[{"LISTAGG(LINK_ID,',')WITHINGROUP(ORDERBYLINK_ID)":"2044,2048,2052,2054,2056,2063,2065,2068,2070,2079,2081,2082"
}]

This is the query that I need the data for, and would like to integrate the aforementioned query into:
$sql = sprintf("
    DELETE FROM 
	OVERRIDES_TABLE
	WHERE LINK_ID IN ($LINK_ID)
);
$overrides = $writeDB->runQuery($sql);

Open in new window

Oracle DatabasePHP

Avatar of undefined
Last Comment
brianmfalls

8/22/2022 - Mon