Simple example of Sybase ASE 12.0 on a crosstalk
Thousands of dreams in October 2002
/ * CREATE TABLE ... * /
Create Table #dest_Table (
Flag char (1),
A int NULL,
B Int Null,
C int NULL,
D int NULL,
E int NULL
)
Go
/ * Flag can be one or more fields; but Flag must build unique index * /
CREATE UNIQUE INDEX IDX_PRK ON #DEST_TABLE (FLAG)
Go
Create Table #MID_TABLE (
Flag char (1),
Item char (1),
Num Int
)
Go
/ * Flag can be one or more fields; but Flag and Item can establish unique index * /
CREATE UNIQUE INDEX IDX_PRK ON #MID_TABLE (FLAG, ITEM)
Go
/ * Init Data ... * /
INSERT #MID_TABLE VALUES ('A', 'A', 100)
INSERT #MID_TABLE VALUES ('A', 'B', 200)
INSERT #MID_TABLE VALUES ('A', 'C', 300)
INSERT #MID_TABLE VALUES ('A', 'D', 400)
INSERT #MID_TABLE VALUES ('A', 'E', 500)
Go
INSERT #MID_TABLE VALUES ('B', 'A', 10)
INSERT #MID_TABLE VALUES ('B', 'B', 20)
INSERT #MID_TABLE VALUES ('B', 'C', 30)
INSERT #MID_TABLE VALUES ('B', 'D', 40)
INSERT #MID_TABLE VALUES ('B', 'E', 50)
Go
INSERT #MID_TABLE VALUES ('C', 'A', 9)
INSERT #MID_TABLE VALUES ('C', 'B', 8)
INSERT #MID_TABLE VALUES ('C', 'D', 6)
INSERT #MID_TABLE VALUES ('C', 'E', 5)
Go
INSERT #MID_TABLE VALUES ('X', 'A', 22)
Go
Declare Cur_name Cursor for
SELECT FLAG, ITEM, NUM from #mid_table
For Read Only
Go
/ * cursor var ... * /
Declare @flag char (1)
Declare @item char (1)
Declare @num int
/ * Other var ... * /
Declare @SQL_Text Char (255)
Declare @col_char char (1)
Open cur_name
FETCH CUR_NAME INTO @ Flag, @ @ @ @ Num
While (@@ sqlstatus = 0)
Begin
/ * Note #mid_table with the corresponding logic between #dest_table fields * // * This can be implemented in a very complex conversion * /
SELECT @ col_char = @ iTEM
IF not exists (SELECT 1
From #dest_table
WHERE flag = @ flag
)
Begin
SELECT @ SQL_Text = "Insert #dest_Table (Flag,"
@ col_char ") VALUES ('"
@ flag "',"
Convert (CHAR (10), @num) ")"
/ * Open Select @SQL_Text when debugging; Exec (@SQL_Text) mask * /
--Select @SQL_Text
EXEC (@SQL_Text)
end
Else
Begin
SELECT @ SQL_Text = "Update #dest_Table Set"
@ col_char "="
Convert (Char (10), @n)
"Where flag = '" @ flag "'"
--Select @SQL_Text
EXEC (@SQL_Text)
end
/ * Even the tour ~~~~~~ * /
FETCH CUR_NAME INTO @ Flag, @ @ @ @ Num
end
Go
Close Cur_name
Go
Deallocate Cursor Cur_Name
Go
Select * from #mid_table
Go
Select * from #dest_table
Go
