Skip to main content
Solved

Basic sql behaviour, getting cases with changes in NETPR

  • November 3, 2021
  • 8 replies
  • 17 views
A
  • Anonymous

Hi there,

I am getting this strange behaviour and was wondering if somebody can explain this?

I want to create an activity for when NETPR in CDPOS changed to 0 but when I look for 0s the table is empty.

 

Query 1: My first entry happens to be a case that I am looking for. 

   SELECT DISTINCT FNAME,VALUE_NEW from CDPOS 

   WHERE FNAME = 'NETPR'

   ORDER BY VALUE_NEW

   LIMIT 1

 

   FNAME  VALUE_NEW

   NETPR  0.00

 

Query 2: But I get an empty table when I use these conditions.  

   SELECT DISTINCT FNAME,VALUE_NEW 

   FROM CDPOS 

   WHERE FNAME = 'NETPR' AND VALUE_NEW = '0.00'

 

   FNAME  VALUE_NEW

   -

 

Next I wanted to see if my VALUE_NEW = '0.00' might be wrong somehow. 

 

 

 

Why do I get different smallest values '0.000' and '0.00' from query 3 and query 4 respectively?

 

Query 3

 

  SELECT DISTINCT VALUE_NEW from CDPOS 

  ORDER BY VALUE_NEW

 

    VALUE_NEW

    0.000

    1.000

 

Query 4

 

  SELECT DISTINCT FNAME, VALUE_NEW 

  FROM CDPOS 

  WHERE FNAME = 'NETPR'

  ORDER BY VALUE_NEW

 

   FNAME  VALUE_NEW

   NETPR  0.00

   NETPR  0.01

   NETPR  0.01-

 

 

 

Query 5: Looking for multiple strings with 0 get's me an empty table again.

 

  SELECT DISTINCT FNAME,VALUE_NEW 

  FROM CDPOS 

  WHERE FNAME = 'NETPR' AND VALUE_NEW IN ('0','0.0','0.00','0.000')

  ORDER BY VALUE_NEW

 

 

  FNAME  VALUE_NEW

  -

 

Best answer by stephane.multo

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.

 

    8 replies

    S
    Level 0

    Hi Sasa,

     

    Here are a couple ideas:

    • It might help to try querying CDPOS with a float filter for VALUE_NEW (e.g. WHERE VALUE_NEW = 0.0) instead of a string filter. VALUE_NEW is indeed usually a string but never know.
    • There might be non-displayed empty characters before/after the 0 values, such as non-displayed spaces. You could try to SELECT '|' || VALUE_NEW || '|' to see if there are invisible spaces around the 0 values which are preventing the 0 filters from finding the data.

     

    A
    • Anonymous
    • November 5, 2021

    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

    A
    • Anonymous
    • November 5, 2021

    Sorry, Query 5: VALUE_NEW not trimmed should read Query 5: VALUE_NEW trimmed.

     

    S
    Level 0

    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.

     

    A
    • Anonymous
    • November 9, 2021

    Hi Stephane,

     

    that did it. Thank you !

    A
    • Anonymous
    • November 12, 2021

    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.

    S
    Level 0

    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,

     

    A
    • Anonymous
    • November 12, 2021

    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!

    Community Code of ConductAccessibility statement