All Forums Database
comptrtoy1 3 posts Joined 03/14
18 Jul 2014
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.

comptrtoy1 3 posts Joined 03/14
27 Jul 2014

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;

dnoeth 4628 posts Joined 11/04
27 Jul 2014

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.

Dieter

comptrtoy1 3 posts Joined 03/14
28 Jul 2014

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.

dnoeth 4628 posts Joined 11/04
29 Jul 2014

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
     )

 

Dieter

You must sign in to leave a comment.