* 질문 등록 시 다음의 내용을 꼭 기입하여 주세요.
|
|
Window7 32bit, Linux 64bit 등 |
|
|
[cubrid_rel] 수행 결과 |
|
|
[도움말]-[버전정보] 확인 |
|
|
java, php, odbc 등 입력 |
* CUBRID 응용 오류, SQL 오류 또는 SQL 튜닝 관련된 문의는 반드시 다음의 내용을 추가해 주세요. 비밀글이나 비밀 댓글도 가능합니다.
* 저희가 상황을 이해하고, 재현이 가능해야 알 수 있는 문제들이 많습니다. 가능한 정보/정황들을 부탁합니다.
| 에러 내용 및 재현 방법 | 재현 가능한 Source와 SQL |
| 관련 테이블(인덱스, 키정보 포함) 정보 | CUBRID 홈 디렉토리 아래 log 디렉토리 압축 |
-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
VIEW 생성후 SELECT 시 에러가 납니다.
String 변환후 해도 에러가 동일하네요...
실행 오류 : -494
Semantic: before ' ) [c] ([docid], [oridocid], [docttl], [reportdt], [state], [a...'
'=' operator is not defined on types char and sequence. select bms_audit_view.apprid, bms_audit_view.title, bms_audi...[CAS INFO - 192.168.28.164:33000,5,30204],[SESSION--2554],[URL-jdbc:cubrid:192.168.28.164:33000:onnara_db:onsadmin:********:].
=======================
CREATE VIEW BMS_AUDIT_VIEW(
APPRID VARCHAR(35),
TITLE VARCHAR(255),
DRAFTDATE VARCHAR(20),
DRAFTTIME VARCHAR(20),
DOCUMENT_STATUS VARCHAR(100),
DRAFTERID VARCHAR(35),
DRAFTERNAME VARCHAR(100),
DRAFTDEPTID VARCHAR(35),
DRAFTDEPTNAME VARCHAR(256),
AUDITTYPE VARCHAR(10),
ATTACHFLAG VARCHAR(10),
ISOPINION VARCHAR(10),
ISEDIT VARCHAR(10),
URGENTFLAG VARCHAR(10),
AARSFILE VARCHAR(10),
APPROVER_ID VARCHAR(35),
APPROVER_NAME VARCHAR(100))
AS
SELECT APPRID /**문서 ID**/
, TITLE /**문서제목 **/
, DRAFTDATE /**기안일자**/
, DRAFTTIME /** 기안시간**/
, DOCUMENT_STATUS /** 문서상태**/
, DRAFTERID /**기안자 KID**/
, DRAFTERNAME /** 기안자 이름**/
, DRAFTDEPTID /** 기안자 부서 KID**/
, DRAFTDEPTNAME /**기안자 부서 이름**/
, AUDITTYPE /**감사유형**/
, ATTACHFLAG /**첨부파일 유무**/
, ISOPINION /** 의견 유무**/
, ISEDIT /**수정 유무**/
, URGENTFLAG /**긴급결재 구분**/
, AARSFILE /** 기록물철**/
, APPROVER_ID /**최종결재자 ID**/
, APPROVER_NAME /**최종결재자 이름**/
FROM (
SELECT docid AS APPRID
, '일반' AS docFlag
, oridocid
, docttl AS TITLE
, SUBSTRING(reportdt, 0,8) AS DRAFTDATE
, SUBSTRING(reportdt, 9,8) AS DRAFTTIME
, (SELECT codename FROM bms_code WHERE codeid = state) AS DOCUMENT_STATUS
, (SELECT LOGINID FROM COM_USERINFO WHERE USERID = authorid) AS DRAFTERID
, authorname AS DRAFTERNAME
, authordeptid AS DRAFTDEPTID
, authordeptname AS DRAFTDEPTNAME
, DECODE(objid, 'U000000000000000000001', '1', '', '2') AS AUDITTYPE
, DECODE(attachseq, 0, 'N', 'Y') AS ATTACHFLAG
, CASE WHEN (SELECT COUNT(*) FROM BMS_DCT_OPINION BDO WHERE BDO.opinion != '' AND BDO.docid = oridocid) > 0 THEN '1' ELSE '0' END AS ISOPINION
, DECODE(protectflag, 'N', '', 'Y', '1') AS ISEDIT
, emergency AS URGENTFLAG
, DECODE(rmsmoveflag, 'N', '', 'Y', docttl) AS AARSFILE
, (SELECT LOGINID FROM COM_USERINFO WHERE USERID = lastauthorid) AS APPROVER_ID
, lastauthorname AS APPROVER_NAME
, state
, authorid
, authordutyname
, authordeptnamedesc
, docnoseq
, paperflag
, maindocflag
, attachseq
, enddt
, updt
, open
, (SELECT codename FROM bms_code WHERE codeid = open) AS open_name
, openbasis
, openreason
, delflag
, readrangetype
, (SELECT codename FROM bms_code WHERE codeid = readrangetype) AS readrange_name
, moveflag
, enfgubun
, drmgubun
, protectflag
, docfrom
, typeflag
, tempflag
, gubun
, (SELECT codename FROM bms_code WHERE codeid = gubun) AS gubun_name
, apprdocnum
, relaydocid
, recvdeptid
, actseq
, recvdt
, setcardflag
, distributeflag
, systemtype
, convbodyflag
, firstreadid
, distributedt
, relaytype
, distributedocseq
, pathorder
, asktype
, dutyname
, objid
, objname
, deptid
, deptname
, indt
, inid
, actdt
, starred
, readdt
, objtype
, (SELECT codename FROM bms_code WHERE codeid = objtype) AS objtype_name
, pathstate
, (SELECT codename FROM bms_code WHERE codeid = pathstate) AS pathstate_name
, acttype
, actergubun
, deptnamedesc
, rcvflag
, USE_YN
, editortype
, ispublicdoc
, ispublicdoctemp
, authororgid
, authororgname
, replyreqflag
, recdeptname
, authorinfo
, senddeptname
, sendorgid
, recvdisplayname
, pjtcardname
, openlistflag
, mobilegubun
, rmsmoveflag
, recvminwondisplayname
, lastauthorid
, partflag
, '' AS AARSID
FROM (
SELECT
docid
, '일반문서' AS docFlag
, oridocid
, (DECODE(autosend_chk_val, 1, '[auto]', '') || docttl) AS docttl
, reportdt
, state
, authorid
, authorname
, authordutyname
, authordeptid
, authordeptname
, authordeptnamedesc
, docnoseq
, paperflag
, maindocflag
, attachseq
, enddt
, updt
, open
, openbasis
, openreason
, delflag
, readrangetype
, moveflag
, enfgubun
, drmgubun
, protectflag
, docfrom
, typeflag
, emergency
, tempflag
, gubun
, apprdocnum
, relaydocid
, recvdeptid
, actseq
, recvdt
, setcardflag
, distributeflag
, systemtype
, convbodyflag
, firstreadid
, distributedt
, relaytype
, distributedocseq
, '' AS pathorder
, '' AS asktype
, '' AS dutyname
, '' AS objid
, '' AS objname
, '' AS deptid
, '' AS deptname
, '' AS indt
, '' AS inid
, '' AS actdt
, '' AS starred
, '' AS readdt
, '' AS objtype
, '' AS pathstate
, '' AS acttype
, actergubun
, '' AS deptnamedesc
, 'P' AS rcvflag
, 'Y' AS USE_YN
, '' AS editortype
, ispublicdoc
, '' AS ispublicdoctemp
, authororgid
, authororgname
, 'N' AS replyreqflag
, recdeptname
, authorinfo
, senddeptname
, sendorgid
, recvdisplayname
, pjtcardname
, openlistflag
, mobilegubun
, rmsmoveflag
, recvminwondisplayname
, lastauthorid
, lastauthorname
, partflag
FROM (SELECT T01.*
, (SELECT GROUP_CONCAT ( DECODE(TRIM(TT1.ZIPCODE || TT1.ADDRESS),'','','(' || TT1.ZIPCODE || ') ' || TT1.ADDRESS))
FROM (SELECT T1.ZIPCODE, T1.ADDRESS
FROM BMS_DCT_APPR_RECV T1
WHERE T1.DOCID = T01.docid
AND T1.TEMPFLAG = 'N'
AND T1.ZIPCODE <> ''
AND T1.ADDRESS <> ''
ORDER BY T1.SEQ ) TT1) AS recvminwondisplayname
, (NVL((SELECT GROUP_CONCAT(TT1.DEPTNAME || DECODE(TT1.REFDEPTNAME, NULL, '', '', '', '(' || TT1.REFDEPTNAME || ')'))
FROM (SELECT T1.DEPTNAME, T1.REFDEPTNAME
FROM BMS_DCT_APPR_RECV T1
WHERE T1.DOCID = T01.docid
AND T1.TEMPFLAG = 'N'
ORDER BY T1.SEQ LIMIT 0, 3 ) TT1) ,'')) || ( NVL((SELECT DECODE(T1.DEPTNAME || DECODE(T1.REFDEPTNAME, NULL, '', '', '', '(' || T1.REFDEPTNAME || ')'), NULL, '', '...')
FROM BMS_DCT_APPR_RECV T1
WHERE T1.DOCID = T01.docid
AND T1.TEMPFLAG = 'N'
ORDER BY T1.SEQ LIMIT 3, 1) ,'')) AS recdeptname
, CASE WHEN rcvflag = 'R' THEN (SELECT acterid || CHR(8) || actername || CHR(8) || dutyname || CHR(8) || deptid || CHR(8) || deptname || CHR(8) || deptnamedesc
FROM bms_dct_enf_path b
WHERE b.enfdocid = T01.docid
AND b.actergubun = 'EPR03' LIMIT 1 )
END AS authorinfo
, (SELECT CASE WHEN sendorgname = senderdeptname THEN senderdeptname
WHEN senderdeptname IS NULL THEN sendorgname
ELSE sendorgname || CHR(8) || senderdeptname
END
FROM bms_dct_doc_sendinfo b
WHERE b.docid = T01.oridocid
AND b.tempflag = 'N' LIMIT 1 ) AS senddeptname
, ( SELECT sendorgid
FROM bms_dct_doc_sendinfo b
WHERE b.docid = T01.oridocid
AND b.tempflag = 'N' LIMIT 1 ) AS sendorgid
, (SELECT i.recvdisplayname
FROM bms_dct_doc_sendinfo i
WHERE i.docGubun = 'A'
AND i.tempflag = 'N'
AND i.docid = T01.docid ) AS recvdisplayname
, ( '' ) AS pjtcardname
, DECODE(T02.docid, NULL, 0, 1) AS autosend_chk_val
FROM (SELECT c.*
FROM ( SELECT docid
, oridocid
, docttl
, reportdt
, state
, authorid
, authorname
, authordutyname
, authordeptid
, authordeptname
, authordeptnamedesc
, docnoseq
, paperflag
, maindocflag
, attachseq
, enddt
, updt
, "open"
, openbasis
, openreason
, delflag
, readrangetype
, moveflag
, enfgubun
, drmgubun
, protectflag
, docfrom
, typeflag
, emergency
, tempflag
, gubun
, lastauthorid
, lastauthorname
, partflag
, '' AS apprdocnum
, '' AS relaydocid
, '' AS recvdeptid
, '0' AS actseq
, '' AS recvdt
, '' AS setcardflag
, '' AS distributeflag
, '' AS systemtype
, '' AS convbodyflag
, '' AS firstreadid
, '' AS distributedt
, '' AS relaytype
, '0' AS distributedocseq
, 'M' AS rcvflag
, '' AS ispublicdoc
, '' AS authororgid
, '' AS authororgname
, a.openlistflag
, 'N' AS mobilegubun
,'' AS actergubun
, rmsmoveflag
, 'Y' AS USE_YN
FROM bms_dct_mtg_rdoc a
WHERE a.moveflag = 'N'
AND a.delflag = 'N'
AND a.batchseq = 0
AND a.docnoseq > 0
UNION ALL
SELECT c.*, 'Y' AS USE_YN
FROM (SELECT docid
, oridocid
, docttl
, reportdt
, state
, authorid, authorname, authordutyname, authordeptid, authordeptname
, authordeptnamedesc, docnoseq, paperflag, maindocflag, attachseq
, enddt, updt, "open", openbasis, openreason
, delflag, readrangetype, moveflag, enfgubun, drmgubun
, protectflag, docfrom, typeflag, emergency, tempflag
, gubun, lastauthorid, lastauthorname, partflag, authordeptname || '-' || docnoseq AS apprdocnum
, '' AS relaydocid, '' AS recvdeptid, 0 AS actseq, '' AS recvdt, '' AS setcardflag , '' AS distributeflag
, '' AS systemtype, '' AS convbodyflag, '' AS firstreadid, '' AS distributedt , '' AS relaytype
, '0' AS distributedocseq, 'P' AS rcvflag, a.ispublicdoc, a.authororgid, a.authororgname
, a.openlistflag , nvl(a.mobilegubun , 'N') AS mobilegubun, '' AS actergubun, rmsmoveflag
FROM bms_dct_rdoc a
WHERE a.moveflag = 'N'
AND a.delflag = 'N'
AND a.viewflag = 'Y'
AND a.batchseq = 0
AND 'DCST5' = (SELECT state FROM bms_dct_path WHERE docid = a.docid AND objid != '' ORDER BY pathorder DESC LIMIT 1)
AND a.docnoseq > 0
/**ORDER BY a.reportdt DESC, a.docnoseq DESC**/ ) c
UNION ALL
SELECT c.*
, '' AS actergubun
, 'Y' AS USE_YN
FROM (SELECT docid, oridocid, docttl, reportdt, state
, authorid, authorname, authordutyname, authordeptid, authordeptname
, authordeptnamedesc, docnoseq, paperflag, maindocflag, attachseq
, enddt, updt, "open", openbasis, openreason
, delflag, readrangetype, moveflag, enfgubun, drmgubun
, protectflag, docfrom, typeflag, emergency, tempflag
, gubun, lastauthorid, lastauthorname, partflag, authordeptname || '-' || docnoseq AS apprdocnum
, '' AS relaydocid, '' AS recvdeptid, 0 AS actseq, '' AS recvdt, '' AS setcardflag
, '' AS distributeflag, '' AS systemtype, '' AS convbodyflag, '' AS firstreadid, '' AS distributedt
, '' AS relaytype, 0 AS distributedocseq, 'P' AS rcvflag, a.ispublicdoc, a.authororgid
, a.authororgname, a.openlistflag , nvl(a.mobilegubun,'N') AS mobilegubun, rmsmoveflag
FROM bms_dct_rdoc a
, (SELECT DISTINCT docid rdocid FROM bms_dct_map_dept a WHERE docgubun = 'A' ) da
WHERE da.rdocid = a.docid AND a.moveflag = 'Y' AND a.delflag = 'N'
AND a.viewflag = 'Y'
AND a.batchseq = 0
AND a.docnoseq > 0
/**ORDER BY a.reportdt DESC, a.docnoseq DESC**/) c
UNION ALL
SELECT c.docid, oridocid, docttl, c.reportdt, c.state
, authorid, authorname, authordutyname, authordeptid, authordeptname
, authordeptnamedesc, docnoseq, paperflag, maindocflag, attachseq
, enddt, updt, "open", openbasis, openreason
, delflag, readrangetype, moveflag, enfgubun, drmgubun
, protectflag, '' AS docfrom, '' AS typeflag, 'N' AS emergency, 'N' AS tempflag
, '' AS gubun, b.acterid AS lastauthorid, b.actername AS lastauthorname, partflag, apprdocnum
, relaydocid, recvdeptid, c.actseq, recvdt, setcardflag
, distributeflag, systemtype, convbodyflag, firstreadid, distributedt
, relaytype, distributedocseq, 'R' AS rcvflag, '' AS ispublicdoc, '' AS authororgid
, '' AS authororgname, openlistflag , 'N' AS mobilegubun , b.actergubun AS actergubun , rmsmoveflag
, 'Y' AS USE_YN
FROM ( SELECT a.enfdocid AS docid, a.docid AS oridocid, a.doctitle AS docttl, a.acceptdt AS reportdt, a.docstatus AS state
, a.acceptid AS authorid, a.acceptname AS authorname, a.acceptdutyname AS authordutyname, a.acceptdeptid AS authordeptid, a.acceptdeptname AS authordeptname
, a.acceptdeptnamedesc AS authordeptnamedesc , a.acceptdocseq AS docnoseq , a.paperdocflag AS paperflag
, CASE WHEN a.paperdocflag = 'Y' AND a.paperbodyflag = 'N' THEN 'N'
ELSE 'Y'
END AS maindocflag
, a.attcount AS attachseq , a.recvdt AS enddt, a.updt, a."open", a.openbasis
, a.openreason , a.delflag, a.readrangetype, a.moveflag, a.enfgubun
, a.drmgubun , a.protectflag, a.apprdocnum, a.relaydocid, a.recvdeptid
, a.actseq , a.recvdt, a.setcardflag, a.distributeflag, a.systemtype
, a.convbodyflag , a.firstreadid, a.distributedt, a.relaytype, a.distributedocseq
, a.partflag , a.openlistflag AS openlistflag
, CASE WHEN a.actseq = 0 THEN a.actseq
WHEN a.actseq > 1 AND a.setcardflag = 'N' THEN a.actseq - 1
WHEN a.actseq > 0 AND a.setcardflag = 'Y' THEN a.actseq - 2
ELSE 1
END actseq2
, a.rmsmoveflag
FROM bms_dct_enf_doc a
WHERE a.moveflag = 'N'
AND a.delflag = 'N'
AND a.viewflag = 'Y'
ORDER BY a.acceptdt DESC, a.acceptdocseq DESC ) c
, bms_dct_enf_path b
WHERE c.docid = b.enfdocid(+)
AND c.actseq2 = b.actseq(+)
UNION ALL
SELECT c.docid, oridocid, docttl, c.reportdt, c.state
, authorid, authorname, authordutyname, authordeptid, authordeptname
, authordeptnamedesc, docnoseq, paperflag, maindocflag, attachseq
, enddt, updt, "open", openbasis, openreason
, delflag, readrangetype, moveflag, enfgubun, drmgubun
, protectflag, '' AS docfrom, '' AS typeflag, 'N' AS emergency, 'N' AS tempflag
, '' AS gubun, b.acterid AS lastauthorid, b.actername AS lastauthorname, partflag, apprdocnum
, relaydocid, recvdeptid, c.actseq, recvdt, setcardflag
, distributeflag, systemtype, convbodyflag, firstreadid, distributedt
, relaytype, distributedocseq, 'R' AS rcvflag, '' AS ispublicdoc, '' AS authororgid
, '' AS authororgname , c.openlistflag, 'N' AS mobilegubun, b.actergubun AS actergubun, rmsmoveflag
, 'Y' AS USE_YN
FROM ( SELECT /*+ ORDERED */ a.enfdocid AS docid, a.docid AS oridocid, a.doctitle AS docttl, a.acceptdt AS reportdt, a.docstatus AS state
, a.acceptid AS authorid , a.acceptname AS authorname, a.acceptdutyname AS authordutyname, a.acceptdeptid AS authordeptid, a.acceptdeptname AS authordeptname
, a.acceptdeptnamedesc AS authordeptnamedesc , a.acceptdocseq AS docnoseq , a.paperdocflag AS paperflag
, CASE WHEN a.paperdocflag = 'Y' AND a.paperbodyflag = 'N' THEN 'N'
ELSE 'Y'
END AS maindocflag
, a.attcount AS attachseq , a.recvdt AS enddt, a.updt, a."OPEN", a.openbasis
, a.openreason , a.delflag, a.readrangetype, a.moveflag, a.enfgubun
, a.drmgubun , a.protectflag, a.apprdocnum, a.relaydocid, a.recvdeptid
, a.actseq , a.recvdt, a.setcardflag, a.distributeflag, a.systemtype
, a.convbodyflag , a.firstreadid, a.distributedt, a.relaytype, a.distributedocseq
, a.partflag , a.acceptdocseq, a.acceptdt, a.enfdocid, a.openlistflag AS openlistflag
, CASE WHEN a.actseq = 0 THEN a.actseq
WHEN a.actseq > 1 AND a.setcardflag = 'N' THEN a.actseq - 1
WHEN a.actseq > 0 AND a.setcardflag = 'Y' THEN a.actseq - 2
ELSE 1
END actseq2
, a.rmsmoveflag AS rmsmoveflag
FROM (SELECT DISTINCT docid rdocid
FROM bms_dct_map_dept a
WHERE docgubun = 'E' ) da
, bms_dct_enf_doc a
WHERE da.rdocid = a.enfdocid
AND a.moveflag = 'Y'
AND a.delflag = 'N' ) c
, bms_dct_enf_path b
WHERE c.docid = b.enfdocid(+)
AND c.actseq2 = b.actseq(+) ) c
WHERE c.USE_YN = 'Y' ) T01
, BMS_DCT_AUTOINFO T02
WHERE T01.oridocid = T02.oridocid(+)
AND T01.docid = T02.docid(+))
UNION ALL
SELECT
a.docid
, '감사' AS docFlag
, a.oridocid
, a.docttl
, a.reportdt
, a.state
, a.authorid
, a.authorname
, a.authordutyname
, a.authordeptid
, a.authordeptname
, a.authordeptnamedesc
, a.docnoseq
, a.paperflag
, a.maindocflag
, a.attachseq
, a.enddt
, a.updt
, a."open"
, a.openbasis
, a.openreason
, a.delflag
, a.readrangetype
, a.moveflag
, a.enfgubun
, a.drmgubun
, a.protectflag
, a.docfrom
, a.typeflag
, a.emergency
, a.tempflag
, a.gubun
, '' AS apprdocnum
, '' AS relaydocid
, '' AS recvdeptid
, '0' AS actseq
, '' AS recvdt
, '' AS setcardflag
, '' AS distributeflag
, '' AS systemtype
, '' AS convbodyflag
, '' AS firstreadid
, '' AS distributedt
, '' AS relaytype
, '0' AS distributedocseq
, b.pathorder
, b.asktype
, b.dutyname
, b.objid
, b.objname
, b.deptid
, b.deptname
, b.indt
, b.inid
, b.actapprdt AS actdt
, b.starred
, b.readdt
, b.objtype
, b.state AS pathstate
, b.acttype AS acttype
, '' AS actergubun
, '' AS deptnamedesc
, 'P' AS rcvflag
, 'Y' AS USE_YN
, a.editortype
, '' AS ispublicdoc
, a.ispublicdoc AS ispublicdoctemp
, a.authororgid
, a.authororgname
, 'N' AS replyreqflag
, '' AS recdeptname
, '' AS authorinfo
, '' AS senddeptname
, '' AS sendorgid
, '' AS recvdisplayname
, '' AS pjtcardname
, '' AS openlistflag
, '' AS mobilegubun
, '' AS rmsmoveflag
, '' AS recvminwondisplayname
, '' AS lastauthorid
, '' AS lastauthorname
, '' AS partflag
FROM bms_dct_rdoc a
, bms_dct_path b
WHERE a.docid = b.docid
AND a.delflag = 'N'
AND a.batchseq = 0
AND a.dailyauditflag = 'Y'
AND (a.auditsubmitflag IS NULL OR a.auditsubmitflag != 'Y')
AND ( ( a.state = 'DRDS1' AND a.tempflag = 'N' ) OR (a.state = 'DRDS2') )
AND b.objid = 'U000000000000000000001'
AND b.reportdt > ' '
AND a.PAPERFLAG = 'N'
AND 'T' = ( CASE WHEN (1 = 1) THEN 'T' ELSE 'F' END )
AND 'T' = (CASE WHEN ( 1 = 1 ) THEN 'T' ELSE 'F' END )
)
) AA
;
쿼리에서 '=' 처리 시 이슈가 발생하고 있는 것 같습니다.
작성 쿼리로만으로는 확인이 어렵고 아래 내용을 확인하여 댓글 주시면 테스트 하도록 하겠습니다.
OS : Window7 32bit, Linux 64bit 등
CUBRID Ver. : [cubrid_rel] 수행 결과
CUBRID TOOL Ver. : [도움말]-[버전정보] 확인
응용 환경(API) : java, php, odbc 등 입력
VIEW에서 사용된 table schema : csql> show create table 테이블명; 또는 Cubrid Manager인 경우 왼쪽 테이블트리에서 확인 가능합니다.
감사합니다.