Please can someone provide me the SQL to split out "Worker"."Employee Mailing Postal Code" AS Incode

MLowe3639

Hi,

i am tyring to use a calculated measure to split the "Worker"."Employee Mailing Postal Code" (Workforce Management - Person Real Time Folder) to show a column with just the "incode".

i am using the following SQL:

CASE
WHEN INSTR("Worker"."Employee Mailing Postal Code", ' ') > 0 THEN
SUBSTR("Worker"."Employee Mailing Postal Code", INSTR("Worker"."Employee Mailing Postal Code", ' ') + 1)
ELSE
NULL
END

i am new to using OTBI so any help would be appreciated?

Faisal.Iqbl

Here's a simple example SQL query that demonstrates how to split the "Employee Mailing Postal Code" into parts. Assuming the postal code is in a standard format and you want to get the first part (the "Incode"), you can use the following approach:

SELECT 

    "Worker"."Employee Mailing Postal Code",

    SUBSTR("Worker"."Employee Mailing Postal Code", 1, INSTR("Worker"."Employee Mailing Postal Code", ' ') - 1) AS Incode

FROM 

    "Worker"

WHERE 

    "Worker"."Employee Mailing Postal Code" IS NOT NULL;

Explanation:

• SUBSTR: This function extracts a substring from a string.
• INSTR: This function finds the position of a substring (in this case, a space ' ') within the string. It gives the position where the first space occurs, which separates the Incode from the rest of the postal code.
• The - 1 is used in SUBSTR to avoid including the space in the result.

Notes:

• Make sure to adjust the space ' ' in the INSTR function if your separation logic is based on a different character or format.
• If the postal code structure varies, you may need to refine the logic accordingly.

MLowe3639

This hasn't worked for some reason

is it possible to reach the outcome by using a calculation?