Javaに関する様々な情報をご紹介します。

Javaに関する様々な情報をご紹介します。
評価

0

with句などをつかってSQL分離

部分的にselect句を分離させて、定義したいです。
with句かストアドなどを使って、動的にパラメータを挿入したい。


SELECT
       hdrbase.outstockdate,
       hdrbase.base_to,
       hdrbase.base_from,
       hdrbase.modelsum,
       hdrbase.taku as no,
    case     
      when hdrbase.taku<>'1'
        then (
                SELECT
                    count(dtl.control_id)
                FROM
                    CONTROL_DTL dtl left join CONTROL_HDR cthdr on(dtl.control_id=cthdr.control_id)
                WHERE
                    hdrbase.outstockdate=cthdr.outstockdate AND
                    hdrbase.base_to=cthdr.base_to AND
                    hdrbase.base_from=cthdr.base_from and
                  hdrbase.taku=cthdr.orderid and
                    dtl.deliverdate is not null

             ) 
        else (
                SELECT
                    count(dtl.control_id)
                FROM
                    CONTROL_DTL dtl left  join CONTROL_HDR cthdr on(dtl.control_id=cthdr.control_id)
                WHERE
                    hdrbase.outstockdate=cthdr.outstockdate AND
                    hdrbase.base_to=cthdr.base_to AND
                    hdrbase.base_from=cthdr.base_from  and
                    dtl.deliverdate is not null

             ) 
    end deliveryDate
,
    case     
      when hdrbase.taku<>'1'
        then (
                SELECT
                    count(dtl.control_id)
                FROM
                    CONTROL_DTL dtl left join CONTROL_HDR cthdr on(dtl.control_id=cthdr.control_id)
                WHERE
                    hdrbase.outstockdate=cthdr.outstockdate AND
                    hdrbase.base_to=cthdr.base_to AND
                    hdrbase.base_from=cthdr.base_from and
                  hdrbase.taku=cthdr.orderid and
                    dtl.recieptdate is not null

             ) 
        else (
                SELECT
                    count(dtl.control_id)
                FROM
                    CONTROL_DTL dtl left  join CONTROL_HDR cthdr on(dtl.control_id=cthdr.control_id)
                WHERE
                    hdrbase.outstockdate=cthdr.outstockdate AND
                    hdrbase.base_to=cthdr.base_to AND
                    hdrbase.base_from=cthdr.base_from  and
                    dtl.recieptdate is not null

             ) 
    end recieptDate
        
    FROM
        (
    SELECT
            hdr.outstockdate,
            hdr.base_to,
            hdr.base_from,
            sum(hdr.modelcount_plan) as modelsum     ,
            hdr.taku
        FROM
            (SELECT
                control_id,
                outstockdate,
                base_to,
                base_from,
                modelcount_plan,
                orderid,
                  (case        when base_to ='ddd' then orderid        else '1'       end) taku
            FROM
                control_hdr
            ) hdr
        GROUP BY
            hdr.outstockdate,
            hdr.base_to,
            hdr.base_from,
            hdr.taku
) hdrbase
    

1

回答

7159

閲覧

1件の回答

評価

0

丸々SQLの話だな。
SQL向けの掲示板に行こう。

質問から6ヶ月以上経過しているので、回答を書き込むことはできません。