SQL 인라인뷰

SQL 인라인뷰

인라인 뷰는 프로그래밍 로직을 포함하는 SQL 스크립트 이다. 프로그래밍 로직을 통해서 변수에 따른 다이나믹한 SQL 스크립트를 구성할 수 있으며 이를 통해 다양한 비즈니스 시나리오를 구현 하도록 한다.

최종 실행 SQL 쿼리는 인라인 SQL 뷰 형태로 조합된다. 이는 다른 테이블과의 조인 등 분석 쿼리의 AdHoc 구현을 지원한다.

SQL 인라인 뷰 생성하기

SQL 인라인 뷰 생성하기

 

1

비즈니스 오브젝트를 편집 탭으로 연다.

2

비즈니스 오브젝트 툴바의 인라인 뷰 버튼을 클릭하여 신규 항목을 생성한다.

3

SQL 마법사에서 다이나믹 쿼리를 입력한다.

4

확인 버튼을 클릭하여 서버에 저장한다.
비즈니스 오브젝트 네비게이션 툴바의 리프레시 버튼을 클릭하여 변경 사항을 체크한다.

 

SQL 편집하기

인라인 뷰 항목을 클릭 한 편집 버튼을 클릭한다.

SQL 인라인 뷰에서의 고급 태그

SQL 과 프로그래밍 로직을 결합한 뷰를 통해 유연한 어플리케이션과 다양한 비즈니스 시나리오에 적용할 수 있도록 하는 기능을 제공한다. SQL에 조건절, 변수의 선행 처리 및 고급 태그를 활용해 변수 체크를 구현할 수 있다.

인라인 뷰는 전체 쿼리의 서브 쿼리로 실행 된다. 큐브모델에서 인라인뷰/테이블 혹은 인라인뷰/인라인뷰간에의 상관관계를 정의 할수 있다. 최종 쿼리는 다음과 같은 형태로 생성되어 실행된다.

인라인뷰 정의값, “SELECT * FROM FM_CUSTOMER” 에 대해 리포트 실행 쿼리는 “SELECT … FROM (SELECT * FROM FM_CUSTOMER) n10, FM_PRODUCTS n20 INNER JOIN n10.PRODUCT_ID=n20.PRODUCT_ID” 형태로 생성된다.

다이나믹 쿼리

다이나믹 쿼리는 SQL에 조건적 연산을 포함한다. @IF / @ELSE / @END IF 와 같은 구문을 사용하여 프로그램 가능한 SQL 뷰 개체를 생성할 수 있다. 필터 선택값에 대한 파라미터, 선처리 프로세싱을 통해 다양한 비즈니스 시나리오 및 분석 쿼리의 유연성에 대한 솔류션을 제공한다.

다이나믹 쿼리 예제

#set cur_month= ? : "200501"

#set pre_month = helper.getDate(cur_month, "yyyyMM", "M", -1)

#set daccnt=daccnt ? daccnt : "16";

#set daccnt=daccnt.substring(0, 2);

 

select

n10.basedate, n10.datediv, n10.location,

      concat(n12.daccnt, '|', case when n10.basedate= then '_2' else '_1' end) as mdaccnt,

n11.locname,

n12.dadesc as dadesc,

n12.daseq,

concat(n12.dadesc, '|', case when n10.basedate= then 'CM' else 'PM' end) as gname,

        as prev_month,

      '' as cur_month,

sum(n10.tdvalue) as tdvalue,

      '' as mlocation,

      '' as chartmode

from

d_main n10

inner join d_loc n11 on n10.location=n11.loccode

inner join d_accnt n12 on n10.daccnt=n12.daccnt

where

@IF ='SHEET 1'

    1=1 AND

    n10.location in () AND

    n12.daccnt in ()

@ELSE

    @IF

    n12.daccnt in () and

    n10.basedate in () AND

    n10.location in ()

    @ELSE

    n12.daccnt in ('16', '17', '18', '19', '20') and

    (

n10.basedate in (, )

    )

    @END

     

@END

group by

n10.basedate, n10.datediv, n10.location, mdaccnt,

n11.locname, n12.dadesc, n12.daseq,

gname, mlocation;

 

SQL 쿼리 구조

Preprocessors

선행처리부에 수식 혹은 연산식을 입력할 수 있이며 이를 다시 변수에 할당한다.

SQL 쿼리

SQL 쿼리의 주요 구성은 SELECT, FROM 절을 포함한다.

각 필드값에 별칭을 지정하여 SQL 결과가 적합한 이름 규칙을 가지도록 하는것을 권장한다.

-- NOT RECOMMENDED

SELECT

       COLUMN1,

       COUNT(COLUMN2)

FROM

      fm_customer

GROUP BY

     COLUMN1

 

