PS Query & SQL

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

Other possibilities would have been a LIKE comparison, or TRIMming both sides of the comparison.

But Mital raises a good point - why were you joining on a name field?

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

Other possibilities would have been a LIKE comparison, or TRIMming both sides of the comparison.

But Mital raises a good point - why were you joining on a name field?

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

Other possibilities would have been a LIKE comparison, or TRIMming both sides of the comparison.

But Mital raises a good point - why were you joining on a name field?

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

If I every try to do a join where I now there is a possibility for mixed case usage, I usually wrap the field in the UPPER() function.  I have even done this with prompts as well.  For example UPPER(:1).

------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------

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: 05-09-2025 12:58 PM
From: Jessica Holler
Subject: I learned something interesting today about apparently identical fields

Original Message:
Sent: 5/9/2025 12:08:00 PM
From: Garrett Fitzgerald
Subject: RE: I learned something interesting today about apparently identical fields

Other possibilities would have been a LIKE comparison, or TRIMming both sides of the comparison.

But Mital raises a good point - why were you joining on a name field?

------------------------------
Garrett Fitzgerald
Senior Analyst Programmer I
University of Maine

Original Message:
Sent: 05-07-2025 03:30 PM
From: Jessica Holler
Subject: I learned something interesting today about apparently identical fields

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

------------------------------
Jessica Holler
Systems Integration Analyst
DePaul University
jshisler@depaul.edu
------------------------------

100% agree, best practice if joining/comparing character fields would be to force them to matching case. You can actually set up the prompt itself to automatically format the input to UPPER for this very reason.

------------------------------
Scott Nishizaki
Developer/Analyst
Azusa Pacific University
------------------------------

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: 05-12-2025 12:05 PM
From: Daniel Labrecque
Subject: I learned something interesting today about apparently identical fields

If I every try to do a join where I now there is a possibility for mixed case usage, I usually wrap the field in the UPPER() function.  I have even done this with prompts as well.  For example UPPER(:1).

------------------------------
Daniel Labrecque
Senior 2 Business Systems Analyst/Functional Architect
University of Nevada, Las Vegas
------------------------------

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: 05-09-2025 12:58 PM
From: Jessica Holler
Subject: I learned something interesting today about apparently identical fields

Original Message:
Sent: 5/9/2025 12:08:00 PM
From: Garrett Fitzgerald
Subject: RE: I learned something interesting today about apparently identical fields

Other possibilities would have been a LIKE comparison, or TRIMming both sides of the comparison.

But Mital raises a good point - why were you joining on a name field?

------------------------------
Garrett Fitzgerald
Senior Analyst Programmer I
University of Maine

Original Message:
Sent: 05-07-2025 03:30 PM
From: Jessica Holler
Subject: I learned something interesting today about apparently identical fields

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

------------------------------
Jessica Holler
Systems Integration Analyst
DePaul University
jshisler@depaul.edu
------------------------------

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

This is great.  Thanks for sharing.

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica

Greetings Friends,

This is probably old news to a lot of people, but I'm so happy I figured this out that I wanted to share this.

I have been working on a query to find EmplID from a private loan application.  The loan application contains all of the PII I need to complete the search.

After converting date of birth from a character field using CCYYMMDD to MM/DD/CCYY using an expression, everything was working fine joining the expression to the date field, but I knew that wasn't nearly a good enough match. 

I wanted to add a join on last Last Name.  Both fields are character fields, so everything should be fine...but nope!  I got 0 rows returned.

After a few minutes looking like a confused puppy, I took another look at the Fields tab in Query Manager.  The Last Name fields were, indeed, both character fields, but one is 30 characters long and the other is 35 characters long.

On a whim, I created an expression for the shorter last name field to create one 35 characters long, joined on the expression, and it worked!

Here's a screenshot of the expression:

Thanks for listening!

Cheers,
Jessica