質問内容
質問を評価する
(0ポイント)
|
部分的に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
|
