- PR -

数値の判別入力について

1
投稿者投稿内容
くまっこ
会議室デビュー日: 2007/08/16
投稿数: 2
投稿日時: 2007-08-16 07:35
初めまして、oracle10gにて以下表を完成させようとしています。
しかしいろいろと条件を加えた結果うまく作成できません。

初心者ですので勉強不足でどのようなSQL、PL/SQLを
用いればよいのかわかりません。

どなたかご教授の程おねがいいたします。

●完成させたい表

基本コード NO1 NO2 取引番号 取引番号2 コード
---------- ---------- ---------- ---------- ---------- ----------

100 1 1 3 81915 0
100 2 1 4 862547 0
100 2 2 16 862547 0
100 3 1 6 498879 0
100 4 1 7 408783 0
100 5 1 8 83488 0
100 6 1 9 82385 0
100 7 1 11 99607 0
100 8 1 12 352730 0
100 9 1 14 706444 0
100 10 1 15 783458 1
100 10 2 18 783458 0
100 11 1 17 876015 0
100 11 2 19 876015 1
100 11 3 21 876015 0
100 12 1 20 881000 0
100 12 2 22 881000 1
100 12 3 24 881000 1
100 12 4 25 881000 1
100 13 1 23 909153 0
100 14 1 26 938457 0
100 15 1 27 925333 0
200 1 1 1 738194 0
200 1 2 10 738194 1
200 1 3 13 738194 1
200 2 1 2 716382 0
200 3 1 5 82076 0
200 4 1 28 881000 0

・NO1、NO2は作成時未入力です。今回はその他の列の値を判別し、
　NO1、NO2に番号を振るSQL、またはPL/SQLをご教授いただければと思い
　ご連絡させていただきました。

●入力条件
　・NO1は連番だが、基本コード、取引番号が同一の場合、同じ番号が入力されます。

　基本コード NO1 NO2 取引番号 取引番号2 コード
　---------- ---------- ---------- ---------- ---------- ----------

100 1 1 3 81915 0
100 2 1 4 862547 0
100 2 2 16 862547 0

　・NO2はNO1の値が連続した場合のみ昇順の値を入力。複数のNO1同一値が
　　無い場合は1を入力。

　・基本コードが変更(上記例だと100→200)になった場合、NO1は
　 1から振りなおし。

　基本コード NO1 NO2 取引番号 取引番号2 コード
　---------- ---------- ---------- ---------- ---------- ----------
　100 14 1 26 938457 0
　 100 15 1 27 925333 0
　 200 1 1 1 738194 0
　 200 1 2 10 738194 1

●表作成SQL
CREATE TABLE test1 (
"基本コード" NUMBER(3) NOT NULL,
NO1 NUMBER(3),
NO2 NUMBER(2),
"取引番号" NUMBER(7) NOT NULL,
"取引番号2" 　　　　　　　NUMBER(7),
"コード" 　　NUMBER(1),
PRIMARY KEY("基本コード", "顧客No")
)
/

●データ入力
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,1,738194,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,2,716382,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,3,81915,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,4,862547,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,5,82076,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,6,498879,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,7,408783,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,8,83488,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,9,82385,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,10,738194,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,11,99607,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,12,352730,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,13,738194,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,14,706444,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,15,783458,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,16,862547,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,17,876015,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,18,783458,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,19,876015,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,20,881000,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,21,876015,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,22,881000,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,23,909153,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,24,881000,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,25,881000,1);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,26,938457,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(100,27,925333,0);
insert into test1 ("基本コード","取引番号","取引番号2","コード") values
(200,28,881000,0);

