Declare and Set then Open and Fetch

Database

Declare and Set then Open and Fetch

If I understand correctly, I am trying to convert a Declare, Set then Open and Fetch. I need to do this in a SP? If so does anyone have an example of using all of these in one SP?

I need to create the table

Declare the values

Set the values

Declare the cursor as Select * from table

Open that table/query and fetch from it to put those values into another volatile table.

Then insert values into another volatile table.

Close

then deallocate.

My first time trying to figure the whole cursor and fetch.

4 REPLIES

Re: Declare and Set then Open and Fetch

Do you know if there is anyone out there that can teach me how to convert this from SQL server to Teradata?





drop  table #claim3DaysConcurrent

create table #claim3DaysConcurrent

        (memberid                    varchar(15),

        gpi10                        varchar(60),

        gpi10Dos                    datetime,

        gpi10DosDaysSupply            datetime,

        overLapDays                varchar(10),

        AutoType                    varchar(15));



declare @overLapDays                int;

declare @gpi10Dos                    datetime;

declare @gpi10DosHold                datetime;

declare @gpi10DosDaysSupply            datetime;

declare @gpi10DosDaysSupplyHold        datetime;

declare @memberID                    varchar(15);

declare @memberIDHold                varchar(15);

declare @AutoType                    varchar(15);

declare @AutoTypeHold                varchar(15);

declare @gpi10                        varchar(60);

declare @gpi10Hold                    varchar(60);



set @gpi10Dos                        = '1900-01-01 00:00:00.000';

set @gpi10DosHold                    = '1900-01-01 00:00:00.000';

set @gpi10DosDaysSupply                = '1900-01-01 00:00:00.000';

set @gpi10DosDaysSupplyHold            = '1900-01-01 00:00:00.000';

set @memberID                        = ' ';

set @memberIDHold                    = ' ';

set @AutoType                        = ' ';

set @AutoTypeHold                    = ' ';

set @gpi10                            = ' ';

set @gpi10Hold                        = ' ';



declare filterAutosOut cursor for

select  memberId,

        gpi10,

        gpi10Dos,

        gpi10DosDaysSupply,

        AutoType

  from #claimOverLap

  order by memberId,

          gpi10Dos,

          gpi10DosDaysSupply,

          AutoType;



OPEN filterAutosOut;

fetch from filterAutosOut into  @memberId,

                                @gpi10,

                                @gpi10Dos,

                                @gpi10DosDaysSupply,

                                @AutoType;



        set @memberIDHold                    = @memberID;

        set @gpi10Hold                        = @gpi10;

        set @gpi10DosHold                    = @gpi10Dos;

        set @gpi10DosDaysSupplyHold            = @gpi10DosDaysSupply;

        set @AutoTypeHold                    = @AutoType



        WHILE @@FETCH_STATUS = 0     

        begin 

                if @memberIdHold      = @memberId    and

                  @AutoTypeHold    <> @AutoType       

                  begin

                        insert  into #claim3DaysConcurrent

                                        (memberid,

                                        gpi10,

                                        gpi10Dos,

                                        gpi10DosDaysSupply,

                                        overLapDays,

                                        AutoType)

                                values  (@memberID,

                                          @gpi10,

                                        @gpi10Dos,

                                        @gpi10DosDaysSupply,

                                        case when @gpi10DosDaysSupplyHold > @gpi10DosDaysSupply then

                                            dateDiff(dd, @gpi10Dos,@gpi10DosDaysSupplyHold) -

                                            dateDiff(dd, @gpi10DosDaysSupply,@gpi10DosDaysSupplyHold)

                                        else

                                            dateDiff(dd, @gpi10Dos,@gpi10DosDaysSupplyHold)

                                        end,

                                        @AutoType);

                  end

                else

                  begin

                        insert  into #claim3DaysConcurrent

                                        (memberid,

                                        gpi10,

                                        gpi10Dos,

                                        gpi10DosDaysSupply,

                                        overLapDays,

                                        AutoType)

                                values  (@memberID,

                                          @gpi10,

                                        @gpi10Dos,

                                        @gpi10DosDaysSupply,

                                        '0',

                                        @AutoType);

                  end



                  set @memberIDHold                = @memberID;

                  set @gpi10Hold                    = @gpi10;

                  set @gpi10DosHold                = @gpi10Dos;

                  set @gpi10DosDaysSupplyHold        = @gpi10DosDaysSupply;

                  set @AutoTypeHold                = @AutoType;



                fetch from filterAutosOut into @memberId,

                                              @gpi10,

                                              @gpi10Dos,

                                              @gpi10DosDaysSupply,

                                              @AutoType;



        end



CLOSE filterAutosOut

DEALLOCATE filterAutosOut;

Junior Contributor

Re: Declare and Set then Open and Fetch

The logic can be easily be ported to a Teradata Stored Procedure.

Most of the syntax is the same, e.g. declare cursor/while/fetch/set, but some has to be adjusted, e.g.

- TD doesn't allow a variable name starting with @

- for each IF/WHILE there must be an END IF/END WHILE

- each statement must be terminated by a semicolon

- first there's a DECLARE section before any other statement

- there's no DATEDIFF, your timestamps are probably dates, so it's CAST(timestamp1 AS DATE) - (timestamp2 AS DATE)

But before you start porting this cursor to TD you should check what it's actually doing as cursors are always processed serial (fetch next) which is worst case in a parallel database like TD.

You seem to simply compare the current row with the previous row and you don't need a cursor for that if you got Windowed Aggregate Functions (SQL Servers support for that was quite basic before SS2012).

When you add some more details about the source data and the result it should be possible to rewrite that with a single select statement.

Re: Declare and Set then Open and Fetch

Dieter. I'm a newbie so just trying to make sure i dont miss anything. Nothing worse than a newbie talking about something they dont know, then insert foot in mouth. Trying to figure out what that select is suppose to be. Trying to get my head around it. Basically i'm doing one big data pull, then pulling it through several temp tables weeding out what I dont need and adding back in some demographics and some more criteria. If you have a email addy, i'll send ya the file so you can see what I am doing? But I think I need one select to pull the Current row previous row output. The select from that would evaluate the dates? What i'm trying to do:

Find claims that have that are in the same date ranges. We need a distinct auto from each class with a sum of 3 days of concurrent usage.

I think I should use the Max function, i need to figure out how to evaluate the current and previous rows.

Junior Contributor

Re: Declare and Set then Open and Fetch

Standard SQL's LAG and LEAD functions are not implemented in TD, but it easy to rewrite.

E.g. get the previous row's value 

min(gpi10)
over (partition by memberId
order by gpi10Dos,
gpi10DosDaysSupply,
AutoType
rows between 1 preceding and 1 preceding
)