Post reply

Convert Oracle to Sql server - use Collection index

  • November 17, 2011 at 4:17 am

    #262806

    Hi,

    I have use this code to declare collection index

    DECLARE

    @CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( UCID DECIMAL , C_TIME DATETIME , KEY DECIMAL , KEY_SET DECIMAL , VALUE DECIMAL ) )',

    @CollectionIndexInt$TYPE$2 varchar(max) = ' TABLE OF ( RECORD ( KEY_SET DECIMAL , VALUE DECIMAL ) )'

    DECLARE

    @main_sets_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE),

    @main_item dbo.CollectionIndexInt = dbo.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE$3),

    Now I want to use this like

    FETCH @c_sysref

    INTO

    @ucid,

    @c_time,

    @key,

    @key_set,

    @value

    IF @@FETCH_STATUS = -1

    BREAK

    SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'VALUE', @value))

    SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY_SET', @key_set))

    SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'KEY', @key))

    SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDatetime(N'C_TIME', @c_time))

    SET @main_sets_item = @main_sets_item.ExtendAndSetRecord(@collection_cur_key, @main_sets_item.GetOrCreateRecord(@collection_cur_key).SetDecimal(N'UCID', @ucid))

    DECLARE

    @key_set$2 float(53)

    SELECT @key_set$2 = sysdb.ssma_oracle.db_get_next_sequence_value(N'Tific', N'DEV_LOG', N'LOG_SAC_SETS_SEQ')

    SET @main_sets_item = @main_sets_item.SetRecord(@i, @main_sets_item.GetOrCreateRecord(@i).SetDecimal(N'KEY_SET', @key_set$2))

    SET @main_item = @main_item.Extend()

    SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'UCID', @main_sets_item.GetRecord(@i).GetDecimal(N'UCID')))

    SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDatetime(N'C_TIME', @main_sets_item.GetRecord(@i).GetDatetime(N'C_TIME')))

    SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY')))

    SET @main_item = @main_item.SetRecord(@main_item.Last(), @main_item.GetOrCreateRecord(@main_item.Last()).SetDecimal(N'KEY_SET', @main_sets_item.GetRecord(@i).GetDecimal(N'KEY_SET')))

    But when i use this like

    WHILE @ii <= @main_item.COUNT

    INSERT INTO log_sac_main VALUES @main_item.GetRecord(@ii)

    This gives Error, syntax error,

    Please help me on this

  • November 24, 2011 at 3:17 am

    #1412610

    Is this the Oracle code you are trying to convert?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply