꿈꾸는 개발자, DBA 커뮤니티 구루비

쿼리 질문드립니다..

by 풋내기 [SQL Query] [2010.01.29 14:10:06]

SELECT WRK640_SUBJECT,
    WRK640_PROG_NAME,
CASE
WHEN WRK640_STATUS ='S' THEN
CASE
  WHEN (WRK640_REQUEST_START_DATE [= SYSDATE AND WRK640_REQUEST_END_DATE ]= SYSDATE ) AND (:WRK641TL_SELECT_COUNT + NVL(WRK640_WAITING_COUNT,0)) ]= WRK640_ATTENDANCE_COUNT THEN
   '선발중'
  WHEN (WRK640_REQUEST_START_DATE [= SYSDATE AND WRK640_REQUEST_END_DATE ]= SYSDATE ) AND (:WRK641TL_SELECT_COUNT + NVL(WRK640_WAITING_COUNT,0)) [ WRK640_ATTENDANCE_COUNT THEN
   '접수시작'
  WHEN (WRK640_REQUEST_START_DATE ] SYSDATE ) THEN
   '진행예정'
  WHEN (WRK640_REQUEST_END_DATE [ SYSDATE ) THEN
   '선발중'
  END
ELSE
'선발완료'
END AS WRK640_RESULT,
TO_CHAR(WRK640_REQUEST_START_DATE,'yyyy.MM.dd HH24') AS WRK640_REQUEST_START_DATE,
TO_CHAR(WRK640_REQUEST_END_DATE,'yyyy.MM.dd HH24') AS WRK640_REQUEST_END_DATE,
WRK640_ATTENDANCE_COUNT_TYPE,
WRK640_ATTENDANCE_COUNT,
WRK640_WAITING_COUNT,
WRK640_REQ_CALENDAR_VISIBLE_YN,
WRK640_REQUEST_METHOD,
WRK640_USE_YN,
WRK640_INPUT_DATE,
WRK640_STATUS,
WRK640_CONTENTS,
WRK640_START
, A.WRK130_CODE_NAME AS METHODNAME
, B.WRK130_CODE_NAME AS TYPENAME
, WRK640_NOTE
, WRK640_NOTE_DESC
, WRK640_FILE_DESC
, WRK640_REMARK
  FROM WRK640TL
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME
    FROM WRK130TL
WHERE WRK130_CODE_PKID = 'REQM'
    ) A
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME
    FROM WRK130TL
WHERE WRK130_CODE_PKID = 'ATTY'
    ) B
WHERE A.WRK130_SUB_CODE_PKID = WRK640_REQUEST_METHOD
    AND B.WRK130_SUB_CODE_PKID = WRK640_ATTENDANCE_COUNT_TYPE
    AND WRK640_INDEX = :WRK640_INDEX

위 쿼리안에없는 또다른 EXTABLE 이름의 테이블안에 NUM이라는 컬럼이있는데요.
NUM이라는 컬럼데이타값이 위에 WRK640_INDEX 값과 같으면 EXTABLE의 NAME이라는 컬럼값을
뿌려주는걸 추가하고싶은데 제가 부족해서 잘모르겠네요..설명도 부족하겠지만.. 조언부탁드립니다.

by pran [2010.01.29 15:26:55]

SELECT .....
, (select name from extable where num = c.WRK640_INDEX) as extable_name
FROM WRK640TL c
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME FROM WRK130TL WHERE WRK130_CODE_PKID = 'REQM') A
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME FROM WRK130TL WHERE WRK130_CODE_PKID = 'ATTY') B
WHERE A.WRK130_SUB_CODE_PKID = WRK640_REQUEST_METHOD
AND B.WRK130_SUB_CODE_PKID = WRK640_ATTENDANCE_COUNT_TYPE
AND WRK640_INDEX = :WRK640_INDEX
..........................................
SELECT .....
, c.name as extable_name
FROM WRK640TL c
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME FROM WRK130TL WHERE WRK130_CODE_PKID = 'REQM') A
, (SELECT WRK130_SUB_CODE_PKID, WRK130_CODE_NAME FROM WRK130TL WHERE WRK130_CODE_PKID = 'ATTY') B
, (SELECT num, name FROM extable) c
WHERE A.WRK130_SUB_CODE_PKID = WRK640_REQUEST_METHOD
AND B.WRK130_SUB_CODE_PKID = WRK640_ATTENDANCE_COUNT_TYPE
and wrk640_index = c.num(+)
AND WRK640_INDEX = :WRK640_INDEX
둘중 하나의 형태를 가지면 되지 않을까요?

by 풋내기 [2010.01.29 17:04:03]

답변감사합니다...둘다해봤는데 되진않네요;

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변