Hatena::ブログ(Diary)

CAMUSのこくばん −クエリの板書所− このページをアンテナに追加 RSSフィード

2017/12/22 どうしても定義域集計関数を使わずに最小値をUPDATEしたかった

8年間ほったらかしてました、このDiary。(苦笑)

SQLなネタはいろいろあったけど、抽象化して書くのがなかなか難しくて放置してます。すみません。

[][]どうしても定義域集計関数を使わずに最小値をUPDATEしたかった

今日は、Access相手に

 TBL_Bを今日更新した場合、そのCOL_Bの最小の値をTBL_AのCOL_Bに反映させたいねん

 ちなみにキーはCOL_Aです。

という要求がありまして、いろいろやったけどダメでした。

こんなの書いたりしたら

UPDATE TBL_A 
SET
  COL_B = (SELECT MIN(COL_B) FROM TBL_B WHERE TBL_A.COL_A = TBL_B.COL_A)
WEHRE EXISTS (SELECT * FROM TBL_B 
               WHERE TBL_B.COL_DATE = #FORMAT(NOW(), 'yyyy/mm/dd')#
                 AND TBL_A.COL_A = TBL_B.COL_A
             ) 

Accessさんにサブクエリだめって怒られ、

また、こんなの書いたら

UPDATE TBL_A INNER JOIN 
       (SELECT COL_A, MIN(COL_B) AS MIN_COL_B FROM TBL_B GROUP BY COL_A) B
             ON TBL_A.COL_A = TBL_B.COL_A
SET
  COL_B = MIN_COL_B
WEHRE EXISTS (SELECT * FROM TBL_B 
               WHERE TBL_B.COL_DATE = #FORMAT(NOW(), 'yyyy/mm/dd')#
                 AND TBL_A.COL_A = TBL_B.COL_A
             ) 

これでもサブクエリダメって怒られるわけです。(サブクエリあかんねんていわれてるから当たり前やがな)

どうしたもんかと思ってたら、Googleさんに神様イター!

 更新クエリで定義域集計関数を使わずに集計する

そう、定義域集計関数を使いたくなかったんです。ADOで動くと思えなかったんで。

その結果、こうなりました。

UPDATE (TBL_A INNER JOIN TBL_B AS B1 ON TBL_A.COL_A=B1.COL_A)
              INNER JOIN TBL_B AS B2 ON B1.COL_A=B2.COL_A
SET
  TBL_A.COL_B = IIF(TBL_A.COL_B > B2.COL_B, B2.COL_B, TBL_A.COL_B)
WEHRE B1.COL_DATE = #FORMAT(NOW(), 'yyyy/mm/dd')#

ばっちしでした。ありがとうございました。

2009/07/06 ORDER BY して TOP 1 で出してほしくない例

[][]ORDER BY して TOP 1 で出してほしくない例

久々にSQL Serverいじってます。

超レスポンス悪いんですけどみてもらえます?といわれて出されたSQLにこんなのがおいでになりました。

SELECT 
  COL_KEY,
  (SELECT TOP 1 COL_NUM
     FROM TBL_B B
    WHREE B.COL_KEY = A.COL_KEY
    ORDER BY COL_NUM DESC) COL_NUM
  FROM TBL_A A
 WEHRE A.COL_DATE > '2009/04/01' 

実行プラン見ると、サブクエリ部分で98%も食っておいでになる。きゅう。(>_<)

SELECT 
  COL_KEY,
  (SELECT MAX(COL_NUM)
     FROM TBL_B B
    WHREE B.COL_KEY = A.COL_KEY) COL_NUM
  FROM TBL_A A
 WEHRE A.COL_DATE > '2009/04/01' 

これでええやんー。

2008/12/26 仕事納めでした&仕事場ではお絵かきしてました

[]まんだらと呼ばれたもの

最近、SQLを書く前とか、ヒトの書いたSQLとかを絵に描いてみるようにしています。

絵に描いていると「マインドマップ?」とか言われちゃったりするんですが、「まんだら」と呼んでくれる後輩がいて、その呼び方が気に入っちゃったので、「まんだら」と呼ぶようになりました。


自分がSQLを書く前に描くまんだらがこんな感じです。(ぼかしているのはわざとです…)

色がアレなのはちょっとぐらい気持ちをアゲ気味にしたかったからですから気にしないでください。

f:id:CAMUS:20081226221932j:image

このSQLを描くときに思ってたのは、とにかく複雑すぎて判らん!ってことでした。

ある業務の工程ごとの状態を出力してほしいって言うリクエストだったので、工程ごとにSELECT文を書いたのをUNION ALLでがっちゃんこしてみたっていうものでOKだろう…というところまでは頭の中でイメージできてたのですが、実際どうしたらいいんかサッパリ判らなくて絵を描いてみたって感じです。

「ある業務の工程ごと」っていうのもぬわ〜ってなるぐらい複雑だったのですが、視覚化するとぬわ〜っむぅ…ぐらいに軽減されたので、個人的には効果はまぁまぁあるかと。

ただ、自分で描き方のルールを決めてないので、気分によって色が変わったり、描きかたが変わったりしてしまうので、あんまり他人が読むドキュメントとして残しておくレベルのもんじゃなかったりするのが、難点です。