-- RECOMMENDED (Give alias name to all column with SQL allowed characters. In oracle length of alias should be less then 32 characters (16 character on Asian 2bytes characters)

SELECT

       COLUMN1 COLUMN1,

       COUNT(COLUMN2) COUNT_COLUMN2

FROM

      fm_customer

GROUP BY

조건 연산

Example of Conditional Operation

#set lparam = helper.systemDate("yyyyMMdd")

 

SELECT *

FROM

fm_customer n10

WHERE

1=1

@IF

AND

  n10.fname like '%'

@ELSEIF = 'NONE'

AND

  N10.fullname = ''

@ELSE

AND

   1=2

@END IF

 

 

Helper 함수

아래의 Helper 함수를 통해 복잡한 연산을 단순화 할수 있습니다.

Function

Input

Output

Description

systemDate

String dateFormat

String

Current server time

getDate

String datevalue
String dateformat
String diff
int value

String

Input date
Input date format
Calculation unit (Y:Year, M:Month, D: Day)
Add or substraction value
Transformation or date operations

 

선처리기(Preprocessor) 에 대하여

선처리기는 # 심볼로 시작하며, SQL 쿼리의 어떤 위치에 있어도 적용된다. # 로 시작하는 줄은 선행처리 마크로로 처리된다. 선행처리부에서 변수와 변수간 연산식을 구성하며 연산 후 다시 변수에 할당된다.

 

조건 연산 문법

Operator

Description

Notes

Boolean and

The usual && operator can be used as well as the word and, e.g.

cond1 and cond2

and

cond1 && cond2

are equivalent

Boolean or

The usual || operator can be used as well as the word or, e.g.

cond1 or cond2

and

cond1 || cond2

are equivalent

Boolean not

The usual ! operator can be used as well as the word not, e.g.

!cond1

and

not cond1

are equivalent

Bitwise and

The usual | operator is used, e.g.

33 | 4

, 0010 0001 | 0000 0100 = 0010 0101 = 37.

Bitwise xor

The usual ^ operator is used, e.g.

33 ^ 4

, 0010 0001 ^ 0000 0100 = 0010 0100 = 37.

Bitwise complement

The usual ~ operator is used, e.g.

~33

, ~0010 0001 = 1101 1110 = -34.

Ternary conditional ?:

The usual ternary conditional operator condition ? if_true : if_false operator can be used as well as the abbreviation value ?: if_false which returns the value if its evaluation is defined, non-null and non-false, e.g.

val1 ? val1 : val2

and

val1 ?: val2

are equivalent.

NOTE: The condition will evaluate to false when it refers to an undefined variable or null for all JexlEngine flag combinations. This allows explicit syntactic leniency and treats the condition 'if undefined or null or false' the same way in all cases.

Equality

The usual == operator can be used as well as the abbreviation eq. For example

val1 == val2

and

val1 eq val2

are equivalent.

null is only ever equal to null, that is if you compare null to any non-null value, the result is false. Equality uses the java equals method

Inequality

The usual != operator can be used as well as the abbreviation ne. For example

val1 != val2

and

val1 ne val2

are equivalent.

Less Than

The usual < operator can be used as well as the abbreviation lt. For example

val1 < val2

and

val1 lt val2

are equivalent.

Less Than Or Equal To

The usual <= operator can be used as well as the abbreviation le. For example

val1 <= val2

and

val1 le val2

are equivalent.

Greater Than

The usual > operator can be used as well as the abbreviation gt. For example

val1 > val2

and

val1 gt val2

are equivalent.

Greater Than Or Equal To

The usual >= operator can be used as well as the abbreviation ge. For example

val1 >= val2

and

val1 ge val2

are equivalent.

In or Match=~

The syntactically Perl inspired =~ operator can be used to check that a string matches a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" =~ "abc.* returns true. It also checks whether any collection, set or map (on keys) contains a value or not; in that case, it behaves as an "in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" =~ ["a","b","c","d","e",f"] returns true.

Not-In or Not-Match!~

The syntactically Perl inspired !~ operator can be used to check that a string does not match a regular expression (expressed either a Java String or a java.util.regex.Pattern). For example "abcdef" !~ "abc.* returns false. It also checks whether any collection, set or map (on keys) does not contain a value; in that case, it behaves as "not in" operator. Note that it also applies to arrays as well as "duck-typed" collection, ie classes exposing a "contains" method. "a" !~ ["a","b","c","d","e",f"] returns true.

Addition

The usual + operator is used. For example

val1 + val2

Subtraction

The usual - operator is used. For example <pre> val1 - val2 </pre> | |- |Multiplication |<nowiki>The usual / operator is used, or one can use the div operator. For example

val1 / val2

or

val1 div val2

Modulus (or remainder)

The % operator is used. An alternative is the mod operator. For example

5 mod 2

gives 1 and is equivalent to

5 % 2

Negation

The unary - operator is used. For example<nowiki> <pre> -12 </pre> | |- |Array access |<nowiki>Array elements may be accessed using either square brackets or a dotted numeral, e.g.

arr1[0]

and

arr1.0

are equivalent

HashMap access

Map elements are accessed using square brackets, e.g.

map[0]; map['name']; map[var];

Note that

map['7']

and

map[7]

refer to different elements. Map elements with a numeric key may also be accessed using a dotted numeral, e.g.

map[0]

and

map.0

are equivalent.