아래와 같이 출력을 하고자 합니다.
도움을 요청드립니다.
대략 5개의 사내 협력사가 각 일자별 작업실적을 관리하는 기능을 구현하려 합니다.
구현상에있어서 해결이 되지 않아 이렇게 요청드립니다.
각업체의 실적정보가 해당일자에 한꺼번에 가로로 표기를 하고 싶습니다.
쿼리실력이 미진하여^^; 위의 결과는 한업체의 실적을 출력하고 다시 다른업체의 실적을 출력하기 때문에 ROW수도 많을 뿐만아니라 보기도 좀 그렇습니다.
해ㅤㄱㅕㅎ할 수 있도록 도움 부탁드립니다.
아래는 쿼리입니다.
SELECT
WORK_DATE ,
V100_PLET_NO,
V100_RTNG_CODE,
V100_PLET_QTY,
V100_PLET_WT,
V100_VNDR_NAME_KOR,
V100_OWNER_PLET_NO,
V200_PLET_NO,
V200_RTNG_CODE,
V200_PLET_QTY,
V200_PLET_WT,
V200_VNDR_NAME_KOR,
V200_OWNER_PLET_NO,
V300_PLET_NO,
V300_RTNG_CODE,
V300_PLET_QTY,
V300_PLET_WT,
V300_VNDR_NAME_KOR,
V300_OWNER_PLET_NO,
V400_PLET_NO,
V400_RTNG_CODE,
V400_PLET_QTY,
V400_PLET_WT,
V400_VNDR_NAME_KOR,
V400_OWNER_PLET_NO,
V500_PLET_NO,
V500_RTNG_CODE,
V500_PLET_QTY,
V500_PLET_WT,
V500_VNDR_NAME_KOR,
V500_OWNER_PLET_NO FROM (
SELECT TEMP.WORK_DATE ,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.PLET_NO,'') V100_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.RTNG_CODE,'') V100_RTNG_CODE,
SUM(DECODE(TEMP.VNDR_CODE,'V100',TEMP.PLET_QTY,0)) V100_PLET_QTY,
SUM(DECODE(TEMP.VNDR_CODE,'V100',TEMP.PLET_WT,0)) V100_PLET_WT,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.VNDR_NAME_KOR,'') V100_VNDR_NAME_KOR,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.OWNER_PLET_NO,'') V100_OWNER_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.PLET_NO,'') V200_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.RTNG_CODE,'') V200_RTNG_CODE,
SUM(DECODE(TEMP.VNDR_CODE,'V200',TEMP.PLET_QTY,0)) V200_PLET_QTY,
SUM(DECODE(TEMP.VNDR_CODE,'V200',TEMP.PLET_WT,0)) V200_PLET_WT,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.VNDR_NAME_KOR,'') V200_VNDR_NAME_KOR,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.OWNER_PLET_NO,'') V200_OWNER_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.PLET_NO,'') V300_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.RTNG_CODE,'') V300_RTNG_CODE,
SUM(DECODE(TEMP.VNDR_CODE,'V300',TEMP.PLET_QTY,0)) V300_PLET_QTY,
SUM(DECODE(TEMP.VNDR_CODE,'V300',TEMP.PLET_WT,0)) V300_PLET_WT,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.VNDR_NAME_KOR,'') V300_VNDR_NAME_KOR,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.OWNER_PLET_NO,'') V300_OWNER_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.PLET_NO,'') V400_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.RTNG_CODE,'') V400_RTNG_CODE,
SUM(DECODE(TEMP.VNDR_CODE,'V400',TEMP.PLET_QTY,0)) V400_PLET_QTY,
SUM(DECODE(TEMP.VNDR_CODE,'V400',TEMP.PLET_WT,0)) V400_PLET_WT,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.VNDR_NAME_KOR,'') V400_VNDR_NAME_KOR,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.OWNER_PLET_NO,'') V400_OWNER_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.PLET_NO,'') V500_PLET_NO,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.RTNG_CODE,'') V500_RTNG_CODE,
SUM(DECODE(TEMP.VNDR_CODE,'V500',TEMP.PLET_QTY,0)) V500_PLET_QTY,
SUM(DECODE(TEMP.VNDR_CODE,'V500',TEMP.PLET_WT,0)) V500_PLET_WT,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.VNDR_NAME_KOR,'') V500_VNDR_NAME_KOR,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.OWNER_PLET_NO,'') V500_OWNER_PLET_NO
FROM
(SELECT 'V100' VNDR_CODE,
SM950M.GRND_FD WORK_DATE,
SM950M.RTNG_PLET_NO ||'-('|| SM950M.OWNER_PLET_NO||')'||DECODE(NVL(SUBSTR(SM900M.WORK_CNT,1,1),'0'),'*','*',' ')PLET_NO,
SM950M.RTNG_CODE RTNG_CODE ,
SM950M.PLET_QTY PLET_QTY,
SM950M.PLET_WT PLET_WT,
STD99M.VNDR_NAME_KOR VNDR_NAME_KOR,
SM950M.OWNER_PLET_NO OWNER_PLET_NO
FROM SM950M , STD99M , SM900M
WHERE SM950M.GRND_FD BETWEEN '$Fdate1$' AND '$Fdate2$'
AND SM950M.RTNG_PLET_NO = SM900M.RTNG_PLET_NO
AND SM950M.OWNER_CODE = SM900M.OWNER_CODE
AND SM900M.OWNER_CODE = '$FownerCode$'
AND STD99M.VNDR_CODE = '$FvndrCode$'
AND SM950M.GRND_WORK_VNDR_CODE || '00' = STD99M.VNDR_CODE
UNION ALL
SELECT 'V200' VNDR_CODE,
SM950M.PEND_FD WORK_DATE,
SM950M.RTNG_PLET_NO ||'-('|| SM950M.OWNER_PLET_NO||')'||DECODE(NVL(SUBSTR(SM900M.WORK_CNT,1,1),'0'),'*','*',' ') PLET_NO,
SM950M.RTNG_CODE RTNG_CODE ,
SM950M.PLET_QTY PLET_QTY ,
SM950M.PLET_WT PLET_WT,
STD99M.VNDR_NAME_KOR VNDR_NAME_KOR,
SM950M.OWNER_PLET_NO OWNER_PLET_NO
FROM SM950M , STD99M , SM900M
WHERE SM950M.PEND_FD BETWEEN '$Fdate1$' AND '$Fdate2$'
AND SM950M.RTNG_PLET_NO = SM900M.RTNG_PLET_NO
AND SM950M.OWNER_CODE = SM900M.OWNER_CODE
AND SM900M.OWNER_CODE = '$FownerCode$'
AND STD99M.VNDR_CODE = '$FvndrCode$'
AND SM950M.PEND_WAIT_VNDR_CODE || '00' = STD99M.VNDR_CODE
UNION ALL
SELECT 'V300' VNDR_CODE,
SM950M.PNT_FD WORK_DATE,
SM950M.RTNG_PLET_NO ||'-('|| SM950M.OWNER_PLET_NO||')'||DECODE(NVL(SUBSTR(SM900M.WORK_CNT,1,1),'0'),'*','*',' ') PLET_NO,
SM950M.RTNG_CODE RTNG_CODE ,
SM950M.PLET_QTY PLET_QTY,
SM950M.PLET_WT PLET_WT,
STD99M.VNDR_NAME_KOR VNDR_NAME_KOR,
SM950M.OWNER_PLET_NO OWNER_PLET_NO
FROM SM950M , STD99M , SM900M
WHERE SM950M.PNT_FD BETWEEN '$Fdate1$' AND '$Fdate2$'
AND SM950M.RTNG_PLET_NO = SM900M.RTNG_PLET_NO
AND SM950M.OWNER_CODE = SM900M.OWNER_CODE
AND SM900M.OWNER_CODE = '$FownerCode$'
AND STD99M.VNDR_CODE = '$FvndrCode$'
AND SM950M.MNL_WORK_VNDR_CODE || '00' = STD99M.VNDR_CODE
UNION ALL
SELECT 'V400' VNDR_CODE,
SM950M.PNT_FD WORK_DATE,
SM950M.RTNG_PLET_NO ||'-('|| SM950M.OWNER_PLET_NO||')'||DECODE(NVL(SUBSTR(SM900M.WORK_CNT,1,1),'0'),'*','*',' ') PLET_NO,
SM950M.RTNG_CODE RTNG_CODE ,
SM950M.PLET_QTY PLET_QTY,
SM950M.PLET_WT PLET_WT,
STD99M.VNDR_NAME_KOR VNDR_NAME_KOR,
SM950M.OWNER_PLET_NO OWNER_PLET_NO
FROM SM950M , STD99M , SM900M
WHERE SM950M.PNT_FD BETWEEN '$Fdate1$' AND '$Fdate2$'
AND SM950M.RTNG_PLET_NO = SM900M.RTNG_PLET_NO
AND SM950M.OWNER_CODE = SM900M.OWNER_CODE
AND SM950M.AUTO_WORK_VNDR_CODE || '00' = STD99M.VNDR_CODE
AND SM900M.OWNER_CODE = '$FownerCode$'
AND STD99M.VNDR_CODE = '$FvndrCode$'
UNION ALL
SELECT 'V500' VNDR_CODE,
SM950M.DLVY_WAIT_DATE WORK_DATE,
SM950M.RTNG_PLET_NO ||'-('|| SM950M.OWNER_PLET_NO||')'||DECODE(NVL(SUBSTR(SM900M.WORK_CNT,1,1),'0'),'*','*',' ') PLET_NO,
SM950M.RTNG_CODE RTNG_CODE ,
SM950M.PLET_QTY PLET_QTY,
SM950M.PLET_WT PLET_WT,
STD99M.VNDR_NAME_KOR VNDR_NAME_KOR,
SM950M.OWNER_PLET_NO OWNER_PLET_NO
FROM SM950M , STD99M , SM900M
WHERE SM950M.DLVY_WAIT_DATE BETWEEN '$Fdate1$' AND '$Fdate2$'
AND SM950M.STOR_WORK_VNDR_CODE || '00' = STD99M.VNDR_CODE
AND SM950M.RTNG_PLET_NO = SM900M.RTNG_PLET_NO
AND SM950M.OWNER_CODE = SM900M.OWNER_CODE
AND SM900M.OWNER_CODE = '$FownerCode$'
AND STD99M.VNDR_CODE = '$FvndrCode$'
) TEMP
GROUP BY TEMP.WORK_DATE,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.RTNG_CODE,'') ,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.VNDR_NAME_KOR,'') ,
DECODE(TEMP.VNDR_CODE,'V100',TEMP.OWNER_PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.RTNG_CODE,'') ,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.VNDR_NAME_KOR,'') ,
DECODE(TEMP.VNDR_CODE,'V200',TEMP.OWNER_PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.RTNG_CODE,'') ,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.VNDR_NAME_KOR,'') ,
DECODE(TEMP.VNDR_CODE,'V300',TEMP.OWNER_PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.RTNG_CODE,'') ,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.VNDR_NAME_KOR,'') ,
DECODE(TEMP.VNDR_CODE,'V400',TEMP.OWNER_PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.PLET_NO,'') ,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.RTNG_CODE,'') ,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.VNDR_NAME_KOR,'') ,
DECODE(TEMP.VNDR_CODE,'V500',TEMP.OWNER_PLET_NO,'')
)
ORDER BY WORK_DATE
데이타베이스 : 오라클
부탁 드립니다.
|