SELECT-INTO returns more than one row

Database

SELECT-INTO returns more than one row

Hi,

I am far from new to database programming but totally new to Teradata and really struggling with stored procedures (amongst other things).

I have a particular problem at the moment with a stored procedure failing with the error message "SELECT-INTO returned more than one row".

I have narrowed the sp down to one sql statement that is causing the problem. This is it:


REPLACE PROCEDUREValidateAccScreen ( IN ACC_NO INTEGER , IN PERIOD_DTE Date  , OUT IS_VALID VARCHAR ( 7 ) , OUT ERROR_STRING VARCHAR ( 255 ))


BEGIN


 


DECLARE MAX_ABS_PELB DECIMAL ( 18 , 2 );


DECLARE CO_CDE_FK VARCHAR ( 20 );


 


SELECT max ( abs ( period_bal )) ,


code_fk


INTO MAX_ABS_PB ,


CODE_FK


FROM  table1


WHERE acc_no = ACC_NO


AND period_dte = PERIOD_DTE


GROUP BY co_cde_fk ;


END ;


However, when I run the below select statement in Teradata SQL Assistant I only get 1 row returned. Note the hardcoded values are the same as those passed to the sp.

SELECT max(abs(period_bal)) ,

code_fk

FROM table1

WHERE acc_no=12345

and period_dte= '2011-12-30'

GROUP BY code_fk;


 


Now my suspicion is that the problem is something to do with TD needing to bring back more than one value in order to get the max but at the end of the day I only want to get back 1 row and store that in the relevant variables.


Can somebody please tell me if you can use SELECT-INTO like this with an aggregate such as max()?


And if not, what are you meant to do instead?


Thanks in advance

Tags (3)
3 REPLIES
Senior Apprentice

Re: SELECT-INTO returns more than one row

Of course if there's a 1-to-1 relation between acc_no and code_fk you could also get rid of the GROUP BY and do a simple MAX(code_fk). But if it's not 1-to-1 your SP will fail some day.

If the query actually returns a single row, the SELECT INTO shouldn't fail.

But is this the actual source code?

You mixed code_fk and cd_cde_fk in the SP SELECT and this should not compile.

The only other thing i could think of is a case sensitivity issue, did you run the CREATE PROC and the SELECT in the same client/session?

Try

SELECT max(abs(period_bal)) ,

code_fk (CASESPECIFIC)

FROM table1

WHERE acc_no=12345

and period_dte= '2011-12-30'

GROUP BY code_fk;

Dieter

Re: SELECT-INTO returns more than one row

Dieter,

thanks for reply. I really appreciate the help!

 You are so much more observant than I :) No that's not the exact code, that was my attempt to change field names etc so that our security people would allow me to post it on a forum!!!

code_fk and cd_cde_fk are indeed the same thing!

The  (CASESPECIFIC) thing didn't work so I am wondering if I am just going about this all wrong.

What I need to get is the value in the code_fk column that corresponds to the maximun absolute value for period_bal. It would be highly unlikely but I cannot guarantee that only 1 row would have period_bal=max(abs(period_bal))

Do I need a complete rethink here??

Senior Apprentice

Re: SELECT-INTO returns more than one row

Your description sounds like this should work:

SELECT abs(period_bal) as  MAX_PB,

code_fk (CASESPECIFIC)

FROM table1

WHERE acc_no=12345

and period_dte= '2011-12-30'

qualify row_number() over (order by MAX_PB DESC) = 1:

Dieter