Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Robert,

I had no idea that these two functions existed; thank you for sharing them! I have also been using the same technique as Michelle, but ANY() and ALL() will go a long way to make our queries/SQL easier to read.

This group is awesome! I am very excited to see all of the cool things that come out of it.

Jeffrie

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Robert,

I had no idea that these two functions existed; thank you for sharing them! I have also been using the same technique as Michelle, but ANY() and ALL() will go a long way to make our queries/SQL easier to read.

This group is awesome! I am very excited to see all of the cool things that come out of it.

Jeffrie

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

I haven't either!  I will have to give this one a try!  Thanks for sharing Paul's presentation too Dana!  These are great resources!

Robert,

I had no idea that these two functions existed; thank you for sharing them! I have also been using the same technique as Michelle, but ANY() and ALL() will go a long way to make our queries/SQL easier to read.

This group is awesome! I am very excited to see all of the cool things that come out of it.

Jeffrie

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

I haven't either!  I will have to give this one a try!  Thanks for sharing Paul's presentation too Dana!  These are great resources!

Robert,

I had no idea that these two functions existed; thank you for sharing them! I have also been using the same technique as Michelle, but ANY() and ALL() will go a long way to make our queries/SQL easier to read.

This group is awesome! I am very excited to see all of the cool things that come out of it.

Jeffrie

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Robert,

I've never used the expressions ANY / ALL. Thanks for sharing these expressions. I've tried both the expressions for more than 1000 values for in list with ORs and criteria grouping. This is so cool and awesome!

typically, I use equal to ANY() in the expression for "in list"

and not equal to ALL() for "not in list"

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

------------------------------
Michelle Jackson
Director of Operations for Financial Aid
University of Pittsburgh
------------------------------

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

We do something similar to focus on a certain student population, but we use an Expression.

------------------------------
Tiffany Yuen MS
Information Technology Business/Technical Analyst
Los Rios Community College District

Original Message:
Sent: 01-21-2025 09:56 AM
From: Michelle Jackson
Subject: In List -

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

------------------------------
Michelle Jackson
Director of Operations for Financial Aid
University of Pittsburgh
------------------------------

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

Hi all, 

We also use something similar to this, as well:

This allows us to drop an SID string into the expression (max 285 students). Thanks for sharing other alternatives!

Elizabeth O'Connor

We do something similar to focus on a certain student population, but we use an Expression.

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Hi all, 

We also use something similar to this, as well:

This allows us to copy/paste an SID string straight from excel into the expression (max 285 students). Thanks for sharing other alternatives!

Elizabeth O'Connor

------------------------------
Elizabeth O'Connor
System Operations Manager
University of Colorado
------------------------------

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

Original Message:
Sent: 01-21-2025 05:12 PM
From: Tiffany Yuen
Subject: In List -

We do something similar to focus on a certain student population, but we use an Expression.

------------------------------
Tiffany Yuen MS
Information Technology Business/Technical Analyst
Los Rios Community College District

Original Message:
Sent: 01-21-2025 09:56 AM
From: Michelle Jackson
Subject: In List -

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

------------------------------
Michelle Jackson
Director of Operations for Financial Aid
University of Pittsburgh
------------------------------

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

Hi all, 

We also use something similar to this, as well:

This allows us to copy/paste an SID string straight from excel into the expression (max 285 students). Thanks for sharing other alternatives!

Elizabeth O'Connor

------------------------------
Elizabeth O'Connor
System Operations Manager
University of Colorado
------------------------------

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

Original Message:
Sent: 01-21-2025 05:12 PM
From: Tiffany Yuen
Subject: In List -

We do something similar to focus on a certain student population, but we use an Expression.

------------------------------
Tiffany Yuen MS
Information Technology Business/Technical Analyst
Los Rios Community College District

Original Message:
Sent: 01-21-2025 09:56 AM
From: Michelle Jackson
Subject: In List -

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

------------------------------
Michelle Jackson
Director of Operations for Financial Aid
University of Pittsburgh
------------------------------

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

This is so smart! Nice to meet you Lea. My name is Shad I work for CSN in Admissions. I never thought about doing this, but this is such a good idea. I will need to remember this if I ever need to do large groups of queries at a time.

Hi all, 

We also use something similar to this, as well:

This allows us to copy/paste an SID string straight from excel into the expression (max 285 students). Thanks for sharing other alternatives!

Elizabeth O'Connor

------------------------------
Elizabeth O'Connor
System Operations Manager
University of Colorado
------------------------------

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

Original Message:
Sent: 01-21-2025 05:12 PM
From: Tiffany Yuen
Subject: In List -

We do something similar to focus on a certain student population, but we use an Expression.

------------------------------
Tiffany Yuen MS
Information Technology Business/Technical Analyst
Los Rios Community College District

Original Message:
Sent: 01-21-2025 09:56 AM
From: Michelle Jackson
Subject: In List -

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

------------------------------
Michelle Jackson
Director of Operations for Financial Aid
University of Pittsburgh
------------------------------

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

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

I've done the various apostrophe/quote hacks in the past in Excel with Student IDs too for lists; I used to think, 'I'm probably the only fool doing this and I'm missing something more efficient.' It's fun to see that it's the approach many others are using too!

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

I've done the various apostrophe/quote hacks in the past in Excel with Student IDs too for lists; I used to think, 'I'm probably the only fool doing this and I'm missing something more efficient.' It's fun to see that it's the approach many others are using too!

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle

Thank you all for the additional great tips!  Here is the formula I use in excel to assist in adding the quotes and commas to make it a lot easier:

To format the numbers in excel, if your IDs are in column B and there is a header row, you can enter the following formula in column A and copy down: 

="'"&B2&"',"

Michelle,

That is a great suggestion, thanks for sharing it with the community!

I have actually employed this same strategy recently, and it's so much easier, especially when you have a long list.

Here's a tip: if you have a lengthy list and your formatting is incorrect-like missing single quotes around individual items or delimiting commas-you can use a bit of code in Notepad++ or other programs like Excel or Python to quickly correct the formatting. This way, you can easily copy and paste it into the expression without having to edit each item manually. My list of 100 or so emplids required this and this trick made life a whole lot easier!

Good Morning,

Sharing a way in Query Manager to use expressions in the where clause that allows you to paste lists of data into your queries.  This has been very useful for when we are asked to provide data from PeopleSoft based on a list of IDs.  In my example below, I have a list of IDs that I want to pull the Primary Academic Program and Plan from STDNT_FA_TERM.  Thought I would share in the hopes of helping someone else.  

Thank you,

Michelle