Inline View SQL

Inline SQL View

Inline View is SQL script with programmatic logic inside, so that you can build dynamic SQL for reports control from parameter. With this feature you can apply various and complex business scenarios into SQL.

The SQL executed finally generated is inline SQL which is sub query with joined with other table or other Inline View also.

Creating SQL Inline View

Create new SQL Inline View

1

Click on database instance to work with analysis.

2

Click on Inline View button to register new InlineView query.

3

On the SQL Wizard type your SQL and dynamic operator on the text area.

4

Click on confirm button to save contents.

 

Editing SQL

Click on Inline view and click on Edit button next to item name.

Advanced Tag for InlineView SQL

SQL + Programming logic is fundamental requirements to make your application more flexible and adaptive with various business scenarios. You can implement conditional operation, and precalcuation of parameters, and parameter checking with advanced tags.

InlineView is part of subquery inside of main query. On the cubemodel, you can make join between InlineView / Table or InlineView/InlineView. The final SQL query is generated with subquery on the following format.

For the InlineView syntax, *SELECT * FROM FM_CUSTOMER*.  The report AdHoc query generator generates query with *SELECT * FROM (SELECT * FROM FM_CUSTOMER) n10, FM_PRODUCTS n20 INNER JOIN n10.PRODUCT_ID=n20.PRODUCT_ID*

Dynamic Query

Dynamic Query includes Conditional processing of SQL. Using IF / ELSE / END IF you can make programmatic implementation for your SQL view objects. And also parameters with filter values, and preprocessing is ultimate solution for adopting various business scenarios and make flexible for your analysis query.

Example Dynamic Query

#set cur_month=${cur_month} ? ${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=${cur_month} then '_2' else '_1' end) as mdaccnt,
n11.locname,
n12.dadesc as dadesc,
n12.daseq,
concat(n12.dadesc, '|', case when n10.basedate=${cur_month} then 'CM' else 'PM' end) as gname,
        ${daccnt} 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_NAME}='SHEET 1'
    1=1 AND
    n10.location in (${mlocation}) AND
    n12.daccnt in (${daccnt})
@ELSE
    @IF ${chartmode}
    n12.daccnt in (${daccnt}) and
    n10.basedate in (${cur_month}) AND
    n10.location in (${mlocation})
    @ELSE
    n12.daccnt in ('16', '17', '18', '19', '20') and
    (
n10.basedate in (${cur_month}, ${pre_month})
    )
    @END
     
@END
group by
n10.basedate, n10.datediv, n10.location, mdaccnt,
n11.locname, n12.dadesc, n12.daseq,
gname, mlocation;

 

SQL Query Parts

Preprocessors

On this preprocessor line, you can put calculation or formulas to be calculated using parameters and assign value to parameters.

SQL Query

The main parts of SQL query which includes SELECT, FROM and tables.

We recommend to use fields with alias name to make proper name on 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

 

Conditional Operations

Example of Conditional Operation

#set lparam = helper.systemDate("yyyyMMdd")
 
SELECT *
FROM
fm_customer n10
WHERE
1=1
@IF ${fname}
AND
  n10.fname like '${fname}%'
@ELSEIF ${fullname} = 'NONE'
AND
  N10.fullname = ''
@ELSE
AND
   1=2
@END IF

 

 

Helper Functions

Bellow is Helper functions predefined on package.

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

 

About Preprocessor

Preprocessor Starts with # symbol, and placed anywhere in SQL query. The line starts with # is assumed that preprocessor line. On this preprocessor line, you can put calculation or formulas to be calculated using parameters and assign value to parameters.

 

Preprocessor Syntax

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.