Hi folks,

Anyone up for a challenge? I reached out to my colleagues at Duke and thought "why not see what the PS Query masters of the universe have to say about my puzzle?" I think I've stretched my query skills as far as they'll go.

Scenario: I've got some SQL code that I need to translate and replicate in PS Query Manager without App Designer. 

The Error: Database Driver Error (30,11)

The "Problem children":

#1

ROW_NUMBER() OVER (PARTITION BY A.ACAD_ORG ORDER BY B.ACAD_CAREER) AS rn
   FROM PS_ACAD_ORG_TBL A

.....

   WHERE rn = 1

• For each ACAD_ORG, we want to have rows numbered starting from 1, ordered by ACAD_CAREER. How might foks account for ROW_NUMBER directly in PS Query since it doesn't inherently or directly support this function, as far as I know (but I might be wrong)? Would you use a subquery? 
• As a workaround, I tried using an expression in order to show only the min or max career.

CASE
    WHEN MIN(B.ACAD_CAREER) IS NOT NULL
         THEN MIN(B.ACAD_CAREER)
         ELSE MAX(E.ACAD_CAREER)
END

#2

   LISTAGG(DISTINCT COALESCE(B.SUBJECT, ''), '|') WITHIN GROUP (ORDER BY B.SUBJECT) 

• We want to take all the subjects for a group, remove duplicates, replace nulls with blanks, sort them alphabetically, and concatenate them into one string separated by |. This is what gives me the Database Driver Error.

The Tables:

ACAD_ORG_TBL

CRSE_OFFER

ACAD_PLAN_OWNER

ACAD_PLAN_TBL

ACAD_PROG_TBL

I attached the raw SQL I was given as well as the SQL generated from my attempt in PS Query Manager. Thoughts or recommendations would be much appreciated! Let me know if anyone would like to help solve this problem and if I should share screenshots from the PS Query Manager pages. 

Best,

Anna 

Side note: I "chatted" with ChatGPT, Juliusai, and Perplexity - and after having provided the context, problem, desired solution, and avialalbe tools through multiple prompting - guess what they all recommended at the end?  --> Custom view in App Designer

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Thanks for your reply, Dana. That is one of the things we need to do. I have been using the listagg with distinct to achieve this over the regexp replace combo and it works great until I attempt to coalesce.  I will try the regular expression to see what that renders. 

My overall painpoint is just generally trying to translate the raw SQL into PS Query Manager. I don't use SQL developer or have access to App Designer.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Original Message:
Sent: 09-24-2025 08:17 AM
From: Dana Pawlowicz
Subject: Translating SQL to PS Query Manager

So, if your main issue is removing dupes from LISTAGG, Jamie Polizzi had that in her query presentation.  I have more that one version of her presentations and I literally look up LISTAGG in it every time.  For whatever reason, I can't memorize this one.

 

Anyway,

 

To remove dupes (from Jamie):

 

