style24.doc/산출물/3.설계/05.DB-Object/01.db_view/VW_STOCK.sql

DROP VIEW VW_STOCK;
CREATE VIEW VW_STOCK AS
SELECT A.GOODS_CD
     , A.OPT_CD
     , A.OPT_CD1
     , A.OPT_CD2
     , A.CURR_STOCK_QTY
     , 0                AS BASE_STOCK_QTY
     , A.ADD_PRICE
     , A.SOLDOUT_YN
     , A.DISP_ORD
     , A.DISP_YN
FROM   (
        SELECT S.GOODS_CD
             , S.OPT_CD
             , S.OPT_CD1
             , S.OPT_CD2
             , GREATEST((GREATEST(((GREATEST(S.CURR_STOCK_QTY,0) + GREATEST(S.STORE_STOCK_QTY,0)) - S.BASE_STOCK_QTY),0) - IFNULL(SQ.STOCK_QTY, 0)),0) AS CURR_STOCK_QTY
             , S.SOLDOUT_YN
             , S.ADD_PRICE
             , S.DISP_ORD
             , S.DISP_YN
        FROM   TB_OPTION S
        LEFT OUTER JOIN (
                         SELECT GOODS_CD
                              , OPT_CD
                              , SUM((CASE SELL_GB
                                          WHEN '10' THEN 1
                                          WHEN '20' THEN 1
                                          ELSE -1
                                     END) * SELL_QTY) AS STOCK_QTY
                         FROM   TB_SELL_QTY
                         GROUP  BY GOODS_CD, OPT_CD
                        ) SQ ON S.GOODS_CD = SQ.GOODS_CD
                            AND S.OPT_CD = SQ.OPT_CD
       ) A
;