Spring Egov 엑셀 다운로드 폼 예시 / Oracle Decode 예시 / Oracle Case When Decode 조합 사용 예시

SELECT ROWNUM																	excelRow
		 , C.* 
		       	 
	  FROM (SELECT A.OT_SEQ																							   					otSeq
				 , (SELECT TAD.NAME FROM T_AD_USER TAD WHERE TAD.USE_YN = 'Y' AND TAD.SEQ = A.RGST_ID)				   					rgstId
				 , TO_CHAR(RGST_DT, 'YYYY.MM.DD')																	   					rgstDt
				 , (SELECT TAD.NAME FROM T_AD_USER TAD WHERE TAD.USE_YN = 'Y' AND TAD.SEQ = A.APPROVER)				   					approver
				 , A.PRJCT_NM																						   					prjctNm
				 , A.APPLY_RSN																				           					applyRsn
				 , A.STATUS																					           					status
				 , A.APPLY_DATE																				           					applyDate
				 , NVL(A.CMT, '')																				  					    cmt
				 , B.AD_SEQ																					      				   		adSeq
				 , B.EMPLY_SEQ    																									    emplySeq  
				 , (SELECT TAD.NAME FROM T_AD_USER TAD WHERE TAD.USE_YN = 'Y' AND '[' || TAD.SEQ || ']' LIKE '%' || B.EMPLY_SEQ || '%') emplyName    
				 , TO_CHAR(B.STA_DATE, 'YYYY.MM.DD')																   					staDate      
				 , TO_CHAR(B.END_DATE, 'YYYY.MM.DD')																   					endDate          
			 	 , NVL(REPLACE(CONT, ' ', ' '), '')															   					cont
			 	 , NVL(TO_CHAR(B.STA_DATE, 'HH'), '')																   					staHour       
			 	 , NVL(TO_CHAR(B.STA_DATE, 'II'), '')																   					staMin  
			 	 , NVL(TO_CHAR(B.END_DATE, 'HH'), '')																   					endHour    
			 	 , NVL(TO_CHAR(B.END_DATE, 'II'), '')																   					endMin
			 	 , CASE WHEN B.REST_HOUR IS NULL AND B.REST_MIN IS NULL
			 	 		THEN '-'     
			 	 		ELSE DECODE(NVL(B.REST_HOUR, ''), '', '', B.REST_HOUR, B.REST_HOUR || '시간') || ' ' || DECODE(NVL(B.REST_MIN, ''), '', '', B.REST_HOUR, B.REST_HOUR || '분') 
			 	   END																								   restTime									
			 	 , CASE WHEN B.TOTAL_HOUR IS NULL AND B.TOTAL_MIN IS NULL  
			 	 		THEN '-'    
			 	 		ELSE DECODE(NVL(B.TOTAL_HOUR, ''), '', '', B.TOTAL_HOUR, B.TOTAL_HOUR || '시간') || '' || DECODE(NVL(B.TOTAL_MIN, ''), '', '', B.TOTAL_MIN, B.TOTAL_MIN || '분') 
			 	   END 																							 	   totalTime
			 	 
			  FROM T_OVER_TIME A  
   LEFT OUTER JOIN T_OVER_ADD B
   			    ON B.USE_YN = 'Y' 
   			   AND B.OT_SEQ = A.OT_SEQ
   			 WHERE A.USE_YN = 'Y'
   			   AND A.OT_SEQ = #{otSeq}) C