regexp_replace(LISTAGG ((YOURFIELD), ',') within group (order by YOURFIELD),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')

 

 

I have this in a few queries and it works well.  I had a mack daddy query that kept throwing an error that said I was concatenating too much and this revision fixed it.  And to give proper credit, ChatGPT told me how to fix it.  I didn't come up with that on my own.

 

REGEXP_REPLACE( LISTAGG(YOURFIELD, ',' ON OVERFLOW TRUNCATE WITHOUT COUNT)    
WITHIN GROUP (ORDER BY YOURFIELD),  '([^' || CHR(44) || ']+)(' || CHR(44) || '\1)*(' || CHR(44) || '|$)',  '\1\3')

 

The only thing I've ever done with row number is to only pull back so many rows of data if it's a big one and I want to tweak it in testing.    So, hoping I helped with #2.  I don't have a clue about #1.

 

So, I tried both versions and got a Database Driver Error. (30,11) for the first. The second gave me duplicate values.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Original Message:
Sent: 09-24-2025 08:26 AM
From: Anna Kourouniotis
Subject: Translating SQL to PS Query Manager

Thanks for your reply, Dana. That is one of the things we need to do. I have been using the listagg with distinct to achieve this over the regexp replace combo and it works great until I attempt to coalesce.  I will try the regular expression to see what that renders. 

My overall painpoint is just generally trying to translate the raw SQL into PS Query Manager. I don't use SQL developer or have access to App Designer.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------


Original Message:
Sent: 09-24-2025 08:17 AM
From: Dana Pawlowicz
Subject: Translating SQL to PS Query Manager

So, if your main issue is removing dupes from LISTAGG, Jamie Polizzi had that in her query presentation.  I have more that one version of her presentations and I literally look up LISTAGG in it every time.  For whatever reason, I can't memorize this one.

 

Anyway,

 

To remove dupes (from Jamie):

 

regexp_replace(LISTAGG ((YOURFIELD), ',') within group (order by YOURFIELD),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')

 

 

I have this in a few queries and it works well.  I had a mack daddy query that kept throwing an error that said I was concatenating too much and this revision fixed it.  And to give proper credit, ChatGPT told me how to fix it.  I didn't come up with that on my own.

 

REGEXP_REPLACE( LISTAGG(YOURFIELD, ',' ON OVERFLOW TRUNCATE WITHOUT COUNT)    
WITHIN GROUP (ORDER BY YOURFIELD),  '([^' || CHR(44) || ']+)(' || CHR(44) || '\1)*(' || CHR(44) || '|$)',  '\1\3')

 

The only thing I've ever done with row number is to only pull back so many rows of data if it's a big one and I want to tweak it in testing.    So, hoping I helped with #2.  I don't have a clue about #1.

 

Original Message:
Sent: 9/24/2025 8:29:00 AM
From: Anna Kourouniotis
Subject: RE: Translating SQL to PS Query Manager

So, I tried both versions and got a Database Driver Error. (30,11) for the first. The second gave me duplicate values.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------


Original Message:
Sent: 09-24-2025 08:26 AM
From: Anna Kourouniotis
Subject: Translating SQL to PS Query Manager

Thanks for your reply, Dana. That is one of the things we need to do. I have been using the listagg with distinct to achieve this over the regexp replace combo and it works great until I attempt to coalesce.  I will try the regular expression to see what that renders. 

My overall painpoint is just generally trying to translate the raw SQL into PS Query Manager. I don't use SQL developer or have access to App Designer.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Thanks for the suggestion, Jeffrie. 

Tried it but got this: An error occurred. Please consult your system log for details.
Error in running query because of SQL Error, Code=30483, Message=ORA-30483: window functions are not allowed here (50,380)

This code also doesn't account for the need to locate the first non-null acad_career from ACAD_PROG_TBL or CRSE_OFFER.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Original Message:
Sent: 09-24-2025 08:39 AM
From: Jeffrie Brooks
Subject: Translating SQL to PS Query Manager

For the row number piece, could you try using the RANK() function? So:

RANK() OVER (PARTITION BY A.ACAD_ORG ORDER BY B.ACAD_CAREER)

On Wed, Sep 24, 2025 at 8:29 AM Anna Kourouniotis via Higher Education User Group <Mail@heug.org> wrote:

So, I tried both versions and got a Database Driver Error. (30,11) for the first. The second gave me duplicate values. -------------------------... -posted to the "PS Query & SQL" group

PS Query & SQL Post New Discussion Re: Translating SQL to PS Query Manager Reply to Thread Sep 24, 2025 8:29 AM Anna Kourouniotis So, I tried both versions and got a Database Driver Error. (30,11) for the first. The second gave me duplicate values. ------------------------------ HEUG Community of Practice Reporting, Analytics, and Data Governance Subcommittee Anna Kourouniotis MA Database Analyst II Duke University ------------------------------   Reply to Community   Reply to Sender via Email   View Thread   Recommend   Original Message: Sent: 09-24-2025 08:26 AM You are subscribed to "PS Query & SQL" as jedobr@umich.edu. To change your subscriptions, go to My Subscriptions. To remove yourself from this community discussion, you can unsubscribe at any time.

Original Message:
Sent: 9/24/2025 8:29:00 AM
From: Anna Kourouniotis
Subject: RE: Translating SQL to PS Query Manager

So, I tried both versions and got a Database Driver Error. (30,11) for the first. The second gave me duplicate values.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------


Original Message:
Sent: 09-24-2025 08:26 AM
From: Anna Kourouniotis
Subject: Translating SQL to PS Query Manager

Thanks for your reply, Dana. That is one of the things we need to do. I have been using the listagg with distinct to achieve this over the regexp replace combo and it works great until I attempt to coalesce.  I will try the regular expression to see what that renders. 

My overall painpoint is just generally trying to translate the raw SQL into PS Query Manager. I don't use SQL developer or have access to App Designer.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University


Original Message:
Sent: 09-24-2025 08:17 AM
From: Dana Pawlowicz
Subject: Translating SQL to PS Query Manager

So, if your main issue is removing dupes from LISTAGG, Jamie Polizzi had that in her query presentation.  I have more that one version of her presentations and I literally look up LISTAGG in it every time.  For whatever reason, I can't memorize this one.

 

Anyway,

 

To remove dupes (from Jamie):

 

regexp_replace(LISTAGG ((YOURFIELD), ',') within group (order by YOURFIELD),'([^' || chr(44) || ']+)(' || chr(44) || '\1)*(' || chr(44) || '|$)', '\1\3')

 

 

I have this in a few queries and it works well.  I had a mack daddy query that kept throwing an error that said I was concatenating too much and this revision fixed it.  And to give proper credit, ChatGPT told me how to fix it.  I didn't come up with that on my own.

 

REGEXP_REPLACE( LISTAGG(YOURFIELD, ',' ON OVERFLOW TRUNCATE WITHOUT COUNT)    
WITHIN GROUP (ORDER BY YOURFIELD),  '([^' || CHR(44) || ']+)(' || CHR(44) || '\1)*(' || CHR(44) || '|$)',  '\1\3')

 

The only thing I've ever done with row number is to only pull back so many rows of data if it's a big one and I want to tweak it in testing.    So, hoping I helped with #2.  I don't have a clue about #1.

 

Anna,

Would this work?

count(1) over (partition by 1 order by 1  rows unbounded preceding)

------------------------------
Robert Fogarty PMP
Reporting Team Lead | LionPATH Development and Maintenance Office
The Pennsylvania State University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Good morning Anna,

I can't speak to the row number issue as I've never had occasion to work with it.

But regarding the LISTAGG issue, here are a few suggestions. I usually have to tweak the syntax to get exactly what I want in each query. But these are my starting points.  I also recommend the website:

https://www.techonthenet.com/oracle/functions/regexp_replace.php

It includes several excellent tables of syntax operators and examples of their use.

Tristan Bender's Non-repeating LISTAGG:

RTRIM(REGEXP_REPLACE((LISTAGG(D.BUSINESS_UNIT, ', ') WITHIN GROUP (ORDER BY D.BUSINESS_UNIT) ), '([^, ]*)(, \1)+($|, )', '\1, '), ', ')

Scott Frey's:

(REGEXP_REPLACE (LISTAGG (J.DEPTID, ', ') WITHIN GROUP (ORDER BY J.DEPTID),
'(\W[^,]+)(,[ ]*\1)+',
'\1'))

Alternate Scott Frey's:

(REGEXP_REPLACE (LISTAGG (J.DEPTID, ', ') WITHIN GROUP (ORDER BY J.DEPTID),
'([^,]+)(,[ ]*\1)+',
'\1'))

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado System
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

1) Unfortunately, PS Query will not handle Row_Number() as anything with partitions cannot be used as a criteria.  It is likely to require more work (and run slowly) but you can likely get away by using NTH_VALUE (or FIRST_VALUE)  on each field of PS_ACAD_ORG_TBL with the same partition and order clause.

2)  It seems that LISTAGG automatically ignores nulls and it's possible that an completely empty string '' is considered NULL for that purpose.  Perhaps you could use ' ' instead?  

