PS Query & SQL

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Good morning Bob & Kendall,

I wasn't able to locate Post #44 in my blizzard of emails, so I apologize if this is redundant.

I use the following INSTR expression and criteria. As previously mentioned, this method is limited by the field width of PSQuery prompts, and by the character-count of whatever field you're using in the prompt. Prompt values are comma-space separated when entered, e.g. 

XXXXX1, XXXXX2, XXXXX3, XXXXX4, etc.

In this example, I'm prompting for EmplID. Univ. of Colorado uses 6-digit EmplIDs, and I think I can search five at a time. I don't use this option very often. My customers are usually FAs. But it does have its applications.

(CASE

WHEN REGEXP_INSTR(:1, A.EMPLID) > 0

THEN A.EMPLID

END)

=

A.EMPLID

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Good morning Bob & Kendall,

I wasn't able to locate Post #44 in my blizzard of emails, so I apologize if this is redundant.

I use the following INSTR expression and criteria. As previously mentioned, this method is limited by the field width of PSQuery prompts, and by the character-count of whatever field you're using in the prompt. Prompt values are comma-space separated when entered, e.g. 

XXXXX1, XXXXX2, XXXXX3, XXXXX4, etc.

In this example, I'm prompting for EmplID. Univ. of Colorado uses 6-digit EmplIDs, and I think I can search five at a time. I don't use this option very often. My customers are usually FAs. But it does have its applications.

(CASE

WHEN REGEXP_INSTR(:1, A.EMPLID) > 0

THEN A.EMPLID

END)

=

A.EMPLID

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I think the posts may renumber themselves if one of us adds a reply to a "higher up" post.

We've utilized this prompt method before, and agree that INSTR(:1, A.EMPLID) > 0 is the only way we've found to allow this.  Calling this a "limited number" is a bit unfair since a PS Text prompt can handle 4,000 characters (this is the PS universal limit for a Char vs CLOB)  So even if you're doing 'EMPLID1', 'EMPLID2',... you could get at least 266 IDs (assuming you use all 11 characters in EMPLID).

@Scott Frey, I'm wondering why you use REGEXP_INSTR(...) rather than INSTR(...).  Is there an advantage to this?

Good morning Bob & Kendall,

I wasn't able to locate Post #44 in my blizzard of emails, so I apologize if this is redundant.

I use the following INSTR expression and criteria. As previously mentioned, this method is limited by the field width of PSQuery prompts, and by the character-count of whatever field you're using in the prompt. Prompt values are comma-space separated when entered, e.g. 

XXXXX1, XXXXX2, XXXXX3, XXXXX4, etc.

In this example, I'm prompting for EmplID. Univ. of Colorado uses 6-digit EmplIDs, and I think I can search five at a time. I don't use this option very often. My customers are usually FAs. But it does have its applications.

(CASE

WHEN REGEXP_INSTR(:1, A.EMPLID) > 0

THEN A.EMPLID

END)

=

A.EMPLID

Speaking of ANY()/ALL(), does anyone know if it is possible to use a prompt to pass the list of values.   I've tried a few things, but nothing worked.

So, Instead of ANY('11111111','22222222',...), something like ANY(:1).  Can it be done?

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

ARTP - A&R Temp Group

I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

ARTP - A&R Temp Group

I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

ARTP - A&R Temp Group

I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

For those utilizing Student Groups, may I make an alternate suggestion?  Student Blocks (assuming you have access).  A student block can be populated using an external file but would be totally invisible to anyone/everyone else.  You could then query the ID's in that student block similarly to how you would for those in a student group.

I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

ARTP - A&R Temp Group

I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

Scott - are you saying that the advantage of using student blocks over student groups is that student blocks cannot be viewed by others? I can see student blocks created by others.

For those utilizing Student Groups, may I make an alternate suggestion?  Student Blocks (assuming you have access).  A student block can be populated using an external file but would be totally invisible to anyone/everyone else.  You could then query the ID's in that student block similarly to how you would for those in a student group.

I actually use "Student Groups" to do this; I think it is much easier and doesn't limit the number of IDs I'm trying to get data on.  I have a couple of student groups to do this, but for in my area, I have:

ARTP - A&R Temp Group

I then make all students in ARTP inactivate from this student group and activate the IDs I need from an external file.  Then I write my query to find the data I need for these students.  In PUM 36, I can actually delete these out of the group, so that should make this process cleaner in the future.  

I agree - this ANY solution makes much more sense than the 1 = 1 workaround.

I love this one, and I learned it from you, Scott! But then at this year's Alliance, I mentioned it, and some delightful soul (whose name I have shamefully forgotten....please identify yourself if you're reading this!) told me you can use "ANY" instead to make it simpler. All you have to do is set EMPLID = [an expression]. The expression text is ANY('10000001','10000002','1000003') except of course you use real EMPLIDs. Then you can skip the "and 1 = 1" bit!