●自分で作ったSQL、PL/SQL(失敗例)
　・NO1に番号を入力
update test1 t1 set NO1=(
select NO1 from (select rownum NO1,"基本コード","取引番号2"
from (select "基本コード", "取引番号2"
from test1 group by "基本コード","取引番号2"
order by "基本コード","取引番号2")) t2
　　　　　 where t1."基本コード"=t2."基本コード"and t1."取引番号2"=t2."
取引番号2");


　・NO2に番号を入力
update test1 t4 set NO2=(
select NO2
from (select NO1,"取引番号2","取引番号","基本コード",1 + (select count(*)
from test1 t3
where t3."取引番号2" = test1."取引番号2"
and t3."基本コード" = test1."基本コード"
and t3."取引番号" < test1."取引番号")
as NO2
from test1
order by test1.NO1)t5
where t4."取引番号"=t5."取引番号");




　　　　　
　　　　　
　・基本コード変更時にNO1の値をリセットする
　　(基本コード、取引番号が同一でもNO1に連番が振られてしまう為、失敗)
begin
for cur in (select distinct "基本コード" from test1) loop
dbms_output.put_line(cur."基本コード");
　　
for cur2 in ( select "取引番号2",rownum from test1
where "基本コード"=cur."基本コード" order by "取引番号2") loop

　　　　dbms_output.put_line(cur2."取引番号");

update test1 set NO1 = cur2.rownum
where "基本コード"=cur."基本コード"
and "取引番号2"=cur2."取引番号2"

end loop;
end loop;
end;
/

ごちゃごちゃしてますが、ご教授お願いします！
未記入
会議室デビュー日: 2007/07/12
投稿数: 18
投稿日時: 2007-08-16 14:05
まず、表作成SQLが間違っています。
その修正版を提示したらそれなりの反応があるかもしれませんね。

あと、どういう順番（並び）で採番したいのかを明確にして下さい。
失敗例のSQLから想像はできますが、失敗例ですからね。

# SQL文中の空白に全角を混ぜるのは止めて欲しい。


[ メッセージ編集済み 編集者: 未記入 編集日時 2007-08-16 14:09 ]
chamaro
会議室デビュー日: 2002/07/28
投稿数: 10
投稿日時: 2007-08-16 17:22
↓イメージ（メモ帳に打っただけなのでシンタックスエラーになると思うがこんな感じ）


DECLARE
　recBack test1%ROWTYPE;
BEGIN
　FOR rec IN (SELECT * FROM test1 ORDER BY 基本コード,取引番号) LOOP
　　IF recBack IS NULL OR recBack.基本コード <> rec.基本コード THEN
　　　rec.NO1 = 1;
　　　rec.NO2 = 1;
　　ELSE
　　　IF recBack.取引番号 <> rec.取引番号 THEN
　　　　rec.NO1 = recBack.NO1 + 1;
　　　　rec.NO2 = 1;
　　　ELSE
　　　　rec.NO1 = recBack.NO1;
　　　　rec.NO2 = recBack.NO2 + 1;
　　　END IF;
　　END IF;

　　update test1 set NO1 = rec.NO1, NO2 = rec.NO2 WHERE 基本コード = rec.基本コード AND 顧客No = rec.顧客No;
　　recBack = rec;
　END LOOP
END;

# 最終的に正しいか確認して己でコミットしてください。
# 項目にないけど、顧客NoはP KEYだよね？
未記入
会議室デビュー日: 2007/07/12
投稿数: 18
投稿日時: 2007-08-16 18:10
とりあえず、Primary Key の顧客Noを取引番号に置き換えて
やってみました。
件数が多いなら、PL/SQLでprogrammingするのが良さそうですが
少ない件数なら、一つのSQL文でまとめれますね。

コード:
update test1 t1
  set no1 = (
       select num
       from (
         select rowid, 
             dense_rank() over (partition by "基本コード" 
                      order by "基本コード", "取引番号2") as num
             from test1
            ) t2
        where t1.rowid = t2.rowid
      ),
      no2 = (
       select num
       from (
         select rowid, 
             row_number() over (partition by "基本コード", no1 
                      order by no1) as num
             from test1
            ) t2
        where t1.rowid = t2.rowid
      )
;

並べ方が思惑と違うはずなので、その点は調整が必要です。
くまっこ
会議室デビュー日: 2007/08/16
投稿数: 2
投稿日時: 2007-08-16 23:49
ありがとうございまふ！

無事に作成できました〜。うふふ。
1

スキルアップ／キャリアアップ（JOB@IT）