Help with in-db and sql steatment.

Question

Hi everyone, I am Eduardo back working with Alteryx and I hope forever 🙂

I am with a question I tryied to solve myself for hours but unsucess I tryied everything I could....

I am using a in-db tool to read a database based on an hadoop ecosystem and this part so far so good but please see the photo 1 you will that this first part is ok.

My question is that there is a field named as "a.anomesdia" where ano is year,  mes is month and dia means days with content "20210812" and would need to fetch only year and month what means: "202108" I tried substring, select year (db.table.field) with semicolon without semicolon putting full file name etc. I am not able to get this data, please see the photo 2.

Please, Can someone help me?

Thanks a lot.

Alteryx photo 1.png

Alteryx photo 2.png

danilang · Accepted Answer

hi @Edu_XGomes

According to the docs here you should be able to use

SUBSTR(string, start_pos [, substring_len])

to extract the string parts.  Note that unlike Alteryx where the first character of the string is position 0, HPL/SQL starts at position 1.

Dan