엄청난 쿼리 초보자 입니다. 조인을 못하겠네요.

Question

WHIT TMP_MBR_LIST_TBL AS( SELECT A.MBR_SEQ, A.MBR_TYPE_CD, A.SCF_CI_VAL, A.MBR_ID, A.SEX_TYPE_CD, A.MBR_BTHDAY, A.MBR_NM, A.MBR_EMAIL_ADR, A.MBR_RTRMMB_YN, A.PUSH_RCV_AGREE_YN, A.ADVM_RCV_AGREE_YN, A.SBSC_TYPE_CD, A.SBSC_TYPE_CD_MM, A.MBR_RTPMMB_DT, A.MBR_SBSC_DT, A.MBR_HP_TEL_NO, A.MBR_CARD_POSS_FLAG, A.SBSC_DVC_TYPE_CD, A.LAST_LOGIN_DT, A.PWD_ERR_TMSCNT, A.PWD_LAST_CHG_DT, A.ACCUM_POINT_QNT, A.BIZR_NO, A.BIZPLC_NM, A.ROAM_USE_YN, CASE WHEN B.SETTL_MTH_CD]] ' ' THEN(CASE WHEN B.SETTL_MTH_CD = '01' THEN '°Çº°' ELSE '¿ùº°' END) ELSE '-' END AS SETTL_MTH_CD FROM ( SELECT A.MBR_SEQ, A.MBR_TYPE_CD, A.SCF_CI_VAL, A.MBR_ID, A.SEX_TYPE_CD, A.MBR_BTHDAY, A.MBR_NM, A.MBR_EMAIL_ADR, A.MBR_RTRMMB_YN, A.PUSH_RCV_AGREE_YN, A.ADVM_RCV_AGREE_YN, A.SBSC_TYPE_CD, ( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE 1 = 1 AND CD_GROUP_ID= 'C0021' AND DTL_CD = B.SBSC_TYPE_CD ) AS SBSC_TYPE_CD_NM ,A.MBR_RTRMMB_DT ,A.MBR_SBSC_DT ,A.MBR_HP_TEL_NO ,( CASE WHEN ( SELECT COUNT(MBR_CARD_ISS_SEQ) FROM MBR_CARD_ISS_TXN WHERE 1 AND MBR_SEQ = A.MBR_SEQ ) ]]> 0 THEN 'Y' ELSE 'N' END ) AS MBR_CARD_POSS_FLAG ,A.SBSC_DVC_TYPE_CD ,A.LAST_LOGIN_DT ,A.PWD_ERR_TMSCNT ,A.PWD_LAST_CHG_DT ,A.ACCUM_POINT_QNT ,A.BIZR_NO ,A.BIZPLC_NM ,A.ROAM_USE_YN FROM MBR_MBR_BAS A, MBR_ACC_BAS B WHERE 1 = 1 AND B.MBR_SEQ = A. MBR_SEQ AND ((A.MBR_RTRMMB_YN IS NULL OR A.MBR_RTRMMB_YN = 'N') AND A.MBR_RTRMMB_DT IS NULL AND A.MBR_TYPE_CD ]]> '02') AND ((B.MBR_RTRMMB_YN IS NULL OR B.MBR_RTRMMB_YN = 'N') AND B.MBR_RTRMMB_DT IS NULL) )A LEFT OUTER JOIN (SELECT MBR_SEQ, APLY_DT, USE_YN, SETTL_MTH_CD FROM MBR_SETTL_MTH_TXN WHERE USE_YN='Y') B ON A.MBR_SEQ= B.MBR_SEQ ) --½ÇÁú ¼öÇà Äõ¸® SELECT BB.MBR_SEQ ,BB.UP_MBR_SEQ ,BB.MBR_TYPE_CD ,( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE 1 = 1 AND CD_GROUP_ID = 'C0061' AND DTL_CD = BB.MBR_TYPE_CD ) AS MBR_TYPE_CD_NM ,( CASE WHEN (BB.MBR_ID IS NOT NULL AND BB.MBR_ID != ' ')THEN ( CASE WHEN(POSITION('@' IN BB.MBR_ID) ]]>0) THEN OVERRAY ( BB.MBR_ID PLACING ( SUBSTR(BB.MBR_ID,0,POSITION('@' IN BB.MBR_ID)-3) || REPEAAT('*',3) ( FROM 1 FOR POSITION('@' IN BB.MBR_ID) -1 ) ELSE ( OVERLAY( BB.MBR_ID PLACING ( SUBSTR(BB.MBR_ID,0,CHAR_LENGTH(BB.MBR_ID)-2 || REPEAT('*'.3) ) FROM 1 ) ) ) END ) ELSE NULL END ) AS PRTCT_MBR_ID ,BB.SEX_TYPE_CD ,( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE 1=1 AND CD_GROUP_ID= 'C0020' AND DTL_CD = BB.SEX_TYPE_CD ) AS SEX_TYPE_CD_NM ,TO_CHAR(BB.MBR_BTHDAY,'YYYY-MM-DD') AD MBR_BTHDAY ,( CASE WHEN (BB.MBR_BTHDAY IS NOT NULL) THEN ( OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'YYY')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'YYYY'))-0 FOR 1)||'.'|| OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'MM')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'MM'))-0 FOR 1)||'.'|| OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'DD')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'DD'))-0 FOR 1) ) ELSE '' END ) AS PRTCT_MBR_BTHDAY ,OVERLAY( BB.MBR_NM PLACING '*' FROM CHAR_LENGTH(BB.MBR_NM) FOR 1 ) AS PRTCT_MBR_NM ,( CASE WHEN (BB.MBR_EMAIL_ADR IS NOT NULL AND BB.MBR_EMAIL_ADR != '')THEN OVERLAY( BB.MBR_EMAIL_ADR PAYCING ( SUBSTR(BB.MBR_EMAIL_ADR,0,POSITION('@' IN BB.MBR_EMAIL_ADR)-3) || REPEAT ('*', 3) ) FROM 1 FOR PISITION('@' IN BB.MBR_EMAIL_ADR)-1 )ELSE NULL END ) AS PRTCT_MR_EMAIL_ADR, BB.MBR_RTRMMB_YN, BB.PUSH_RCV_AGREE_YN, BB.ADVM_RCV_AGREE_YN, TO_CHAR(BB.MBR_RTRMMB_DT,'YYYY-MM-DD') AS MBR_RTRMMB_DT, BB.SBSC_TYPE_CD, BB.SBSC_TYPE_CD_NM, TO_CHAR(BB.MBR_SBSC_DT,'YYYY-MM-DD') AS MBR_SBSC_DT ,( BB.FRST_MBR_HP_TEL_NO || '-' || OVERRAY(BB.SCND_MBR_HP_TEL_NO PLACING '**' FROM CHAR_LENGTH(BB.SCND_MBR_HP_TEL_NO)-1 FOR 2) || '-' || OVERRAY BB.LSAT_MBR_HP_TEL_NO PLACING '*' FROM 1 FOR 1) ) AS PRTCT_MBR_HP_TEL_NO ,BB.MBR_CARD_POSS_FLAG ,BB.SBSC_DVC_TYPE_CD ,( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE 1 = 1 AND CD_GROUP_ID = 'C0060' AND DTL_CD = BB.SBSC_DVC_TYPE_C ) AS SBSC_DVC_TYPE_CD_NM ,TO_CHAR(BB.LAST_LOGIN_DT, 'YYYY-MM-DD HH24:MI:SS') AS LAST_LOGIN_DT ,BB.PWD_ERR_TMSCNT ,TO_CHAR(BB.PWD_LAST_LOGIN_DT, 'YYYY-MM-DD HH24:MI:SS') AS PWD_LAST_CHG_DT ,BB.ACCUM_POINT_QNT ,BB.BIZR_NO ,BB.BIZPLC_NM ,BB.SETTL_MTH_CD ,BB.ROAM_USE_YN FROM ( SELECT AA.RN ,AA.MBR_SEQ ,AA.UP_MBR_SEQ ,AA.MBR_TYPE_SEQ ,AA.SCF_CI_VAL ,AA.MBR_ID ,AA.SEX_TYPE_CD ,AA.MBR_BTHDAY ,AA.MBR_NM ,AA.MBR_EMAIL_ADR ,AA.MBR_RTRMMB_YN ,AA.PUSH_RCV_AGREE_YN ,AA.ADVM_RCV_AGREE_YN ,AA.MBR_RTRMMB_dT ,AA.SBSC_TYPE_CD ,AA.SBSC_TYPE_CD_NM ,AA.MBR_SBSC_DT ,AA.MBR_HP_TEL_NO ,( CASE WHEN (AA.MBR_HP_TEL_NO IS NOT NULL AND AA.MBR_HP_TEL_NO != '')THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM 0 FOR 4) END ) AS FRST_MBR_HP_TEL_NO ,( CASE WHEN(AA.MBR_HP_TEL_NO IS NOT NULL AND AA.MBR_HP_TEL_NO != '') THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM 4 FOR CHAR_LENGTH(AA.MBR_HP_TEL_NO)-7) ELSE NULL END ) AS SCND_MBR_HP_TEL_NO ,( CASE WHEN(AA.MBR_HP_TEL_NO IS NOT NULL AA.MBR_HP_TEL_NO !='') THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM CHAR_LENGTH(AA.MBR_HP_TEL_NO)-3 FOR 4) ELSE NULL END ) AS LAST_MBR_HP_TEL_NO ,AA.MBR_CARD_POSS_FLAG ,AA.SBSC_DVC_TYPE_CD ,AA.LAST_LOGIN_DT ,AA.PWD_ERP_TMSCNT ,AA.PWD_LAST_CHG_DT ,AA.ACCUM_POINT_QNT ,AA.BIZR_NO ,AA.BIZPLC_NM ,AA.SETTL_MTH_CD ,AA.ROAM_USE_YN ( SELECT ROW_NUMBER() OVER (ORDER BY MBR_SBSC_DT DESC) AS RN ,MBR_SEQ ,UP_MBR_SEQ ,MBR_TYPE_CD ,SCF_CI_VAL ,MBR_ID ,SEX_TYPE_CD ,MBR_BTHDAY ,MBR_NM ,MBR_EMAIL_ADR ,MBR_RTRMMB_YN ,PUSH_RCV_AGREE_YN ,ADVM_RCV_AGREE_YN ,MBR_RTRMMB_DT ,SBSC_TYPE_SC ,SBSC_TYPE_CD_NM ,MBR_SBSC_DT ,MBR_HP_TEL_NO ,MBR_CARD_POSS_FLAG ,SBSC_DVC_TYPE_CD ,LAST_LOGIN_DT ,PWD_ERR_TMSCNT ,PWD_LAST_CHG_DT ,ACCUM_POINT_QNT ,BIZR_NO ,BIZPLC_NM ,SETTL_MTH_CD ,ROAM_USE YN FROM TMP_MBR_LIST_TBL WHERE 1=1 AND ((#{srchMBRcardYn,jdbcType=VARCHAR}='t') OR (MBR_CARD_POSS_FLAG =#{srchMbrCardYn,jdbcType=VARCHAR})) AND ((#{srchMBrRgstChnlType,jdbcType=VARCHAR}='t') IS NULL OR #{srchMbrRgstChnlType,jdbcType=VARCHAR} ='')OR (SBSC_TYPE_CD = #{srchMbrRgstChnltype,jdbcType=VARCHAR})) AND ((#{srchStartPgstDate, jdbcType=VARCHAR} IS NULL OR #{srchStartRgstDate,jdbcType=VARCHAR}='') OR(MBR_SBSC_DT TO_TIMESTAMP(#{srchStartRgstDate,jdbcType=VARCHAR}||'00:00:00','YYYYMMDD HH24:MI:SS'))) AND ((#{srchEndPgstDate, jdbcType=VARCHAR} IS NULL OR #{srchEndRgstDate,jdbcType=VARCHAR}='') OR(MBR_SBSC_DT TO_TIMESTAMP(#{srchEndRgstDate,jdbcType=VARCHAR}||'23:59:59','YYYYMMDD HH24:MI:SS'))) AND ((#{srchEmailAddr,jdbcType=VARCHAR} IS NULL OR #{srchEmailAddr,jdbcType=VARCHAR}='')OR (MBR_EMAIL_ADR LIKE '%' ||#{srchEmailAddr,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrHpTelNo,jdbcType=VARCHAR} IS NULL OR #{srchMbrHpTelNo,jdbcType=VARCHAR}='') OR (MBR_HP_TEL_NO LIKE '%' ||#{srchMbrHpTelNo,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrId,jdbcType=VARCHAR} IS NULL OR #{srchMbrId,jdbcType=VARCHAR}='') OR(MBR_ID LIKE '%' ||#{srchMbrId,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrNM,jdbcType=VARCHAR} IS NULL OR #{srchMbrnM,jdbcType=VARCHAR}='') OR(MBR_NM LIKE '%' ||#{srchMbrNm,jdbcType=VARCHAR}||'%')) )AA WHERE (#{endRowIndex,jdbcType=NUMERIC} IS NULL OR AA.RN #{endRowIndex,jdbcType=NUMERIC}) )BB WHERE (#{startRowIndex,jdbcType=NUMRIC} IS NULL OR BB.RN=]]> #{startRowIndex,jdbcType=NUMERIC}) card tableseqcard_num111012220233303쿼리가 긴점 사괴드립니다위에 쿼리에 밑에 카드테이블에 있는 card_num컬럼을 추가하려고 하는데요 카드넘버 컬럼은 case를 써서 01일때는 'N'값을 나머지는 'Y' 문자로 출력되도록 해서 본쿼리에 조인을 하고 싶습니다. 초보자로서 저렇게 긴 쿼리도 처음 만져보는 지라 ㅠㅠ 질문이 다소 어리숙하더라도 많은 충고 부탁드릴께요.

사는게힘듭니다 · Answer

엄청난 쿼리초보자님께서 이렇게 긴 쿼리를 만지실 일이 있나요? ㄷㄷ;;;;

ambition · Answer

쿼리 초보자 치곤 위의 쿼리는 초보자가 다룰만한 양이 아닌 것 같기도..

우왕굳 · Answer

ㅇㅈ... 인라인뷰가 많아서 해당 테이블에 대한 이해도가 있어야지 작성가능할거같은데 쿼리만보고는 힘들거같아요

안드개발자 · Answer

질문이 너무 무책임한거 아닌가요 ㅋㅋㅋㅋ

초신입 · Answer

아 그런가요 ㅠㅠ 하하하하;; 질문이 참...죄송합니다 모두

dogi · Answer

ㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋㅋ

자라선 · Answer

저는 스토어프로시저를 사용 하는 회사에서 신입으로 들어와서매일 쿼리만 다루고있습니다.보통 저런거에 컬럼을 추가하거나 제거 및 버그수정은 가독성을 우선으로 키값하나만 두고 SELECT 컬럼들 싹다 지워버리는거죠.그럼 적어도 이해하기 편해져서 좋아지죠.질문 보니 컬럼추가인거같은데 그럼 SELET 컬럼 싹다 지워버리고 원하시는 컬럼을 붙혀나가세요.그리고 WITH문은 하나로 붙혀주시고 ..

초신입 · Answer

조언 감사합니다!!

JaeDo · Answer

님초보맞음? 이건아닌듯;;

초신입 · Answer

저 초보 맞습니다. 그래서 저 쿼리 보고 더욱더 멘붕에 빠져 있습니다 ㅠㅠ 황당한 질문에 모두 죄송합니다;;#자라선말씀대로 바깥쪽 select문을 다 지우고 제일 안쪽 select문부터 붙여가면서 결과값을 보고 있습니다.with문도 일시작하면서 처음보는 거라 많이 헷갈리긴 하네요; select문이 추가되면서 컬럼 수정이 하나씩 늘어가던데.... 그럼 조인을 맨 처럼 select조건에 걸어야 하는 건가요?

BK · Answer

고수, 초보의 문제가 아니라, 질문 자체가 앞뒤가 안맞습니다.추측하기런 MySql 계혈인데, 괄호도 안맞고, PAYCING, REPEEAT은 도대체 어떤 UDF인지...우선 복붙 부터 제대로 해주시면 안될까요? 대충 여기서 부터 시작하면 될듯 하네요:WITH A AS ( SELECT A.MBR_SEQ, A.MBR_TYPE_CD, A.SCF_CI_VAL, A.MBR_ID, A.SEX_TYPE_CD, A.MBR_BTHDAY, A.MBR_NM, A.MBR_EMAIL_ADR, A.MBR_RTRMMB_YN, A.PUSH_RCV_AGREE_YN, A.ADVM_RCV_AGREE_YN, A.SBSC_TYPE_CD, ( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE CD_GROUP_ID= 'C0021' AND DTL_CD = B.SBSC_TYPE_CD ) AS SBSC_TYPE_CD_NM, A.MBR_RTRMMB_DT, A.MBR_SBSC_DT, A.MBR_HP_TEL_NO, ( CASE WHEN ( SELECT COUNT(MBR_CARD_ISS_SEQ) FROM MBR_CARD_ISS_TXN WHERE MBR_SEQ = A.MBR_SEQ ) ]]> 0 THEN 'Y' ELSE 'N' END ) AS MBR_CARD_POSS_FLAG, A.SBSC_DVC_TYPE_CD, A.LAST_LOGIN_DT, A.PWD_ERR_TMSCNT, A.PWD_LAST_CHG_DT, A.ACCUM_POINT_QNT, A.BIZR_NO, A.BIZPLC_NM, A.ROAM_USE_YN FROM MBR_MBR_BAS A, MBR_ACC_BAS B WHERE B.MBR_SEQ = A. MBR_SEQ AND ( (A.MBR_RTRMMB_YN IS NULL OR A.MBR_RTRMMB_YN = 'N') AND A.MBR_RTRMMB_DT IS NULL AND A.MBR_TYPE_CD ]]> '02' ) AND ( (B.MBR_RTRMMB_YN IS NULL OR B.MBR_RTRMMB_YN = 'N') AND B.MBR_RTRMMB_DT IS NULL ) ), B AS ( SELECT MBR_SEQ, APLY_DT, USE_YN, SETTL_MTH_CD FROM MBR_SETTL_MTH_TXN WHERE USE_YN='Y' ), TMP_MBR_LIST_TBL AS ( SELECT A.* CASE WHEN B.SETTL_MTH_CD]] ' ' THEN(CASE WHEN B.SETTL_MTH_CD = '01' THEN '°Çº°' ELSE '¿ùº°' END) ELSE '-' END AS SETTL_MTH_CD FROM A LEFT JOIN B ON A.MBR_SEQ = B.MBR_SEQ ), AA AS ( SELECT ROW_NUMBER() OVER (ORDER BY MBR_SBSC_DT DESC) AS RN, T.* FROM TMP_MBR_LIST_TBL AS T WHERE 1=1 AND ((#{srchMBRcardYn,jdbcType=VARCHAR}='t') OR (MBR_CARD_POSS_FLAG =#{srchMbrCardYn,jdbcType=VARCHAR})) AND ((#{srchMBrRgstChnlType,jdbcType=VARCHAR}='t') IS NULL OR #{srchMbrRgstChnlType,jdbcType=VARCHAR} ='')OR (SBSC_TYPE_CD = #{srchMbrRgstChnltype,jdbcType=VARCHAR})) AND ((#{srchStartPgstDate, jdbcType=VARCHAR} IS NULL OR #{srchStartRgstDate,jdbcType=VARCHAR}='') OR(MBR_SBSC_DT TO_TIMESTAMP(#{srchStartRgstDate,jdbcType=VARCHAR}||'00:00:00','YYYYMMDD HH24:MI:SS'))) AND ((#{srchEndPgstDate, jdbcType=VARCHAR} IS NULL OR #{srchEndRgstDate,jdbcType=VARCHAR}='') OR(MBR_SBSC_DT TO_TIMESTAMP(#{srchEndRgstDate,jdbcType=VARCHAR}||'23:59:59','YYYYMMDD HH24:MI:SS'))) AND ((#{srchEmailAddr,jdbcType=VARCHAR} IS NULL OR #{srchEmailAddr,jdbcType=VARCHAR}='')OR (MBR_EMAIL_ADR LIKE '%' ||#{srchEmailAddr,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrHpTelNo,jdbcType=VARCHAR} IS NULL OR #{srchMbrHpTelNo,jdbcType=VARCHAR}='') OR (MBR_HP_TEL_NO LIKE '%' ||#{srchMbrHpTelNo,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrId,jdbcType=VARCHAR} IS NULL OR #{srchMbrId,jdbcType=VARCHAR}='') OR(MBR_ID LIKE '%' ||#{srchMbrId,jdbcType=VARCHAR}||'%')) AND ((#{srchMbrNM,jdbcType=VARCHAR} IS NULL OR #{srchMbrnM,jdbcType=VARCHAR}='') OR(MBR_NM LIKE '%' ||#{srchMbrNm,jdbcType=VARCHAR}||'%')) ), BB AS ( SELECT AA.RN ,AA.MBR_SEQ ,AA.UP_MBR_SEQ ,AA.MBR_TYPE_SEQ ,AA.SCF_CI_VAL ,AA.MBR_ID ,AA.SEX_TYPE_CD ,AA.MBR_BTHDAY ,AA.MBR_NM ,AA.MBR_EMAIL_ADR ,AA.MBR_RTRMMB_YN ,AA.PUSH_RCV_AGREE_YN ,AA.ADVM_RCV_AGREE_YN ,AA.MBR_RTRMMB_dT ,AA.SBSC_TYPE_CD ,AA.SBSC_TYPE_CD_NM ,AA.MBR_SBSC_DT ,AA.MBR_HP_TEL_NO, ( CASE WHEN (AA.MBR_HP_TEL_NO IS NOT NULL AND AA.MBR_HP_TEL_NO != '') THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM 0 FOR 4) END ) AS FRST_MBR_HP_TEL_NO, ( CASE WHEN(AA.MBR_HP_TEL_NO IS NOT NULL AND AA.MBR_HP_TEL_NO != '') THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM 4 FOR CHAR_LENGTH(AA.MBR_HP_TEL_NO)-7) ELSE NULL END ) AS SCND_MBR_HP_TEL_NO, ( CASE WHEN(AA.MBR_HP_TEL_NO IS NOT NULL AA.MBR_HP_TEL_NO !='') THEN SUBSTRING(AA.MBR_HP_TEL_NO FROM CHAR_LENGTH(AA.MBR_HP_TEL_NO)-3 FOR 4) ELSE NULL END ) AS LAST_MBR_HP_TEL_NO ,AA.MBR_CARD_POSS_FLAG ,AA.SBSC_DVC_TYPE_CD ,AA.LAST_LOGIN_DT ,AA.PWD_ERP_TMSCNT ,AA.PWD_LAST_CHG_DT ,AA.ACCUM_POINT_QNT ,AA.BIZR_NO ,AA.BIZPLC_NM ,AA.SETTL_MTH_CD ,AA.ROAM_USE_YN FROM AA WHERE (#{endRowIndex,jdbcType=NUMERIC} IS NULL OR AA.RN #{endRowIndex,jdbcType=NUMERIC}) ) --½ÇÁú ¼öÇà Äõ¸® SELECT BB.MBR_SEQ, BB.UP_MBR_SEQ, BB.MBR_TYPE_CD, ( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE CD_GROUP_ID = 'C0061' AND DTL_CD = BB.MBR_TYPE_CD ) AS MBR_TYPE_CD_NM, ( CASE WHEN (BB.MBR_ID IS NOT NULL AND BB.MBR_ID != ' ') THEN ( CASE WHEN(POSITION('@' IN BB.MBR_ID) ]]>0) THEN OVERLAY ( BB.MBR_ID PLACING ( SUBSTR(BB.MBR_ID,0,POSITION('@' IN BB.MBR_ID)-3) || REPEAT('*',3) ) FROM 1 FOR POSITION('@' IN BB.MBR_ID) -1 ) ELSE OVERLAY ( BB.MBR_ID PLACING ( SUBSTR(BB.MBR_ID,0,CHAR_LENGTH(BB.MBR_ID) - 2 || REPEAT('*'.3) ) FROM 1 ) ) ) END ) ELSE NULL END ) AS PRTCT_MBR_ID, BB.SEX_TYPE_CD, ( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE CD_GROUP_ID= 'C0020' AND DTL_CD = BB.SEX_TYPE_CD ) AS SEX_TYPE_CD_NM, TO_CHAR(BB.MBR_BTHDAY,'YYYY-MM-DD') AD MBR_BTHDAY, ( CASE WHEN (BB.MBR_BTHDAY IS NOT NULL) THEN ( OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'YYY')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'YYYY'))-0 FOR 1)||'.'|| OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'MM')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'MM'))-0 FOR 1)||'.'|| OVERLAY(TO_CHAR(BB.MBR_BTHDAY,'DD')PLACING '*' from CHAR_LENGTH(TO_CHAR(BB.MBR_BTHDAY,'DD'))-0 FOR 1) ) ELSE '' END ) AS PRTCT_MBR_BTHDAY, OVERLAY(BB.MBR_NM PLACING '*' FROM CHAR_LENGTH(BB.MBR_NM) FOR 1) AS PRTCT_MBR_NM, ( CASE WHEN (BB.MBR_EMAIL_ADR IS NOT NULL AND BB.MBR_EMAIL_ADR != '') THEN OVERLAY ( BB.MBR_EMAIL_ADR PLACING ( SUBSTR(BB.MBR_EMAIL_ADR,0,POSITION('@' IN BB.MBR_EMAIL_ADR)-3) || REPEAT ('*', 3) ) FROM 1 FOR PISITION('@' IN BB.MBR_EMAIL_ADR)-1 ) ELSE NULL END ) AS PRTCT_MR_EMAIL_ADR, BB.MBR_RTRMMB_YN, BB.PUSH_RCV_AGREE_YN, BB.ADVM_RCV_AGREE_YN, TO_CHAR(BB.MBR_RTRMMB_DT,'YYYY-MM-DD') AS MBR_RTRMMB_DT, BB.SBSC_TYPE_CD, BB.SBSC_TYPE_CD_NM, TO_CHAR(BB.MBR_SBSC_DT,'YYYY-MM-DD') AS MBR_SBSC_DT, ( BB.FRST_MBR_HP_TEL_NO || '-' || OVERRAY(BB.SCND_MBR_HP_TEL_NO PLACING '**' FROM CHAR_LENGTH(BB.SCND_MBR_HP_TEL_NO)-1 FOR 2) || '-' || OVERRAY BB.LSAT_MBR_HP_TEL_NO PLACING '*' FROM 1 FOR 1) ) AS PRTCT_MBR_HP_TEL_NO, BB.MBR_CARD_POSS_FLAG, BB.SBSC_DVC_TYPE_CD, ( SELECT DTL_CD_NM FROM COM_CD_DTL WHERE CD_GROUP_ID = 'C0060' AND DTL_CD = BB.SBSC_DVC_TYPE_C ) AS SBSC_DVC_TYPE_CD_NM, TO_CHAR(BB.LAST_LOGIN_DT, 'YYYY-MM-DD HH24:MI:SS') AS LAST_LOGIN_DT, BB.PWD_ERR_TMSCNT, TO_CHAR(BB.PWD_LAST_LOGIN_DT, 'YYYY-MM-DD HH24:MI:SS') AS PWD_LAST_CHG_DT, BB.ACCUM_POINT_QNT, BB.BIZR_NO, BB.BIZPLC_NM, BB.SETTL_MTH_CD, BB.ROAM_USE_YN, C.* FROM BB LEFT JOIN 카드테이블 AS C ON BB.MBR_SEQ = C.MBR_SEQ WHERE (#{startRowIndex,jdbcType=NUMRIC} IS NULL OR BB.RN=]]> #{startRowIndex,jdbcType=NUMERIC})

초신입 · Answer

감사합니다 ㅠㅠ 지식이 얕아서 그런지 질문도 얕은것 같습니다. 조언 감사합니다.

엄청난 쿼리 초보자 입니다. 조인을 못하겠네요.

12개의 답변

Q&A 목록