|
제가 하고자 하는 내용은
asiq에서 1개 row에 들어있는 특정값을 가지고 계속 나누는 loop문을 만들어
1개의 row가 최대 22개 row로 쪼개지도록 cursor문과 loop문을 만들어서 sam파일로 내려 받는 작업을 하려 하는데
현재 source는 cousor에 들어있는 data와 각 row data만 나오고 loop문으로 들어가 쪼개지질 않더라고요
소스는
BEGIN
declare @c_gubun1 char(3);
declare @c_gubun1_name varchar(100);
declare @n_value1 numeric(19,3);
declare @v_value1 numeric(19,3);
declare @c_text1 char(11);
declare @c_date1 char(8);
declare @c_date2 char(8);
declare @c_gubun_yn char(1);
declare @c_result varchar(150);
declare @n_sum_value numeric(19,3);
declare @n_cs_value numeric(19,3);
declare @c_yn_gubun char(1);
declare @n_tot_sum numeric(19,3);
begin
For curfor As test_cur
Cursor For
select text1
,'' gubun1
,'' gubun1_NAME
,0 value1
,dateformat(date1,'yyyymmdd') date1
,dateformat(date2,'yyyymmdd') date2
,gubun_yn
,result
,sum_value
,cs_value
,yn_gubun
,tot_sum
from A
where cs_value > 0
For read only
DO
set @v_value1 = cs_value ;
set @c_text1 = text1;
set @c_date1 = date1;
set @c_date2 = date2;
set @c_gubun_yn = gubun_yn;
set @c_result = result;
set @n_sum_value = sum_value;
set @n_cs_value = cs_value;
set @c_yn_gubun = yn_gubun;
set @n_tot_sum = tot_sum;
Set temporary option Temp_Extract_Column_Delimiter='|';
Set temporary option Temp_Extract_Row_Delimiter = '\n';
Set temporary option TEMP_EXTRACT_Append='On';
Set temporary option TEMP_EXTRACT_NULL_AS_EMPTY='On';
Set temporary option TEMP_EXTRACT_NULL_AS_ZERO='Off';
set temporary option Temp_Extract_Name1='url/test.dat';
WHILE @v_value1 > 1 LOOP
CASE WHEN @v_value1 > 2097152 THEN
select '21',2097152,'랜덤' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 1048576 THEN
select '20',1048576,'경유지' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 524288 THEN
select '19',524288,'출항지' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 262144 THEN
select '18',262144,'전출항지' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 131072 THEN
select '17',131072,'최초 출항지' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 65536 THEN
select '16',65536,'회사소속배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 32768 THEN
select '15',32768,'*' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 16384 THEN
select '14',16384,'*' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 8192 THEN
select '13',8192,'*' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 4096 THEN
select '12',4096,'물품배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 2048 THEN
select '11',2048,'국가배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 1024 THEN
select '10',1024,'항해구분' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 512 THEN
select '9',512,'*' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 256 THEN
select '8',256,'배종류' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 128 THEN
select '7',128,'승무원배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 64 THEN
select '6',64,'북한배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 32 THEN
select '5',32,'중국배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 16 THEN
select '4',16,'러시아배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 8 THEN
select '3',8,'한국배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 4 THEN
select '2',4,'기타배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 2 THEN
select '1',2,'과거배' into @c_gubun1, @n_value1, @c_gubun1_name;
WHEN @v_value1 > 1 THEN
select '0',1,'현재배' into @c_gubun1, @n_value1, @c_gubun1_name;
END CASE;
set @v_value1 = @v_value1 - @n_value1;
select @c_text1,@c_gubun1,@c_gubun1_name,@n_value1,@c_date1,@c_date2,@c_gubun_yn,@c_result,@n_sum_value,@n_cs_value,@c_yn_gubun,@n_tot_sum;
set temporary option Temp_Extract_Name1='';
END LOOP
End For;
commit;
end;
end
이렇게 작성을 했는데 .dat파일로 떨어지는건 아무것도 없거나 약간 수정을 하면 loop문을 안타는지 그냥 select된 순수 value값만 .dat로 떨어집니다.
대체 어디가 어떻게 틀린걸까요? ㅜ.ㅜ
|