------------------------------
Scott Nishizaki
Connected Campus Community of Practice
Developer/Analyst
Azusa Pacific University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Hi Anna,

If I am understanding this correctly, I think there is a way to do this using subqueries.  Every time ACAD_CAREER comes up, a separate subquery is needed to limit the population in place of using row_number.  Our configuration for these setup tables is quite different, but hopefully the attached screenshots and SQL help with getting the idea across of limiting to the first ACAD_CAREER each time by ACAD_ORG.  Along the lines of Scott Nishizaki comment, I added a '|' to the COALESCE of the listagg because listagg strips out nulls and blanks. Hope this helps

------------------------------
Ross Nolan
Data and Reporting Analysis
University of California-Berkeley
rvnolan@berkeley.edu
------------------------------

I want to give special thanks to everyone who responded to my post. After consideration and reavaluation of our needs, we dediced to go a different direction because this query would actually not serve the business need. Goes to show that you don't always need a complicated solution. Thanks again @Dana Pawlowicz, @Robert Fogarty, @Ross Nolan, @Scott Nishizaki, @Scott Frey, @Jeffrie Brooks! What a supportive community this is.

------------------------------
HEUG Community of Practice
Reporting, Analytics, and Data Governance Subcommittee

Anna Kourouniotis MA
Database Analyst II
Duke University
------------------------------

Original Message:
Sent: 09-25-2025 05:53 PM
From: Ross Nolan
Subject: Translating SQL to PS Query Manager

Hi Anna,

If I am understanding this correctly, I think there is a way to do this using subqueries.  Every time ACAD_CAREER comes up, a separate subquery is needed to limit the population in place of using row_number.  Our configuration for these setup tables is quite different, but hopefully the attached screenshots and SQL help with getting the idea across of limiting to the first ACAD_CAREER each time by ACAD_ORG.  Along the lines of Scott Nishizaki comment, I added a '|' to the COALESCE of the listagg because listagg strips out nulls and blanks. Hope this helps

------------------------------
Ross Nolan
Data and Reporting Analysis
University of California-Berkeley
rvnolan@berkeley.edu
------------------------------