Archives of the TeradataForum

Message Posted: Thu, 11 Jun 2015 @ 13:48:45 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi .... I really need your help on this one ....

I'm intending to return 10 results sets to an invoking application (which will probably be an external JDBC Java call to a Teradata Stored Procedure).

Thing is, in the process of testing a simplistic version of it, I noticed that Result set 2 only gets data populated in its Result Set if and ONLY if Result set 1 gets data in its result set !

So, what I'm finding is that in the case where Cursor1 legitimately finds no rows - a not unreasonable situation - the return of that Cursor Set seems to effectively Null out the rows that could and should have been in Result Set 2 !

Hard to believe, I know, but here's my simple code (stripped back to essentially that which is in the manual) :

     REPLACE PROCEDURE Sample_p (IN nP_CustomerId DECIMAL(10), OUT sP_OutCodePoint CHAR(40),
     OUT nP_OutCustomerId DECIMAL(10)) DYNAMIC RESULT SETS 2 BEGIN DECLARE sG_CodePoint
     CHAR(4) DEFAULT 'A0'; DECLARE sG_CustomerId DECIMAL(10);
     --
     DECLARE cur1 CURSOR WITH RETURN ONLY FOR ? ? SELECT * FROM
     TESTALLVIEWS_V.CustomerTradeSummary_FV01 WHERE CUS_ID = :sG_CustomerId; DECLARE cur2
     CURSOR WITH RETURN ONLY FOR ? ? SELECT * FROM TESTALLVIEWS_V.CustomerTradeChannel_FV01
     WHERE CUS_ID = :sG_CustomerId;
     --
     SET sG_CustomerId = nP_CustomerId;
     --
     SET sG_CodePoint = 'A1';
     OPEN cur1;
     SET sG_CodePoint = 'A2';
     OPEN cur2;
     SET sG_CodePoint = 'A3';
     --
     SET sP_OutCodePoint = sG_CodePoint;
     SET nP_OutCustomerId = sG_CustomerId;
     END;

Here's my tests ...

     -- Test 1 : both Tables have data for input ------------------------------ SELECT
     count(*) FROM TESTALLVIEWS_V.CustomerTradeSummary_FV01 WHERE CUS_ID = 6002901825; --102
     SELECT count(*) FROM TESTALLVIEWS_V.CustomerTradeChannel_FV01 WHERE CUS_ID = 6002901825;
     --1 CALL sample_p (6002901825, OutcodePoint,OutCustomerId); -- correct : answer set 1 & 2
     gives data

     Test1 passes.


     -- Test 2 : Only Table one has data have data for input
     ---------------------------------------
     SELECT count(*) FROM TESTALLVIEWS_V.CustomerTradeSummary_FV01 WHERE CUS_ID = 5003908044;
     --76 SELECT count(*) FROM TESTALLVIEWS_V.CustomerTradeChannel_FV01 WHERE CUS_ID =
     5003908044; --0 CALL sample_p (5003908044, OutcodePoint,OutCustomerId); -- correct :
     answer set 1 givess data, answer set 2 gives no data

     Test2 passes.


     -- Test 3 : Only Table two has data have data for input
     ---------------------------------------
     SELECT count(*) FROM TESTALLVIEWS_V.CustomerTradeSummary_FV01 WHERE CUS_ID = 6201450439;
     --0 SELECT count(*) FROM TESTALLVIEWS_V.CustomerTradeChannel_FV01 WHERE CUS_ID =
     6201450439; --1 CALL sample_p (6201450439, OutcodePoint,OutCustomerId); -- INCORRECT !!!
     : answer set 1 gives no data (as I'd expect), BUT answer set 2 ALSO gives NO data !

Test3 fails !

-- Note : all three calls correctly set the CodePoint to position 'A3', indicating that it did go through the code and, in case you're wondering (as I did) it hasn't lost the value of CustomerId, as it gets set in the OUT parm correctly.

Interesting, if I reverse the opening of the cursors (i.e. open cursor2 before cursor1 then, sure enough, I get the data out for Test 3 ...

obviously, not a solution, but proves that there is data there.)

I'm running this from SqlAssistant, by the way, in Teradata 14.10 (odbc 14.0)

I've tried using different values of RETURN TO CALLER, etc, but I may have missed the significant combination.

Is this a bug, do you think or am I just missing something simple ?

If anyone can point me to the error of my ways I'd be eternally grateful (well, for quite some time)

Regards

David Clough
Senior BI Database Designer
BI Competency Centre

	<Prev	Next>		<<First	<Prev	Next>	Last>>