One additional note:

This expression works great in PSQuery, but the number of listed values is 1,000 per expression. However, I haven't found a limit on the number of such expressions that can be used in a single query. I've successfully used 26. All but the first expression was added in criteria as an <OR>, and then all of the expression criteria rows were grouped.

One additional note:

This expression works great in PSQuery, but the number of listed values is 1,000 per expression. However, I haven't found a limit on the number of such expressions that can be used in a single query. I've successfully used 26. All but the first expression was added in criteria as an <OR>, and then all of the expression criteria rows were grouped.

Simple but beautiful: LISTAGG(DISTINCT...)

Mic drop.

I use NTH_VALUE for this purpose.  So for your example I would do something like

NTH_VALUE(A.UNT_TAKEN_PRGRSS, 1) OVER (PARTITION BY A.EMPLID ORDER BY A.STRM DESC)

I use NTH_VALUE for this purpose.  So for your example I would do something like

NTH_VALUE(A.UNT_TAKEN_PRGRSS, 1) OVER (PARTITION BY A.EMPLID ORDER BY A.STRM DESC)

What a great idea to share these!

Okay... I don't really like writing (or reading) subqueries in PSQuery. They are cumbersome to build, modify... and even navigate when looking at a query, so I try to avoid them when I can. Also for whatever reason, whenever I write a query against the record, STDNT_FA_TERM that also involves subqueries, it runs super-slowly.

In my experience, there are primarily two functional reasons to use a subquery:

1. To limit the number of outputted rows based on a MAX() or MIN() value from the subquery.
2. To exclude a sub-population of rows based on some logic that cannot be written in the top-level of the query.

I have funky work-arounds for both of these scenarios, but only 1. involves an expression, so I will focus on that.

Example: I want to find the number of credits students take during their final term. Querying the record, STDNT_CAR_TERM, I could return EMPLID, and UNT_TAKEN_PRGRSS and create a subquery, where STRM = MAX STRM from a subquery, with all of the relevant criteria from the top level duplicated:

Top level > Subquery fields/criteria

In this example, there aren't many duplicated criteria, just to keep it simple, but in many use cases, you could have dozens of lines of logic to replicate - which isn't fun, and can lead to errors.

So instead of writing the query this way, I will often use a combo of aggregates + concatenation + substr() to achieve the same results (mostly):

By taking the MAX of STRM and appending the units taken to it, you get rid of the need to use a subquery to determine that value. Then you wrap the whole thing in a SUBSTR() to extract the parts that you want to keep.

SUBSTR(MAX(A.STRM || A.UNT_TAKEN_PRGRSS),1,4)

SUBSTR(MAX(A.STRM || A.UNT_TAKEN_PRGRSS),5,3)

A couple of things to note: 1) You will need to select "Aggregate Function" on each expression to get it to work. 2) Remember, "(mostly)" from before? Well, this  method does NOT actually remove the rows like a subquery would, it just suppresses them. So use caution if you are also using SUM() or COUNT() in your query.

There may be times when a subquery still makes more sense, but I find myself using this method more often than not.

Jeffrie

--

Jeffrie Brooks | BUSINESS SYSTEM ANALYST UNIVERSITY OF MICHIGAN | INFORMATION AND TECHNOLOGY SERVICES 734-647-8763 | jedobr@umich.edu

Here is something kind of simple, but nice for your end-users. Flatten output to one row per student and make user-friendly:

FA award short description, status, and amount:
LISTAGG(C.DESCRSHORT || ' (' || DECODE(B.AWARD_STATUS,'C','Cancelled','D','Declined','Accepted') || ') ' || B.DISBURSED_AMOUNT, ' – ') WITHIN GROUP (ORDER BY B.ITEM_TYPE)

Output:
Univ Gnt (Accepted) 1732 – UnsubLn (Accepted) 990 – UnsubLn2 (Accepted) 1979 – Plus Ln (Cancelled) 0

Academic plan description and code:
LISTAGG(B.DESCR || ' (' || B.ACAD_PLAN || ') ', ' / ')

Output:
Electromechanical Technology (ET-AASEEET) / Electr-Mechanical Technology (EMECT-AAS)

FA Load by Term:
LISTAGG(G.STRM || ' (' || G.FA_LOAD || ')', ' / ') WITHIN GROUP (ORDER BY G.STRM)

Output:
2205 (L) / 2208 (F)

------------------------------
Patrick Beatty
Assistant Director
Michigan State University
------------------------------

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

It just about blew my mind when I learned that I can have an arbitrary sort order for the results of a SQL script.  For example:

select * from ps_addresses
order by 
  case country
   when ' ' then '1'
   when 'USA' then '2'
   when 'CAN' then '3'
   when 'MEX' then '4'
   else country
end ;

:exploding_head: