Problem with native SQL cursor in generic data source

[Former Member]

Hi, All!

I am implementing generic data source based on FM.

Because of complicated SQL I canu2019t use Open SQL and RSAX_BIW_GET_DATA_SIMPLE-example u201Cas isu201D.

So, I have to use Native SQL. But Iu2019ve got a problem with a cursor. When I test my data source in RSA3, everything is Ok. But, if I start appropriate info-package, I get error u201CABAP/4 processor: DBIF_DSQL2_INVALID_CURSORu201D. It happens after selecting of 1st data package in line u201CFETCH NEXT S1 INTOu2026u201D. It seems to me that when system performs the second call of my FM the opened cursor has already been disappeared.

Did anyone do things like this and what is incorrect?

Is it real to make generic data source based on FM with using Native SQL open, fetch, closeu2026

[jasonmuzzy · Accepted solution]

Hi Denis,

Make sure you use OPEN CURSOR WITH HOLD or else your cursor will be lost between data packages.

Also, OPEN CURSOR, FETCH and CLOSE CURSOR are all open SQL, not native SQL. Native SQL can be identified because it comes between EXEC SQL and ENDEXEC statements.

Hope that helps.

[Former Member · Accepted solution]

Hi Jason,

Thanks for answer!

I was confused by the fact that it (native sql cursor) worked in RSA3. But it doesnu2019t works in u201Creal lifeu201D, after the first package my cursor goes away and thatu2019s all.

Unfortunately, the implementation of my query with using Open-SQL can be done only with sub-queries and this way is too slow.

So, what Iu2019ve done:

In initial call of my FM I do my frightful SQL and save results in temporary db-table with using native-SQL (select u2026 insertu2026).

Then I open u201Ccursor with holdu201D (Open-sql) and work with simple temporary table Iu2019ve done at previous step.

It works.

Thanks once again for answer.

[jasonmuzzy · Accepted solution]

Hi Denis,

I'd be interested to see your native SQL code if you don't mind sharing it here. It sounds like you must be a SQL expert so I'm curious to see what complicated thing you were able to do in native SQL that didn't work so well in open SQL.

Thanks!!

Jason

[Former Member · Accepted solution]

Hi Jason,

I don't think this SQL is very valuable It is just an aggregation with some custom rules. This aggregation is performing on info-provider which consists of two info-cubes. Here we have about 2 billion records in info-provider and about 30 million records in custom db-table Z_TMP (certainly, it has indexes). I have to do this operation on 21 info-providers like this and I have to do this 20 times for each info-provider (with different values of host-variable p_GROUP)

SELECT T.T1, SUM( T.T2 ), SUM( T.T3 ), SUM( T.T4 )

FROM (

SELECT F."KEY_EVENT06088" AS T1,

F."/BIC/EV_COST" + F."/BIC/EV_A_COST" AS T2,

DECODE( D.SID_EVENTTYPE, 23147, 0,

23148, 0,

23151, 0,

23153, 0,

23157, 0,

23159, 0,

24896734, 0,

695032768, 0,

695029006, 0,

695029007, 0,

695036746, 0, F."/BIC/EV_COST") +

DECODE( D.SID_EVENTTYPE, 23147, 0,

23148, 0,

23151, 0,

23153, 0,

23157, 0,

23159, 0,

24896734, 0,

695032768, 0,

695029006, 0,

695029007, 0,

695036746, 0, F."/BIC/EV_A_COST") AS T3,

DECODE( D.SID_EVENTTYPE, 23147, F."/BIC/EV_DURAT",

23148, F."/BIC/EV_DURAT",

23151, F."/BIC/EV_DURAT",

23153, F."/BIC/EV_DURAT",

23157, F."/BIC/EV_DURAT",

23159, F."/BIC/EV_DURAT",

24896734, F."/BIC/EV_DURAT",

695032768, F."/BIC/EV_DURAT",

695029006, F."/BIC/EV_DURAT",

695029007, F."/BIC/EV_DURAT",

695036746, F."/BIC/EV_DURAT", 0) AS T4

FROM "/BIC/VEVENT0608F" F,

Z_TMP G,

"/BIC/DEVENT06085" D

WHERE F."KEY_EVENT06088" = G.ID

AND F."KEY_EVENT06085" = D.DIMID

AND G.GROUP_NO = :p_GROUP

AND ( F."/BIC/EV_COST" < 0 OR F."/BIC/EV_A_COST" < 0 )

AND D.SID_EVENTTYPE <> 695030676 AND D.SID_EVENTTYPE <> 695030678

UNION

SELECT F."KEY_EVNA06088" AS T1,

F."/BIC/EV_COST" + F."/BIC/EV_A_COST" AS T2,

DECODE( D.SID_EVENTTYPE, 23147, 0,

23148, 0,

23151, 0,

23153, 0,

23157, 0,

23159, 0,

24896734, 0,

695032768, 0,

695029006, 0,

695029007, 0,

695036746, 0, F."/BIC/EV_COST") +

DECODE( D.SID_EVENTTYPE, 23147, 0,

23148, 0,

23151, 0,

23153, 0,

23157, 0,

23159, 0,

24896734, 0,

695032768, 0,

695029006, 0,

695029007, 0,

695036746, 0, F."/BIC/EV_A_COST") AS T3,

DECODE( D.SID_EVENTTYPE, 23147, F."/BIC/EV_DURAT",

23148, F."/BIC/EV_DURAT",

23151, F."/BIC/EV_DURAT",

23153, F."/BIC/EV_DURAT",

23157, F."/BIC/EV_DURAT",

23159, F."/BIC/EV_DURAT",

24896734, F."/BIC/EV_DURAT",

695032768, F."/BIC/EV_DURAT",

695029006, F."/BIC/EV_DURAT",

695029007, F."/BIC/EV_DURAT",

695036746, F."/BIC/EV_DURAT", 0) AS T4

FROM "/BIC/VEVNA0608F" F,

Z_TMP G,

"/BIC/DEVNA06085" D

WHERE F."KEY_EVNA06088" = G.ID

AND F."KEY_EVNA06085" = D.DIMID

AND G.GROUP_NO = :p_GROUP

AND ( F."/BIC/EV_COST" < 0 OR F."/BIC/EV_A_COST" < 0 )

AND D.SID_EVENTTYPE <> 695030676 AND D.SID_EVENTTYPE <> 695030678

) T

GROUP BY T.T1

[jasonmuzzy · Accepted solution]

Thanks for posting your SQL!