WITH RECURSIVE Syntax

Database
Enthusiast

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.

Tags (1)
2 REPLIES
Enthusiast

Re: WITH RECURSIVE Syntax

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;

Re: WITH RECURSIVE Syntax

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?