Hi Stephane,
thank you for your suggestions. I tried a couple of things but I am none the wiser and still puzzled.
Especially Queries 4-6 make no sense to me. I must be making a logical mistake here but I can't figure it out.
I tried your suggestion with the pipes.
Query 1
SELECT DISTINCT FNAME, '|' || VALUE_NEW || '|' as VALUE_NEW
FROM CDPOS
WHERE FNAME = 'NETPR'
ORDER BY VALUE_NEW
FNAME VALUE_NEW
NETPR | 0.00|
NETPR | 0.01-|
NETPR | 0.01|
And it looks like VALUE_NEW has a leading space.
However, I get the same result when I try to remove it wit trim.
Query 2
SELECT DISTINCT FNAME, TRIM('|' || VALUE_NEW || '|') as VALUE_NEW
FROM CDPOS
WHERE FNAME = 'NETPR'
ORDER BY VALUE_NEW
FNAME VALUE_NEW
NETPR | 0.00|
NETPR | 0.01-|
NETPR | 0.01|
NETPR | 0.02-|
NETPR | 0.02|
NETPR | 0.03-|
When I use Trim without the pipes I get a similar result but the ordering is different.
(No idea how celonis sorts these strings.)
Query 3
SELECT DISTINCT FNAME, TRIM(VALUE_NEW) AS VALUE_NEW
FROM CDPOS
WHERE FNAME = 'NETPR'
ORDER BY VALUE_NEW
FNAME VALUE_NEW
NETPR 0.00
NETPR 0.01
NETPR 0.01-
NETPR 0.02
NETPR 0.02-
NETPR 0.03
Next I wanted to see how my conditions work if I only use the first row of my table.
I get an empty table when I use VALUE_NEW without trimming
Query 4: VALUE_NEW not trimmed.
SELECT * FROM
(SELECT DISTINCT FNAME, VALUE_NEW
FROM CDPOS
WHERE FNAME = 'NETPR'
ORDER BY VALUE_NEW
LIMIT 1) as TMP
WHERE VALUE_NEW = '0.00'
FNAME VALUE_NEW
--empty
It works when I use the trimmed version and a subquery
Query 5: VALUE_NEW not trimmed.
SELECT * FROM
(SELECT DISTINCT FNAME, TRIM(VALUE_NEW) AS VALUE_NEW
FROM CDPOS
WHERE FNAME = 'NETPR'
ORDER BY VALUE_NEW
LIMIT 1) as TMP
WHERE VALUE_NEW = '0.00'
FNAME VALUE_NEW
NETPR 0.00
But not without the subquery
Query 6
SELECT DISTINCT FNAME, TRIM(VALUE_NEW) AS VALUE_NEW
FROM CDPOS
WHERE VALUE_NEW = '0.00'
FNAME VALUE_NEW
--Empty Table
Sorry, Query 5: VALUE_NEW not trimmed should read Query 5: VALUE_NEW trimmed.
Hi Sasa,
The leading spaces are reassuring as they seem to explain all these query results.
It seems all your query results make sense actually, given that the VALUE_NEW have leading spaces.
In short what's to remember is it might be better to use the TRIM(VALUE_NEW) everywhere in your related queries, to avoid confusion and empty results.
So most of query 6 looks good, you should just use TRIM(VALUE_NEW) in the WHERE clause as well:
SELECT DISTINCT FNAME, TRIM(VALUE_NEW) AS VALUE_NEW
FROM CDPOS
WHERE TRIM(VALUE_NEW) = '0.00'
This could likely work.
Hi Stephane,
is there a way to use TRIM in the table configuration during the extraction? I found more columns that are full of trailing and leading spaces. If this is not possible, what would be the best practice? We are trying to avoid additional TMP tables.
Hi Sasa,
I believe it isn't possible to use TRIM within the extractions. However I'm not sure this should lead to more TMP tables.
For example, if you extract EKPO or VBAP and you have leading spaces, you would just pull the TRIM columns at the same time you are creating the DM version of those tables (P2P_EKPO or O2C_VBAP):
CREATE O2C_VBAP AS (
SELECT VBAP.*, TRIM(column)
FROM VBAP
)
As for CDPOS, one usual method would be to pull the TRIM columns into TMP_CDHDR_CDPOS which is the standard pre-join between CDHDR and CDPOS at the beginning of the Data Job. Then you can use that TMP table and its TRIM columns in any subsequent transformation.
Best,
Hi Sasa,
I believe it isn't possible to use TRIM within the extractions. However I'm not sure this should lead to more TMP tables.
For example, if you extract EKPO or VBAP and you have leading spaces, you would just pull the TRIM columns at the same time you are creating the DM version of those tables (P2P_EKPO or O2C_VBAP):
CREATE O2C_VBAP AS (
SELECT VBAP.*, TRIM(column)
FROM VBAP
)
As for CDPOS, one usual method would be to pull the TRIM columns into TMP_CDHDR_CDPOS which is the standard pre-join between CDHDR and CDPOS at the beginning of the Data Job. Then you can use that TMP table and its TRIM columns in any subsequent transformation.
Best,
Yes, you are right we could do it at TMP_CDHDR_CDPOS. I just went trough code where my predecessor used CDPOS instead of TMP_CDHDR_CDPOS. I need to investigate this further but thank you for your help!
