反馈已提交

网络繁忙

You are viewing 5.1 help doc. More details are displayed in the latest help doc.

Operator and illegal error reporting

  • Recent Updates: May 10, 2022

    • 1. Overview

    1.1 Version

    FineBI VersionJAR PackageFunction Changes
    5.1.112021-04-02Real time data formula verification enhancement
    5.1.12-Enhanced verification of extracted data formula

    1.2 Application scenario

    The function calculation format has explained the calculation composition and basic calculation syntax of the function used in FineBI, but sometimes users will encounter the problem of writing formula errors. You can write the formula correctly according to the prompts.

    1.3 Function introduction

    This article will introduce the syntax of operators and expressions in detail to help users understand FineBI's formula verification scheme and write the required functions correctly and effectively.

    2. Operator syntax

    2.1 +

    Concept"+" operator represent addition when applied to numbers and concatenation when applied to strings.
    Support combination typeExcept date field + date field, any other type combination is supported
    Return result type

    There is a text field in the parameter, and the result is a text field

    Date + value is the date field

    If all are numeric, then it is numeric field

    Illegal prompt: "Does not support the date+date operation", as shown in the following figure:

    47.png

    2.2 -

    Concept-Operators represent subtraction when applied to numbers and negation when applied to expressions.
    Support combination type

    Two numeric type or two date type fields

    Note: numeric field - date field is not supported.

    Return result type

    Date type - date type returns a timestamp.

    Date type - numeric type. The returned result is date type.

    The rest of the returned results are numeric.

    Illegal prompt:

    • "Does not support numeric-date operation", as shown in the following figure:

    48.png

    • Other illegal subtraction: "Text cannot be used as a minus or minus", as shown in the following figure:

    49.png

    2.3 /

    Concept/ Operator represents the division of numeric values
    Support combination type

    Two numeric type parameters

    Return result type

    Value type

    Illegal prompt: "both ends of the operator / must be numeric type", as shown in the following figure:

    50.png

    2.4 *，%，^

    Concept

    The * operator represents the multiplication of numbers. 

    The % operator returns the remainder.

    The ^ operator calculates the specified power of a number

    Support combination type

    Two numeric type parameters

    Return result type

    Value type

    Illegal prompt: "both ends of the operator must be numeric type", as shown in the following figure:

    51.png

    2.5 >,<,<=,>= (compare)

    Concept

    >（greater than）

    <（less than）

    >=（greater than or equal to）

    <=（less than or equal to）

    Support combination type

    Two parameters of the same type

    Return result type

    Boolean type

    Illegal prompt: "the field types at both ends of the operator must be the same", as shown in the following figure:

    52.png

    3.  Expression syntax

    For the function expressions of FineBI, see file: Expression syntax.

    When the user make the following writing mistakes, the error message will appear.

    3.1 Incomplete operator expression

    For example, if the user writes the following expression: ABS(Total residence time)+, the error "Syntax error, missing identifier" will be reported:

    53.png

    3.2 Character error

    For example, you need to add a new column, all of which are assigned as 1. At this time, enter "1. Due to the lack of half of English double quotation marks, the following error "Character error" will appear:

    54.png

    3.3 Syntax error

    Other syntax errors.

    4. Prompt for illegal specific function type

    4.1 Text function

    Function
    		Result typeIllegal prompt

    LEN

    Value

    "LEN: does not meet the parameter requirements of (any type)"

    CHAR

    Text

    "CHAR: does not meet the parameter requirements of (number)"

    CODE

    value

    "CODE: does not meet (text) parameter requirements"

    REPLACE

    Text

    "REPLACE: does not meet the parameter requirements of (any type, any type, any type) or (any type, value, value, any type)"

    STARTWITH

    bool

    "STARTWITH: does not meet the parameter requirements of (any type, any type)"

    ENDWITH

    bool

    "ENDWITH: does not meet the parameter requirements of (any type, any type)"

    EXACT

    bool

    "EXACT: does not meet the parameter requirements of (any type, any type)"

    INDEXOF

    Text

    "INDEXOF: does not meet the parameter requirements of (any type, value)"

    REPEAT

    Text

    "REPEAT: does not meet the parameter requirements of (any type, value)"

    LEFT

    Text

    "LEFT: does not meet the parameter requirements of (any type) or (any type, value)"

    RIGHT

    Text

    "RIGHT: does not meet the parameter requirements of (any type) or (any type, value)"

    TODOUBLE

    Value

    "TODOUBLE: does not meet the parameter requirements of (any type)"

    TOINTEGER

    Value

    "TOINTEGER: does not meet the parameter requirements of (any type)"

    LOWER

    Text

    "LOWER: does not meet the parameter requirements of (text)"

    TRIM

    Text

    "TRIM: does not meet the parameter requirements of (text)"

    UPPER

    Text

    "UPPER: does not meet the parameter requirements of (text)"

    CONCATENATE

    Text

    "CONCATENATE: does not meet the parameter requirements of (any type...)"

    MID

    Text

    "MID: does not meet the parameter requirements of (any type, value, value)"

    REGEXP

    BOOL

    Real-time data: "REGEXP: does not meet the (text, text) parameter requirements"

    Extract data: "REGEXP: does not meet the (text, text) or (text, text, value) parameter requirements"

    FORMAT

    Text

    Real-time data: "FORMAT: does not meet the parameter requirements of (date, text)"

    Extract data: "FORMAT: does not meet the parameter requirements of (any type, text)"

    FIND

    Value

    "FIND: does not meet the parameter requirements of (any type, any type) or (any type, any type, value)"

    NUMTOText"NUMTO: does not meet the parameter requirements of (numerical value) or (numerical value, Boolean)"
    PROPERText"PROPER: does not meet (text) parameter requirements"
    SPLITText"SPLIT: does not meet the parameter requirements of (any type, any type)"
    SUBSTITUTEText"SUBSTITUTE: does not meet the parameter requirements of (any type, any type, any type, [number])"

    4.2 Date function

    FunctionResult type
    		Illegal prompt
    DATEDate"DATE: does not meet the parameter requirements of (value/text, value/text, value/text)"
    DATEDELTADate





    "Does not meet the parameter requirements of (date/text, value)"

    MONTHDELTATime
    YEARDELTADate
    DATEDIFValue"DATEDIF: does not meet the parameter requirements of (date/text, date/text, text)"
    DATESUBDATEValue"DATESUBDATE: does not meet the parameter requirements of (date/text, date/text, text)"
    DATETONUMBERValue"DATETONUMBER: does not meet the (date/text) parameter requirements"
    DAYVALUEValue"DAYVALUE: does not meet the (date/text) parameter requirements"
    DAYSOFMONTHValue


    "Does not meet the (date/text) parameter requirements"

    DAYSOFQUARTERValue
    DAYSOFYEARValue

    Real-time data: "DAYSOFYEAR: does not meet the (date/text) parameter requirements"

    Extract data:  "DAYSOFYEAR: does not meet the (any type...) parameter requirements"

    YEARValue





    "#Function name#: does not meet the parameter requirements of () or (date/text) or (date/text, text)"

    WEEKValue
    DAYValue
    HOURValue
    MINUTEValue
    MONTHValue
    SECONDValue
    TIMETime

    Real-time data: "TIME: does not meet the parameter requirements of (value, value, value)"

    Extract data: "TIME: does not meet the parameter requirements of (value/text, value/text, value/text)"

    TODATE

    		Time

    Real-time data:"TODATE: does not meet the parameter requirements of (any type) or (text, text)"

    Extract data: "TODATE: does not meet the parameter requirements of (any type) or (text, text) or (text, text, text)"

    NOWTime"NOW: does not meet the parameter requirements of ()"
    TODAYTime"TODAY: does not meet the parameter requirements of ()"
    WEEKDATETime"WEEKDATE: does not meet the parameter requirements of (value/text, value/text, value/text, value/text)"
    WEEKDAYValue"WEEKDAY: does not meet the (date/text) parameter requirements"
    DAYS360Value

    "DAYS360: does not meet the parameter requirements of (date/text, date/text, text/value)"

    Note: real-time data does not support this function.

    LUNARText

    "LUNAR: does not meet the parameter requirements of (value/text, value/text, value/text)"

    Note: real-time data does not support this function.

    4.3 Mathematics and trigonometric functions

    Function / formula nameReturn typeIllegal formula prompt
    PIValuePI: does not meet the parameter requirements of (value) or ()"
    INTValue

    Real-time data:"INT: does not meet the parameter requirements of (value)"

    Extract data: "INT: does not meet the parameter requirements of (text/value)"

    CEILING










    Value










    "#Function name# does not meet the parameter requirements of (value)"

    Floor
    SQRT
    LN
    SQRT
    ABS
    ACOS
    ASIN
    ATAN
    LOG10
    SIN
    TAN
    COS
    EXP
    DEGREES
    SIGN
    RADIANS
    RANDValue"RAND: does not meet the parameter requirements of ()"
    MINAny type

    Real-time data:"MIN: does not meet the requirement that all parameters are of the same type"

    Extract data: "MIN: does not meet the parameter requirements of (value...)"

    MAX
    RANDBETWEEN




    Value




    #Function name# does not meet the parameter requirements of (value,value)"

    MOD
    POWER
    ROUND
    ATAN2
    PROMOTION
    LOGValue#Function name# does not meet the parameter requirements of (value,value)"
    TRUNC

    4.4 Logic function

     Function / formula nameReturn type

    Illegal formula prompt

    ANDbool"AND: Parameter type must be boolean or number"
    ORbool"OR: Parameter type must be boolean or number"
    IF

    The first parameter is boolean type or value type (0 or 1). The second and third parameters are of the same type. If there are empty parameters in the two parameters, the non empty parameter type is returned; The two parameter types are different: return text type.

    "IF: the first parameter is not boolean or value"

    "IF: does not meet the parameter requirements of (boolean/number, parameter, parameter)"

    Note: the result type of expression in real-time data must be the same.

    SWITCHThe first non empty parameter type in the return result parameter (SWITCH function, the 2n + 1 parameter is the return parameter n > 0)

    Real-time data:"SWITCH: the result type must be the same"

    Extract data: "SWITCH: has at least 3 parameters"

    ||、&&bool

    "#Symbol#: Only two Boolean or number expressions can be connected"

    4.5 Other functions

    Function / formula nameReturn type
    Illegal formula prompt
    ISNULLbool

    "ISNULL: does not meet the parameter requirements of (any types...)"

    NVL

    Empty value (null) is excluded. If other returned result parameters are of the same type, the parameter type of the first non empty and non empty string in the returned result parameters (NVL function, all parameters are returned parameters)

    		"NVL: does not meet the requirement that all parameters are of the same type"

    4.6 Aggregate function

    Function / formula nameReturn typeIllegal formula prompt
    SUM_AGG




    Value




    "#Function name# does not meet the parameter requirements of (value)"



    AVG_AGG
    VAR_AGG
    MEDIAN_AGG
    STDEV_AGG

    PERCENTILE_AGG

    Note: only supports real-time data.

    		Value

    "PERCENTILE_AGG does not meet the parameter requirements of (value, value)"

    "PERCENTILE_AGG 's second constant value type parameter must be between 0 and 1"

    MAX_AGG

    Same parameter type


    "#Function name# does not meet the parameter requirements of (any type)"

    MIN_AGG

    APPROX_COUNT_AGG

    Note: only supports real-time data.


    Value


    "#Function name# does not meet the parameter requirements of (any type)"

    COUNT_AGG
    COUNTD_AGG

    Illegal error reporting in aggregate function combination scenario:

    Error code

    Meaning

    Examples

    Illegal prompt

    61500002

    		The indicators filtered by details do not conform to the aggregation functionABS(field), the field is filtered in detail"#Function name#: Detailed filtered indicators only support aggregate functions"

    61500003

    		Aggregate and non aggregate parameters cannot be mixedSUM_AGG(field1)+field2"#Function name#: Cannot mix aggregated and non-aggregated parameters"

    61500004

    		Aggregate functions cannot be nestedSUM_AGG(SUM_AGG(field)))"#Function name#: Aggregate functions cannot nest aggregate functions"

    4.7 Quick calculation function

    Function / formula nameReturn typeIllegal formula prompt

    SAME_PERIOD







    Value

    "SAME_PERIOD: does not meet the parameter requirements of (value) or (value, text)"

    "SAME_PERIOD: second constant text type parameter must be "Y", "M", "W" (case insensitive)"

    ACC_SUM

    "ACC_SUM: does not meet the parameter requirements of (value) or (value, value)"

    "ACC_ SUM: second constant value type parameter must be 0,1"

    PREVIOUS_PERIOD"PREVIOUS_PERIOD: does not meet the parameter requirements of (number)"
    PERIOD_ANLS

    "PERIOD_ANLS: does not meet the parameter requirements of (value) or (value, text)"

    "PERIOD_ANLS: second constant text type parameter must be "Y", "M", "W" (case insensitive)"

    PERIOD_TD

    "PERIOD_TD: does not meet the parameter requirements of (value) or (value, text)"

    "PERIOD_TD: does not meet the parameter requirements of (value) or (value, text)"

    RANK_ANLS

    "RANK_ANLS: does not meet the parameter requirements of (value) or (value, value) or (value, text) or (value, value, text)"

    "RANK_ ANLS second constant value type parameter must be 0,1"

    "RANK_ ANLS third constant text type parameter must be "asc", "desc" (case insensitive)"

    TOTAL

    "RANK_ANLS: does not meet the parameter requirements of (value) or (value, value) or (value, text) or (value, value, text)"

    "RANK_ANLS: second constant value type parameter must be 0,1"

    "RANK_ANLS: the third constant text type parameter of total must be "sum", "avg", "min", "max" (case insensitive)"

    General error message:

    Meanings

    Example

    Illegal formula prompt

    Parameter contains non aggregate function or constant, or the first parameter must be an aggregate function"

    		TOTAL(Contract amount)(「Contract amount」is a field)"The parameter in #Function name# is only an aggregate function or constant, and the first parameter must be an aggregate function"

    4.8 Control calculation granularity function

    Function / formula nameReturn typeIllegal formula prompt

    FIXED



    Value



    " #Function name# : Must contain parameter"

    " #Function name# :  the field can not be an aggregate function"

    " #Function name# : The last parameter must be an aggregate function"


    INCLUDE
    EXCLUDE

    Attachment List


    Theme： Advanced Data Analyis
    Already the First
    Already the Last
    • Helpful
    • Not helpful
    • Only read

    售前咨询电话

    400-811-8890转1

    在线技术支持

    在线QQ：800049425

    热线电话：400-811-8890转2

    总裁办24H投诉

    热线电话：173-1278-1526

    文 档反 馈

    鼠标选中内容，快速反馈问题

    鼠标选中存在疑惑的内容，即可快速反馈问题，我们将会跟进处理。

    不再提示

    10s后关闭