Different result in ABAP Open SQL en HANA SQL

[Former Member]

Hi SAP,

we are upgrading from a Secondary HANA to a Primary and notice a different behaviour in JOIN in SQL.

When we use this SQL we get different results in both situations (I only had to change the "." into "~" to make it syntactically correct:

select

ESTRH.RECNROOT , ESTRH.SUBID , ESTVA.RECNTVH , ESTVA.ORD , ESTVH.ESTCAT , TCG12.ESTNAM , REL.RECNROOT_DST , REL.RECNROOT_SRC

from estrh    

left outer join tcgtplrel as rel

on ( rel.recnroot_dst = estrh.recnroot   and rel.delflg = ' ' )  

join estvh as estvh on ( estvh.recnroot = estrh.recnroot   or estvh.recnroot = rel.recnroot_src )  and estvh.delflg = ''   

join estva as estva on estva.recntvh = estvh.recn   and estva.delflg = ''   

join tcg12 as tcg12 on tcg12.estcat = estvh.estcat   and langu = 'N'    

where estrh.subid = 'KUL_00001492'

and estvh.estcat = 'SAP_EHS_1019_003'

and estrh.delflg = ''

order by TCG12.ESTNAM

Formerly on the secondary database (native SQL) and now using Open SQL in ABAP we get 4 records as a result.

When submitting it as native SQL in HANA as primary we get 8 records as a result. This is due to doubles in the 'red' table ESTVA. These doubles also exist in the secondary database that still resides on our production system.

It seems as if in the first case, some GROUPING is done. The JOINs are exactly the same in both cases.

Is SQL behaving differently in different situations?

Anyone any idea?

Regards,

Kris

[Former Member]

Hi SAP,

we are upgrading from a Secondary HANA to a Primary and notice a different behaviour in JOIN in SQL.

When we use this SQL we get different results in both situations (I only had to change the "." into "~" to make it syntactically correct:

select

ESTRH.RECNROOT , ESTRH.SUBID , ESTVA.RECNTVH , ESTVA.ORD , ESTVH.ESTCAT , TCG12.ESTNAM , REL.RECNROOT_DST , REL.RECNROOT_SRC

from estrh    

left outer join tcgtplrel as rel

on ( rel.recnroot_dst = estrh.recnroot   and rel.delflg = ' ' )  

join estvh as estvh on ( estvh.recnroot = estrh.recnroot   or estvh.recnroot = rel.recnroot_src )  and estvh.delflg = ''   

join estva as estva on estva.recntvh = estvh.recn   and estva.delflg = ''   

join tcg12 as tcg12 on tcg12.estcat = estvh.estcat   and langu = 'N'    

where estrh.subid = 'KUL_00001492'

and estvh.estcat = 'SAP_EHS_1019_003'

and estrh.delflg = ''

order by TCG12.ESTNAM

Formerly on the secondary database (native SQL) and now using Open SQL in ABAP we get 4 records as a result.

When submitting it as native SQL in HANA as primary we get 8 records as a result. This is due to doubles in the 'red' table ESTVA. These doubles also exist in the secondary database that still resides on our production system.

It seems as if in the first case, some GROUPING is done. The JOINs are exactly the same in both cases.

Is SQL behaving differently in different situations?

Anyone any idea?

Regards,

Kris

[pfefferf]

Hello Kris,

I think that the reason is the implicit client handling you have with Open SQL now. Can you check if the missing records belong to a different client?

Regards,

Florian

[Former Member]

That is certainly not the reason, since we have only one client. And I see all lines in transactions SE16H, so they don't come from a different client.

We had that problem previously in our test system.

Thanks anyway.

Kris

[Former Member]

I must admit I was wrong here. But I did not realize there were data in client '000'. We only have access to client '300' via the GUI, but the HANA Studio taught me where the problem actually lies.

Apparently, using a secondary database connection uses implicitly a specific client, as open SQL does.

Thanks for helping and sorry for doubting you.

Regards.

Kris

[lbreddemann]

The delflg (deletion flag?) looks suspicious.

The NULL handling for empty strings ('') is different in pure HANA and in NetWeaver if my memory doesn't fail me.

So that would be what I would be looking for here.

[Former Member]

We tried that also. That's not it.

It is a DELETION_FLAG, that is correct and we get the 'correct' result in both cases.

Thanks for the reaction.

Kris

[lbreddemann]

In this case, you really should have a look at the actual SQL statement Netweaver issues (ST05 is your friend here).

If that doesn't provide you with enough insight into what's going on it might be worthwhile to involve support.