1. UX 쿼리는 UI요소를 생성에 사용되므로 Database에 종속적이지 않은 문장만 작성합니다. Select문에서 Top N 구문( SqlServer) , Limit 구문(MySql) 을 쓰면 안됩니다.

   

   VS 실재 Select Qyery에는 MySql의 경우 CONCAT함수를 사용하는 예시입니다.

   

   이렇게 UX쿼리를 작성 후 "필드생성" 버튼을 클릭하면 하단에 UI요소들이 생성되며 실행 미리보기에도 필드들이 생성되어 표시됩니다.

   

   생성된 필드들의 속성들을 변경하면서 자신이 원하는 UI를 완성해 가면 됩니다.

2. 모든 파라미터는 @필드명 으로 작성해야 합니다. ex) WHERE fieldName = @fieldName

3. 필수 입력이 아닌 WHERE 조건 필드들은 아래와 같은 두가지 형태로 작성하시면 됩니다.

   • OR 조건으로 처리하는 방법 WHERE (@title IS NULL OR title LIKE CONCAT('%', @title, '%'))

   • 좌변 = 우변 CASE문을 사용하는 방법 WHERE CASE WHEN @title is null THEN '' ELSE title END = CASE WHEN @title is null THEN '' ELSE @title END

   입력 파라미터로 @title값이 null인 경우 좌변 = 우변 ( '' == '' )으로 모든 값을 조회합니다.

4. 그 외는 Database에 종속적으로 작성하시면 됩니다.

5. SELECT문 작성 MySql의 경우 조건에 LIKE검색을 CASE문 처리 예

   SELECT film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update
   FROM sakila.film
   WHERE CASE WHEN @title is null THEN '' ELSE title END LIKE CASE WHEN @title  is null  THEN '' ELSE CONCAT('%', @title, '%') END
   ORDER BY last_update DESC
   

   위와 같이 일반적인 쿼리를 작성 후 5번의 내용대로 하면 페이징쿼리가 자동완성됩니다.

6. Select문 페이징 적용 쿼리 조회 쿼리문을 작성 후 "페이징쿼리 자동생성"버튼을 이용시 아래와 같은 페이징 쿼리가 작성됩니다. 또한 실행미리보기 하단에 페이저가 표시됩니다.

   • SQLServer

     -- SqlServer
     DECLARE @StartRowIndex INT, @EndRowIndex INT;
     SET @StartRowIndex = ((@PageIndex - 1) * @PageSize) + 1;
     SET @EndRowIndex = @StartRowIndex + @PageSize - 1;
     
     WITH result_set
     AS
     (
         SELECT ROW_NUMBER() OVER(ORDER BY Name ASC ) AS row_no
             , COUNT(*) OVER() AS TotalRowCount
             , BusinessEntityID,AccountNumber,Name,CreditRating,PreferredVendorStatus,ActiveFlag,PurchasingWebServiceURL,ModifiedDate
         FROM(
     		SELECT BusinessEntityID, AccountNumber, Name, CreditRating, PreferredVendorStatus, ActiveFlag, PurchasingWebServiceURL, ModifiedDate
     		FROM Purchasing.Vendor
     		WHERE CASE WHEN @Name is null THEN '' ELSE Name END = CASE WHEN @Name  is null  THEN '' ELSE @Name END
     		
         ) AS A
     )
     SELECT BusinessEntityID,AccountNumber,Name,CreditRating,PreferredVendorStatus,ActiveFlag,PurchasingWebServiceURL,ModifiedDate, row_no, TotalRowCount
     FROM result_set
     WHERE row_no BETWEEN @StartRowIndex AND @EndRowIndex
     ORDER BY Name ASC
     
     

   • MySql

     -- MySql
     SELECT **SQL_CALC_FOUND_ROWS** film_id, title, description, release_year, language_id, original_language_id, rental_duration, rental_rate, length, replacement_cost, rating, special_features, last_update
     FROM sakila.film
     WHERE CASE WHEN @title is null THEN '' ELSE title END LIKE CASE WHEN @title  is null  THEN '' ELSE CONCAT('%', @title, '%') END
     **ORDER BY last_update DESC 
     LIMIT @LimitStart, @PageSize;**
     
     **SELECT FOUND_ROWS();**
     

