PS Query & SQL

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Can you provide more concrete details?  Can you provide an example of the expression you are thinking of?

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Hm... it looks like there's an issue accessing the 2025 materials. I'd expect to see them here https://www.heug.org/resources/heug-files/a25-files, but it's taking me to a page to buy an on-demand pass for 2026 instead. I've sent an email to support.

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Hey All - I was able to get to the Prompt and Circumstance presentation.  Here is what I did.

I used the following URL to get to the Alliance Mobile site.  https://events.rdmobile.com/Home/Details/117 

1. After clicking the link, click the Login link in the upper right corner.  
2. Add your email address.Click the Login button. 
3. You will receive a email with a link in it. 
4. Click the link, and you will be logged into the mobile Alliance site. 
5. Click the Continue button. 
6. Click the All Events link. 
7. Choose the conference 
8. Use the navigation pane on the left side of the page to find presentations, presenters, etc. 
9. I have found searching for the Presenter the easiest way to find a presentation. 

Hm... it looks like there's an issue accessing the 2025 materials. I'd expect to see them here https://www.heug.org/resources/heug-files/a25-files, but it's taking me to a page to buy an on-demand pass for 2026 instead. I've sent an email to support.

Hi Timothy,

Can you point me to the session you are referring? I looked but can't find it. Unless the presentation didn't get recorded and/or the power point wasn't posted.

Thanks,

Lara

John,

I'm interested to see an example of what you have in mind, but I have worked on queries with monstrous (and repeated) expressions that do refer to prompts with default values. It's ugly, but easier than updating a large number of expressions and trying to keep everything synced up.

I'm not aware that PS Query supports the creation of variables like you're asking. However, Michael Williams at UConn gave a presentation at Alliance 2025 called "Prompt and Circumstance" in which he described tying variables in queries to the Field Conversion Definition tables. That's a place where you can save variables with field names and values in a static place, then make updates as you see fit. It takes some setup, but he demonstrated it as a way to update the references in an unlimited number of queries all at the same time (e.g. save all your odd year values and your even year values in one place, update the references when it's time, and now all your "odd" queries go from 2025 to 2027). That might scratch the itch you're describing!

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Hey Alex would you mind expanding on that? Are you talking about a specific page in PS? Quick google didn't make it clear to me - thanks!

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

You might be able to leverage process parameters, as they are query-able. Sort of depends on the requirements, but works well when you have lists, such as academic programs/plans or item types.

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John

Hi guys, the summer session of my presentation was open to everyone I'll attach that and the example SQL. 

I'm probably just fantasizing here but is there anyway to hardcode a variable in an expression and reference it later? Or in a query and reference it in an expression? I have a really complex expression I have to update somewhat frequently, it would save me a lot of time to just declare them at the top of an expression and change them there.

Would the only way to accomplish what I'm thinking of be to set Prompts with default values, then reference the prompts in the expression?

Just thinking out loud after a long day, thanks,

John