topic Re: Different result in ABAP Open SQL en HANA SQL in Application Development and Automation Discussions

Different result in ABAP Open SQL en HANA SQL

Former Member — Wed, 16 Mar 2016 11:07:46 GMT

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

Re: Different result in ABAP Open SQL en HANA SQL

pfefferf — Wed, 16 Mar 2016 11:16:58 GMT

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

Re: Different result in ABAP Open SQL en HANA SQL

Former Member — Wed, 16 Mar 2016 11:26:33 GMT

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

Re: Different result in ABAP Open SQL en HANA SQL

lbreddemann — Wed, 16 Mar 2016 12:50:53 GMT

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.

Re: Different result in ABAP Open SQL en HANA SQL

Former Member — Wed, 16 Mar 2016 13:17:53 GMT

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

Re: Different result in ABAP Open SQL en HANA SQL

lbreddemann — Thu, 17 Mar 2016 06:54:24 GMT

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.

Re: Different result in ABAP Open SQL en HANA SQL

Former Member — Tue, 22 Mar 2016 08:02:30 GMT

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