SAP Signavio Extraction Filter for M2S JCDS

[niels1515]

Hello experts,

I would like to make my extraction of the JCDS table a little smaller because I only use the numbers of the field OBJNR with the beginning OR* in the transformation. I would also like to use a filters in the extraction. But I have problems with the current filter, because no rows are returned.

my attempt was: OBJNR = 'OR*'

Does anyone have an idea how I can solve this?
Greetings Niels

[EvanR · Accepted solution]

Hi Neil, 

Continuing our thread here. I am including screenshots of the suggested code snippet [SUBSTRING(OBJNR,1,2) = 'OR'] working in both extractions and transformations. 

Please let me know if you have any questions.

Thanks,
Evan

[niels1515 · Accepted solution]

Hi Evan, For some reason the filter doesn't work when I use it in a basic extraction. If I use the filter in Advanced Extraction it works. Thanks 🙂 Greetings Niels

[EvanR]

Hi Niels,

Please try: SUBSTRING(OBJNR,1,2) = 'OR'

Documentation on this function can be found here: String Functions and Operators — Presto 0.284 Documentation (prestodb.io)

Hope this helps,

Evan

[niels1515]

Hi Evan, Unfortunately, the suggestion does not work either. I have also tried the function again in the transformations, but I get the feedback that it does not recognise the function. Greetings Niels

[EvanR]

Do you mind sharing screenshots of the error when using the code snippet [SUBSTRING(OBJNR,1,2) = 'OR']? I confirmed that it works in both extractions and transformations. You may also want to try SUBSTR(OBJNR,1,2) = 'OR'

[niels1515]

Hi @EvanR, I can confirm that there are values that start with OR (see screenshot). Here it is also clear again that the suggested code works, because I have just loaded the complete JCDS table. 

Regards  Niels 

[niels1515]

Hi @EvanR , I've attached a few screenshots with the error messages I get...

Regards Niels 

[EvanR]

Niels, in the second screenshot, JCDS 16.05.2024 ERROR in Preview, the error is that the extraction returned no data. This isn't a true error because the extraction ran successfully; however, the filter is too restrictive, and no rows were returned. Can you confirm the column, objnr, has records that start with the characters 'OR' with screenshots please?

[niels1515]

Hi @RobertT , I have just tried your suggested solution. Unfortunately, I still get an error message. See the attached screenshot.

Greetings Niels 

[RobertT]

Hello Niels

Your syntax of OBJNR = 'OR*' would be checking if there was an OBJNR literally equal to OR*. This is likely why 0 results are returned.

Instead, try looking to filter for OBJNR beginning with OR. The correct syntax would be OBJNR LIKE ‘OR%’.

[niels1515]

I have tried Dennis suggestion, but unfortunately I get an error message. Greetings Niels

[D_Streichert]

Hi Niels,

can you try OBJNR LIKE 'OR*'  ?

Cheers,

Dennis

[niels1515]

Hi Dennis, I have tried your solution, unfortunately I get a feedback of an incorrect SQl statment. I can attach screenshots again in a new answer. Greetings Niels

[D_Streichert]

Hi Niels, maybe it's a topic for a ticket, I would not know any better now 🙂