Recursive query

[Former Member]

Hi,

I try to do this query in hana studio:

WITH MyTable

("U_IFC_DOCXML", email)

as

(

    select

        right("U_IFC_DOCXML", length("U_IFC_DOCXML") - LOCATE("U_IFC_DOCXML", '</BatchNumber>')+1),

        substring("U_IFC_DOCXML", LOCATE("U_IFC_DOCXML", '<BatchNumber>')+13, LOCATE("U_IFC_DOCXML", '</BatchNumber>') - LOCATE("U_IFC_DOCXML", '<BatchNumber>') - 13)

    from "@INTERFACE_IDL_DDX"

    where LOCATE("U_IFC_DOCXML", '<BatchNumber>') > 0 AND "U_IFC_NUM_COM"=15

    union all

    select

        right("U_IFC_DOCXML", length("U_IFC_DOCXML") - LOCATE("U_IFC_DOCXML", '</BatchNumber>')+1),

        substring("U_IFC_DOCXML", LOCATE("U_IFC_DOCXML", '<BatchNumber>')+13, LOCATE("U_IFC_DOCXML", '</BatchNumber>') - LOCATE("U_IFC_DOCXML", '<BatchNumber>') - 13)

    from MyTable

    where LOCATE("U_IFC_DOCXML", '<BatchNumber>') > 0 AND "U_IFC_NUM_COM"=15

)

select *

from MyTable

but it don't work, i have this message :

Could not execute 'WITH MyTable ("U_IFC_DOCXML", email) as ( select right("U_IFC_DOCXML", length("U_IFC_DOCXML") - ...'

SAP DBTech JDBC: [259]: invalid table name:  Could not find table/view MYTABLE in schema SBO_CAMEANE_DEV: line 14 col 10 (at pos 744)

Version of SAP HANA : v 112.03

How can i resolve this ?

Thanks for your help ?

Best regards,

Michael

[former_member245927]

Hi,

As lars said Hana does not support recursive WITH clauses.

May be to help you on this can you provide me your requirement more clearly(If possible with sample data.

And also in your query you have mentioned the same condition in both recursive and static places as   where LOCATE("U_IFC_DOCXML", '<BatchNumber>') > 0 AND "U_IFC_NUM_COM"=15.

I am not sure why you have used like that.

Thanks

Kala

[lbreddemann]

HANA doesn't support recursive WITH clauses.

Therefore you cannot reference the subquery MYTABLE in its definition.

Personally, I would avoid recursive processing in HANA SQL until there is proper support for it in SQL (HANA 2.0 should be out later this year and hopefully have something in this corner) and rather do the processing in an XS layer app. There you can even rely on common JS libraries to handle XML/HTML.