All Forums Database
newton1965 12 posts Joined 06/13
24 Oct 2013
WITH RECURSIVE Syntax

I'm having difficulty figuring out how to get the desired results using WITH RECURSIVE in the query below:

WITH RECURSIVE WS_REAS_TXT (UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS, RISK_REASON_TEXT) AS
(
  SELECT a.UWI, a.RISK_CATEGORY_ID, a.RISK_REASON_DETAILS, a.RISK_REASON_TEXT
  FROM  IDW_CORE_V.IDW_WELLSITE a
  WHERE a.UWI = '4225531657'
  UNION ALL
  SELECT a.UWI, a.RISK_CATEGORY_ID, a.RISK_REASON_DETAILS, a.RISK_REASON_TEXT || ' / ' || b.RISK_REASON_TEXT
  FROM   IDW_CORE_V.IDW_WELLSITE b, IDW_CORE_V.IDW_WELLSITE a
  WHERE  a.UWI = b.UWI
  AND a.UWI = '4225531657'
)
SELECT UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS,RISK_REASON_TEXT
FROM   WS_REAS_TXT 
QUALIFY RANK() OVER(PARTITION BY UWI ORDER BY LENGTH(RISK_REASON_TEXT) DESC) = 1

UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS columns are unique.  The RISK_REASON_TEXT column has two records based on my filter.  The UNION ALL is producing 6 variations...
House or Occupied Facility within 1/4 mile (provide actual footage)
Paved Roadway within 1/4 mile (provide actual footage)
House or Occupied Facility within 1/4 mile (provide actual footage) / House or Occupied Facility within 1/4 mile (provide actual footage)
Paved Roadway within 1/4 mile (provide actual footage) / House or Occupied Facility within 1/4 mile (provide actual footage)
House or Occupied Facility within 1/4 mile (provide actual footage) / Paved Roadway within 1/4 mile (provide actual footage)
Paved Roadway within 1/4 mile (provide actual footage) / Paved Roadway within 1/4 mile (provide actual footage)
The full query provides the following result, which is a dup of one of the two records:
House or Occupied Facility within 1/4 mile (provide actual footage) / House or Occupied Facility within 1/4 mile (provide actual footage)
What I'm attempting to do is join the two records into a single column with the expected result of:
House or Occupied Facility within 1/4 mile (provide actual footage) / Paved Roadway within 1/4 mile (provide actual footage)
 
Thanks for any help you can provide.
 
 

newton1965 12 posts Joined 06/13
24 Oct 2013

Solved...
 

WITH RECURSIVE CONCAT_LIST(UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS, RISK_REASON_TEXT, LVL) AS
(
  SELECT UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS, RISK_REASON_TEXT (VARCHAR(1000)), 1
  FROM  IDW_CORE_V.IDW_WELLSITE
  WHERE UWI = '4225531657'
  UNION ALL
  SELECT CL.UWI, WR.RISK_CATEGORY_ID, WR.RISK_REASON_DETAILS, WR.RISK_REASON_TEXT || ' / ' || CL.RISK_REASON_TEXT, LVL+1
  FROM   IDW_CORE_V.IDW_WELLSITE WR
  INNER JOIN CONCAT_LIST CL ON WR.UWI = CL.UWI
  WHERE WR.RISK_REASON_TEXT > CL.RISK_REASON_TEXT
  AND WR.UWI = '4225531657'
)
SELECT UWI, RISK_CATEGORY_ID, RISK_REASON_DETAILS,RISK_REASON_TEXT
FROM   CONCAT_LIST 
QUALIFY RANK() OVER(PARTITION BY UWI ORDER BY LVL DESC) = 1;

 

sriemvee 1 post Joined 12/13
18 Dec 2013

Hi,
i have used a similar with recursive function at my work and  got the desired results.
The with recursive funtion returns values for me when i used it in an ETL tool. But am not able to view the data in TD SQL assistant. When i run the query, i get only a message "11512 rows selected". but the output of the funtion is not available. What do we need to do to bring the output on screen in SQL Assistant? 
 
 
 

Thanks,
Srinivasan V

You must sign in to leave a comment.