AnsweredAssumed Answered

Possible to have Users where 'User ID' is null/blank?

Question asked by Justin Gregory on Jul 17, 2020
Latest reply on Jul 29, 2020 by Justin Gregory

Like • Show 0 Likes0
Comment • 5

Is it possible to have a User(s) in the User's table without an EMPLID (User ID)?? Asking for a friend...

Background: @We have to switch over to a new HR identity source with a new set of EMPLID numbers...but the new system of record is storing the old EMPLID as LEGACYID. We'd like (to keep from having duplicate user entries) to continue using the LEGACYID as the linkage to heartbeats...but new employees will not have/receive a new LEGACYID, therefore there would be no value for the UserID.

Is this possible? Or are we just going to have to live with the duplicated (and deleted and terminated from the old list) Users?

In my humble opinion, it's absolutely silly to have to live with "deleted" users in your users table, if the "deleted" is invalid.

Ahmed Nofal

Jul 17, 2020 7:32 PM

Correct Answer

good question

is this an SQL collector where you have control over the data coming in using a query? if so, I'm just thinking out loud here and seeing if the following might be of use for you or is applicable.

How about using something like the NVL oracle function (I'm sure you there is a non-Oracle SQL equivalent if you are collecting from another database rather than oracle) :

NVL

"NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1."

SELECT ........., NVL(LEGACYID,EMPLID) as USER_ID,........ FROM HR_SYSTEM_TABLE/VIEW

& map the USER_ID alias with the IG&L USER ID attribute within the collector's config.

The flow of USER ID collection would be:

1. check user to see if he/she has a legacy id (NOT NULL).

2. if user has a legacy id (NOT NULL), set IG&L's user id attribute to legacy id value.

3. if user doesn't have a legacy id (legacy id column IS NULL), collect the IG&L's user id attribute value as the EMPLID value from the HR source.

I believe this mainly revolves on how you manage that legacy id attribute within the HR system where it will be crucial for legacy users to have the legacy id value populated and new users to NOT have it populated (should be null) in order for this to work.

See the reply in context

No one else had this question

Outcomes

Ahmed Nofal

Jul 17, 2020 7:32 PM

good question

is this an SQL collector where you have control over the data coming in using a query? if so, I'm just thinking out loud here and seeing if the following might be of use for you or is applicable.

How about using something like the NVL oracle function (I'm sure you there is a non-Oracle SQL equivalent if you are collecting from another database rather than oracle) :

NVL

"NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1."

SELECT ........., NVL(LEGACYID,EMPLID) as USER_ID,........ FROM HR_SYSTEM_TABLE/VIEW

& map the USER_ID alias with the IG&L USER ID attribute within the collector's config.

The flow of USER ID collection would be:

1. check user to see if he/she has a legacy id (NOT NULL).

2. if user has a legacy id (NOT NULL), set IG&L's user id attribute to legacy id value.

3. if user doesn't have a legacy id (legacy id column IS NULL), collect the IG&L's user id attribute value as the EMPLID value from the HR source.

2 people found this helpful

Actions

Craig Ramsay

@ Ahmed Nofal on Jul 20, 2020 4:33 AM

I can confirm the above does work and I have implemented this elsewhere with a customer following a merger of two organisations onto a single new Identity source.

In this scenario a flat file was used but the same logic applied and a fairly simple CASE statement was used in the Identity collector to use the value held under LegacyID if not null or use the new unique identifier provided by the new HR system as the User ID in IGL.

If there will be future processes that only use the new unique identifier from HR then you may need to consider extending your User schema to include an additional attribute to hold it for all users but still maintaining legacy IDs as the User ID for those users in IGL.

2 people found this helpful

Actions

Justin Gregory @ Craig Ramsay on Jul 22, 2020 4:02 PM

Thank you Ahmed and Craig.

I checked with one of our developers and she stated that she did not know how this could be done in a "single" step query, nor does she know how to do this using a CASE statement...she only knows of a two step process where there is an UPDATE to the CSV file first, then proceed with the SELECT statement:

update CS_Data.csv set LEGACYID = EMPLID where LEGACYID is null

But, I'm not certain if IG&L would support a two-step procedure...but I think I don't want to go that way even if IG&L does support it. If what Brian says is true...in that a simple CASE statement will do the trick this is the way I would like to go. I will admit I am no query expert, a novice at best...therefore don't know much about the language, but what Brian said makes sense to me...so I need a little more of your help.

My attributes are EMPLID and LEGACYID.

What would the CASE statement syntax be and where would it reside in the statement? I ask the second part because I'm sure you're aware, there are potentially dozens of attributes to be collected from an HR feed. Should the CASE statement be at the beginning or the end?

I believe this would be the last piece before we are ready to migrate PROD. Your assistance here would be GREATLY appreciated here.

Actions

Ahmed Nofal

Jul 22, 2020 7:59 PM

Hey Justin,

So you are collecting from a csv file, thus you are using the HXTT JDBC csv driver embedded within IG&L. The HXTT csv driver has a slight difference in SQL syntax than oracle (the syntax of both my example and Craig's). For the NVL option, the syntax in hxtt is also NVL. For the case option, the syntax here is IF.

IF(lExpression, eExpression1, eExpression2)

IIF(lExpression, eExpression1, eExpression2)

lExpression specifies the logical expression that IF()/IIF( ) evaluates.

Returns one of two values depending on the value of a logical expression.

If lExpression evaluates to true , eExpression1 is returned. If lExpression evaluates to false, eExpression2 is returned.

NVL(expression, value)

IFNULL(expression, value)

any numeric, string, date/time, or boolean type

Returns one of two values depending on whether expression is null.

If expression evaluates to null , value is returned. Otherwise, expression is returned.

Reference:

function.html - HXTT Text (CSV) JDBC Drivers for raw data, flat text, CSV file, TSV file, PSV file, fixed-length, and va…

I'll roughly put examples for both options, but you need to test those first on a DEV/TEST environment before doing any change on your PROD environment.

NVL:

SELECT ........., NVL(LEGACYID,EMPLID) as USER_ID,........ FROM HR_SYSTEM_TABLE/VIEW

Case (or in this case IF):

SELECT ........., IF(LEGACYID IS NOT NULL,LEGACYID,EMPLID) as USER_ID,........ FROM HR_SYSTEM_TABLE/VIEW

Both options depend on your csv file data where legacy employees will actually have a legacy id value and non legacy employees won't have a legacy id value within the column. We don't need to manipulate the data within the CSV file using any of the 2 approaches discussed here.

1 person found this helpful

Actions

Justin Gregory @ Ahmed Nofal on Jul 29, 2020 2:31 PM

Fellas, I would like to thank you so much. This solution worked like a champ! No duplicates and no account-to-user linkages were broken. We're working on updating the accounts with the new EMPLID numbers to the 100+ domains we host and are VERY appreciative you've helped us move forward.

Thank you again!

Actions

Possible to have Users where 'User ID' is null/blank?

Outcomes

Related Content

Recommended Content