PS Query & SQL

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post:  

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!

What's your favorite trick, case statement, expression in SQL (or PS Query)?  If you have a favorite trick (or treat), please share it!

Mine is still the one that Jeffrie Brooks shared in a FA post awhile back to get your sum totals to stop multiplying by the number of rows of data coming back.  I've been using it ever since and I LOVE it.  Thank you, Jeffrie!

SUM(A.OFFER_AMOUNT)*COUNT(DISTINCT A.EMPLID || A.INSTITUTION || A.AID_YEAR || A.ITEM_TYPE || A.ACAD_CAREER)/COUNT(*)

Since this was on a different forum, I'm copying Jeffrie's total response below instead of linking to the post: