Using Length and Modulo within a Declare Cursor For statement

Database
N/A

Using Length and Modulo within a Declare Cursor For statement

I'm trying to find how many provider ID's (lenth of 9) are contained in the MajoritySourceofCareProvider field. I get an Invalid SQL statement error on the line:

(LENGTH(MajoritySourceofCareProvider) Modulo 9) + 1 AS Iterator

within the Declare ProviderCursor Cursor For statement.  The Select statement works fine independent of the Declare statement.  Don't understand why its not working within the Declare statement.  Can anyone explain?

Thanks!

CREATE PROCEDURE database.ParseACGMajorCareProvider ()

BEGIN

DECLARE LoopCount BYTEINT;

DECLARE tmpIterator BYTEINT;

DECLARE tmpMajorCareProvider CHAR(9);

DECLARE tmpMajoritySourceofCarePercent DECIMAL(8,2);

DECLARE tmpMajoritySourceCareProvider VARCHAR(200);

DECLARE tmpRecipientID CHAR(8);

DECLARE tmpStart BYTEINT;

DECLARE ProviderCursor CURSOR FOR

SELECT

RecipientID,

MajoritySourceofCareProvider,

MajoritySourceofCarePercent,

(LENGTH(MajoritySourceofCareProvider) Modulo 9) + 1 AS Iterator

FROM

MMDSDStg.ACGRecipientMajorCareProvider;

OPEN ProviderCursor;

FETCH ProviderCursor INTO

tmpRecipientID,

tmpMajoritySourceCareProvider,

tmpMajoritySourceofCarePercent,

tmpIterator ;

WHILE (SQLCODE = 0)

SET LoopCount = 0;

SET tmpStart = 1;

1 REPLY
Junior Contributor

Re: Using Length and Modulo within a Declare Cursor For statement

There's no LENGTH function in Teradata, it's ODBC syntax.

The ODBC driver might translate LENGTH to the correct CHAR_LENGTH in some cases, but not within an SP.

Dieter