동적 그리드, PIVOT쿼리 만들기

Nexacro를 하다보면 그리드에 컬럼이 동적으로 추가되는게 필요 할때가 은근히 자주 있다.

쿼리에서 PIVOT쿼리를 만든것을 동적으로 보여주기 위함인데 이럴때 Nexacro에서 어떻게 처리 해 줄지 알아보자

(굉장히 유용함으로 꼭 숙지하자)

 

 

사전에 준비해야 할 조건 

 

1. 공통코드

WITH COMMON_CD AS (
(SELECT 'A01' AS CODE_GRP_CD, 'apple' AS CODE_CD, '사과' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'pear' AS CODE_CD, '배' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'orange' AS CODE_CD, '오렌지' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'mandarin' AS CODE_CD, '귤' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'watermelon' AS CODE_CD, '수박' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
)
SELECT * FROM COMMON_CD

2. PIVOT쿼리를 짤 테이블 데이터

WITH FUIT_PRICE AS (
(SELECT 'apple' AS FUIT_CD, '사과' AS FRUIT_NM, '1500' AS PRICE FROM DUAL)
UNION
(SELECT 'pear' AS FUIT_CD, '배' AS FRUIT, '2500' AS PRICE FROM DUAL)
UNION
(SELECT 'orange' AS FUIT_CD, '오렌지' AS FRUIT, '2500' AS PRICE FROM DUAL)
UNION
(SELECT 'mandarin' AS FUIT_CD, '귤' AS FRUIT, '500' AS PRICE FROM DUAL)
UNION
(SELECT 'watermelon' AS FUIT_CD, '수박' AS FRUIT, '10000' AS PRICE FROM DUAL)
) 
SELECT * FROM FUIT_PRICE

* 위의 2개의 임시 테이블의 데이터가 임의로 추가, 삭제, 미사용 될 수 있다는 가정하에 동적으로 Nexacro에서 그리드를 증가 시키고 감소 시켜야 한다.

 

3. PIVOT 쿼리 작성

(1) PIVOT IN절에 들어갈 String 문자열 데이터를 만들기

WITH COMMON_CD AS (
(SELECT 'A01' AS CODE_GRP_CD, 'apple' AS CODE_CD, '사과' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'pear' AS CODE_CD, '배' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'orange' AS CODE_CD, '오렌지' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'mandarin' AS CODE_CD, '귤' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
UNION
(SELECT 'A01' AS CODE_GRP_CD, 'watermelon' AS CODE_CD, '수박' AS CODE_NM, 'Y' AS USE_YN FROM DUAL)
)
SELECT LISTAGG(''''||CODE_CD||''' AS "' || CODE_CD || '"', ',') WITHIN GROUP (ORDER BY CODE_CD)  AS PIVOT_IN_STR 
  FROM COMMON_CD  
 WHERE CODE_GRP_CD IN ('A01') 
;

(2) JAVA SERVICE에서 PIVOT_IN_STR을 Map(box)에 담아 PIVOT쿼리에 전달

  물론 위에처럼 쿼리 LISTAGG를 사용하지 않고 JAVA에서 처리 해 줄수도 있다.

List<Box> dsComFruit  = sqlSession.selectList("aa.cc.mapper.comonList.selComFruitList", varBox);
        model.addDataSet("dsComFruit", dsComFrt);
        
        String inStr  = "";
        for (int i = 0; i < dsComFruit.size(); i++) {
            String col = dsComFruit.get(i).getString("CODE_CD");
            
            inStr  += "'"   + col + "' AS "           + col;
            
            if (i != dsPosNo.size() -1) {
                inStr  += ",";
            }
        }
        
        String[] inArr = inStr.split(",");
        varBox.put("PIVOT_IN_STR", inArr);

(3) PIVOT쿼리 작성 (IN절 파라미터를 #{}가 아닌 ${}로 받을것!)

WITH FUIT_PRICE AS (
(SELECT 'apple' AS FUIT_CD, '사과' AS FRUIT_NM, '1500' AS PRICE FROM DUAL)
UNION
(SELECT 'pear' AS FUIT_CD, '배' AS FRUIT, '2500' AS PRICE FROM DUAL)
UNION
(SELECT 'orange' AS FUIT_CD, '오렌지' AS FRUIT, '2500' AS PRICE FROM DUAL)
UNION
(SELECT 'mandarin' AS FUIT_CD, '귤' AS FRUIT, '500' AS PRICE FROM DUAL)
UNION
(SELECT 'watermelon' AS FUIT_CD, '수박' AS FRUIT, '10000' AS PRICE FROM DUAL)
) 
SELECT * 
  FROM (SELECT FUIT_CD 
             , PRICE  
          FROM FUIT_PRICE  
       ) 
PIVOT ( MAX(PRICE) 
       --FOR FUIT_CD IN ('apple' AS "apple",'mandarin' AS "mandarin",'orange' AS "orange",'pear' AS "pear",'watermelon' AS "watermelon") 
       -- JAVA 이용시
       -- <foreach collection="PIVOT_IN_STR" item="item" separator="," >
       --    ${item}
       -- </foreach>
       -- LISTAGG 이용시
        FOR FUIT_CD IN (${PIVOT_IN_STR})
       )

FUIT_PRICE에 공통코드를 JOIN하여 USE_YN이 Y인것만 뽑아갈 수 있다. 물론 공통코드는 화면에서 처리 할 수 있도록 준비되어야 한다.

 

4. Nexacro에서 처리

Nexacro뿐만 아니라 html을 사용하는 jsp에서도 위의 쿼리를 통해 동적으로 데이터를 보여주고 가리는데 사용 할 수있을것이다.

this.fv_initGridFormat;

this.fn_init = function() {	
	this.fv_initGridFormat = this.grd_main.getCurFormatString();
	cmCdList.push({cdGrpId: "A01", obj: this.ds_comonFruit});	// 공통코드 가져오기 (개발된 함수이므로 공통코드는 프로젝트 환경에 맞게 가져오자)
}

/**
 * 조회
 */
this.fn_search = function() {
	this.grd_main.set_enableredraw(false);
	this.grd_main.set_formats(this.fv_initGridFormat);
	this.grd_main.set_enableredraw(true);
	this.ds_Main.clearData();
	this.gfn_transaction({
		svcId: "search"
	  , service: "searchFruit"
	  , method: "search"
	  , inDs: ""
	  , outDs: "ds_Main=ds_Main"
	  , args: args
	});
};



this.fn_searchCallback = function(){
	this.grd_main.set_enableredraw(false);
		for(var i=0, s=this.ds_comonFruit.getRowCount(); i<s; i++) {
			for(var j=0; j<3; j++) {
				var nColIndex   = this.grd_main.appendContentsCol("body");
					this.grd_main.setFormatColProperty(nColIndex, "size", 100);
					this.grd_main.setCellProperty("head", nHeadLeftStIndex, "text", this.ds_comonFruit.getColumn(i, "COLUM_NM"));
 					this.grd_main.setCellProperty("body", nColIndex, "text", "bind:COLUM_ID");
 					this.grd_main.setCellProperty("body", nColIndex, "textAlign", "left");
			}
		}
	this.grd_main.set_enableredraw(true);
}

전역변수 fv_initGridFormat - 그리드의 최초 포맷을 기억하며 조회할때마다 최초의 그리드 모양으로 돌아간다.

- enableredraw 속성을 false 로 설정하면 컴포넌트가 변경되어도 화면에 반영되지 않습니다.

- enableredraw 속성값이 false 에서 true 로 변경되면 화면 다시 그리기가 즉시 수행됩니다.

- appendContentsCol 그리드 마지막에 컬럼을 추가합니다.