7. 콤마로 구분된 입력 파라미터 처리 다음과 같은 유틸 성 함수를 사용하여 CheckComboBox나 항목선택(Multi)시 어러개의 코드값을 선택하여 DB파라미터로 넘길 시 콤마로 구분된 데이타를 테이블형식으로 변환하여 사용할 수 있습니다.

   • SqlServer의 경우 아래 프로시져를 DB에 설치합니다.

     SplitCommaToTable.sql

     GET_STRING_SPLIT_TABLE함수를 호출하여 @CategoryTable에 담는 코드가 상단에 구현되어 있습니다.

     -- for ProductSubcategoryID CheckCombox
     DECLARE @CategoryTable TABLE(id VARCHAR(50))
     IF @ProductSubcategoryID is null
     BEGIN
     	INSERT INTO @CategoryTable
     	SELECT ProductSubcategoryID FROM Production.ProductSubcategory
     END
     ELSE
     BEGIN
     	INSERT INTO @CategoryTable
     	SELECT item_name FROM GET_STRING_SPLIT_TABLE(@ProductSubcategoryID,',')
     END
     
     -- paging parameters
     DECLARE @StartRowIndex INT, @EndRowIndex INT;
     SET @StartRowIndex = ((@PageIndex - 1) * @PageSize) + 1;
     SET @EndRowIndex = @StartRowIndex + @PageSize - 1;
     
     WITH Product_set
     AS
     (
         SELECT ROW_NUMBER() OVER(ORDER BY SellStartDate DESC ) AS row_no
             , COUNT(*) OVER() AS TotalRowCount
             , ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost,ListPrice,Size,SizeUnitMeasureCode,WeightUnitMeasureCode,Weight,DaysToManufacture,ProductLine,Class,Style,ProductSubcategoryID,ProductModelID,SellStartDate,SellEndDate,DiscontinuedDate,ModifiedDate,rowguid
     	  , ProductCategoryName
         FROM(
     		SELECT A.ProductID, A.Name, A.ProductNumber, A.MakeFlag, A.FinishedGoodsFlag, A.Color, A.SafetyStockLevel, A.ReorderPoint, A.StandardCost, A.ListPrice, A.Size, A.SizeUnitMeasureCode, A.WeightUnitMeasureCode, A.Weight, A.DaysToManufacture, A.ProductLine, A.Class, A.Style, A.ProductSubcategoryID, A.ProductModelID, A.SellStartDate, A.SellEndDate, A.DiscontinuedDate, A.rowguid, A.ModifiedDate
     		, CONCAT(C.Name, ' - ', B.Name) AS  ProductCategoryName
     		FROM  Production.Product AS A  
     		INNER JOIN Production.ProductSubcategory AS B ON  A.ProductSubcategoryID = B.ProductSubcategoryID
     		INNER JOIN Production.ProductCategory AS C ON  B.ProductCategoryID = C.ProductCategoryID
     		WHERE  CASE WHEN @Name is null THEN '' ELSE A.Name END LIKE CASE WHEN @Name is null THEN '' ELSE '%'+ @Name + '%' END
     		AND CASE WHEN @SellStartDate is null THEN '' ELSE A.SellStartDate END >= CASE WHEN @SellStartDate is null THEN '' ELSE @SellStartDate END
     		AND CASE WHEN @SellEndDate is null THEN '' ELSE A.SellEndDate END <= CASE WHEN @SellEndDate is null THEN '' ELSE @SellEndDate END
     		AND CASE WHEN @MakeFlag is null THEN '' ELSE A.MakeFlag END = CASE WHEN @MakeFlag is null THEN '' ELSE @MakeFlag END
     		AND CASE WHEN @Color is null THEN '' ELSE A.Color END = CASE WHEN @Color is null THEN '' ELSE @Color END
     		AND A.ProductSubcategoryID IN (SELECT id FROM @CategoryTable)
     		
         ) AS A
     )
     SELECT ProductID,Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost,ListPrice,Size,SizeUnitMeasureCode,WeightUnitMeasureCode,Weight,DaysToManufacture,ProductLine,Class,Style,ProductSubcategoryID,ProductModelID,SellStartDate,SellEndDate,DiscontinuedDate,ModifiedDate,rowguid, row_no, TotalRowCount
     , ProductCategoryName
     FROM Product_set
     WHERE row_no BETWEEN @StartRowIndex AND @EndRowIndex
     ORDER BY SellStartDate DESC
     

     더 자세한 구현 내용은 제공되는 제품조회.ux의 샘플을 참고하세요. 조회 조건에 ProductSubcategoryID를 여러개 선택하여 조회하는 경우의 구현 입니다. ( 제품은 여러 카테고리에 소속 됩니다 )

   • MySql의 경우 아래 프로시져를 DB에 설치합니다.

     MySql_Split_Procedure.sql

     설치한 sp_split 프로시저를 사용하는 예입니다. 자세한 구현 내용은 첨부된 간단한 설치테스트 에첨부된 샘플 프로젝트 파일 sakila_dev.zip sakila_dev.zip을 열어 확인하세요.

     
     DROP temporary Table IF exists category_temp_table;
     CREATE TEMPORARY TABLE category_temp_table(
         category_id VARCHAR(50)
     );
     
     CALL sp_split(IFNULL(@category_ids,''), 'category_temp_table');
     
     SELECT SQL_CALC_FOUND_ROWS A.film_id, A.title, A.description, A.release_year, A.language_id, A.original_language_id, A.rental_duration, A.rental_rate, A.length, A.replacement_cost, A.rating, A.special_features, A.last_update
     , GROUP_CONCAT(B.category_id SEPARATOR ', ') AS category_ids
     , GROUP_CONCAT(C.name SEPARATOR ', ') AS category_names
     FROM  film AS A
     INNER JOIN film_category B ON A.film_id = B.film_id 
     INNER JOIN category C ON B.category_id = C.category_id
     WHERE CASE WHEN @title is null THEN '' ELSE title END LIKE CASE WHEN @title  is null  THEN '' ELSE CONCAT('%', @title, '%') END
     AND  CASE WHEN @category_ids IS NULL  THEN '' ELSE B.category_id END IN ( SELECT category_id FROM category_temp_table)
     
     GROUP BY A.film_id
     ORDER BY title ASC 
     LIMIT @LimitStart, @PageSize;
     
     SELECT FOUND_ROWS();
     
     DROP temporary Table IF exists category_temp_table;
     

     IFNULL @category_ids값이 null인경우 처리를 했습니다. WHERE조건에 다음과 같이 IN 문도 CASE를 이용하여 처리 했습니다.

     • AND CASE WHEN IFNULL(@category_ids,'') = '' THEN '' ELSE B.category_id END IN ( SELECT category_id FROM category_temp_table)

     examples..

     WHERE
     			(@RACK_NO ='' OR RACK_NO = @RACK_NO)
     		AND (@FLOOR_NO='' OR FLOOR_NO = @FLOOR_NO)
     		AND (@ODR_NO = '' OR ODR_NO = @ODR_NO)
     		AND (@PALETTE_NO = '' OR PALETTE_NO=@PALETTE_NO)
     		AND (@PKG_TYPE_ID = '' OR B.PKG_TYPE_ID = @PKG_TYPE_ID)
     		AND (@ATTCH_NM = '' OR PROD_NM LIKE '%' + @ATTCH_NM + '%')
     
     SELECT A.id, A.status, A.cycle_id, A.group_id
     ,B.name AS group_name, B.active,B.approver_id -- , B.level, B.super_id
     ,C.year, C.quarter, C.begin_date, C.end_date, C.planning_begin_date, C.planning_end_date, C.feedback_begin_date, C.feedback_end_date, C.status
     FROM okr_dev.okr AS A
     LEFT JOIN okr_dev.group AS B ON A.group_id = B.id
     LEFT JOIN okr_dev.cycle AS C ON A.cycle_id = C.id
     WHERE group_id = @group_id
     AND ( CASE WHEN @end_date is null THEN 1 ELSE C.begin_date  END <= CASE WHEN @end_date  is null  THEN 2 ELSE @end_date  END
     AND CASE WHEN @begin_date is null THEN 2 ELSE C.end_date  END >= CASE WHEN @begin_date  is null  THEN 1 ELSE @begin_date  END )
     

추가적인 문의 UXSql네어버 카페를 이용해 주세요