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

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

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.

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

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

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.

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

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

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

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

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

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

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

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