で、ヒトの書いたSQLをまんだら化するときは、ノートにフリクションペンでぐりぐりーっと描きます。

こっちはよく「マインドマップ?」と訊かれます。(ぼかしているのはわざとではないんですが、いい感じにボケてくれたんです…)

f:id:CAMUS:20081121163425j:image

こっちもあんまりルール化はしてないんですが、まだ試行錯誤中ですね…。

とりあえず、見てすぐに INNER JOIN と OUTER JOIN ぐらいの区別はつけるように、OUTER JOIN は片側がぶら下がってる(LEFT or RIGHT)のか、FULL なのかが判るといいカナと。

今のところ、INNER JOIN と EXISTS句/IN句 との区別や、OUTER JOINとサブクエリの区別がついていないところがよろしくないと思ってます。

何とかせねば…。


ちょっと精進したら、またここでいろいろさらしてみます。

なんかいいアイデアあったらください…なんて書いてみたりして。(^^;)


では、よいお年を。

2008/12/25

[][]OracleのCase式のメモ:その後

OracleのCase式のメモとか書いておきながら、その後を書いていなかったです。

いま、Oracle10g(10.2)系と戯れています。

例えば、

create table tbl_a (
    key1        char(10),
    column_type char(1),
    column_a    char(10),
    column_b    varchar2(10)
);

みたいなテーブルがあったとして、

select
    case column_type
        when 'A' then column_a
        else          column_b
    end      d_results
from 
    tbl_a
;

って書いたら、「ORA-12704: キャラクタ・セットが一致しません」がでます。

TRIMでも使ったらVARCHAR2になりよるから、こうしたらええやん…

select
    case column_type
        when 'A' then trim(column_a)
        else          column_b
    end      d_results
from 
    tbl_a
;

って書いても、「ORA-12704: キャラクタ・セットが一致しません」がでます。(涙)

なのに、

select
    decode(column_type, 
           'A', column_a,
           column_b)      d_results
from 
    tbl_a
;

って書いたら、何にも怒られないんですっ。(怒)

なんでなんですか、おらくるさんっ!

[][]View内部でUnionしてると…

これもなんか美しくなくてうにゃー。

さっきと同じようでちょっと違うテーブル使おう。

create table tbl_a (
    key1        char(10),
    column_type char(1),
    column_a    char(10),
    column_b    nvarchar2(10),
    num1        number(10, 2)
);

コレを使ってこんなViewを作ったとする。

create or replace view vw_a as
select
    key1,
    column_a,
    num1
from
    tbl_a
where
    column_type = 'A'
union all
select
    key1,
    column_b column_a,
    num1
from
    tbl_a
where
    column_type = 'B'
;

で、このViewに対して、こんなSQLを発行したら、型が違うと怒られる。

select
    column_a,
    sum(num1) num1
from
    vw_a
group by 
    column_a
;

しゃーないので、↓こんなふうにCASTかましてやると、上のSQLも動くようになった。

create or replace view vw_a as
select
    key1,
    cast(column_a as nvarchar2(10) column_a,
    num1
from
    tbl_a
where
    column_type = 'A'
union all
select
    key1,
    column_b column_a,
    num1
from
    tbl_a
where
    column_type = 'B'
;

で、ここからが本題。上記で型があわんという怒られ方をするようなデータを持っている場合。

creaet materialized view mv_a
--ここは省略
as 
select
    column_a,
    sum(num1) num1
from
    vw_a
group by 
    column_a
;

というのを作ると型があわんから作れへんわーと怒られます。

どうも、Viewの中のCASTは効いてない模様です…。なんでー…?

creaet materialized view mv_a
--ここは省略
as 
select
    cast( column_a as nvarchar2(10)) column_a,
    sum(num1) num1
from
    vw_a
group by 
    cast( column_a as nvarchar2(10))
;

結局こういうぶさいくなことをしなきゃならんかったという…orz

2008/09/11 Oracle の CASE式で

[][]OracleのCASE式メモ

昨日から悩んでいて、今日(しかも帰り際)、CASE式を使っているときに不思議な(困った)現象があることが発覚…。

WHERE句でCASE式を使って出力がCHAR型の場合、なぜか「ORA-12704: キャラクタ・セットが一致しません。」エラーが…。(汗)


特定の場合だけなのか、再現性があるのかわからないので、後で環境のあるところで試すためのメモを書いておきます。

結果は後ほど。

CREATE TABLE t_test (
    c_char01  CHAR(5),
    c_char02  CHAR(5),
    c_vchar   VARCHAR2(5)
);

INSERT INTO t_test values ('A', 'A', 'A    ');

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_vchar
           ELSE NULL END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_char02
           ELSE NULL END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_char01
           WHEN t.c_char02 THEN t.c_char02
           ELSE t.c_vchar END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_vchar
           WHEN t.c_char01 THEN t.c_char02
           ELSE t.c_vchar END = t.c_char01
;

SELECT 1
  FROM t_test t
 WHERE CASE t.c_vchar
           WHEN t.c_vchar THEN t.c_char02
           ELSE t.c_char01 END = t.c_char01
;

さて、どうなるかな…?


会社とおうちとでバイナリなメモをやり取りしにくいので、結果もうろ覚えのまま書く事になるかも。

Connection: close