Overview
Version
FineBI Version | Functional Change |
---|---|
6.0 | / |
6.0.16 | Optimized the function of binding parameters to filter components. 1. Parameter display optimization:
2. Parameter binding optimization:
|
Function Description
This document provides SQL writing examples for using SQL dataset parameters in real-time data.
![icon](/core/style/lod.png)
Notes
1. where 1=1 represents that the condition is always true, avoiding errors caused by the redundancy of where if no parameter conditions exist in the subsequent statement.
2. If the to-be-added parameter (can be of text, value, or date type) wrapped by the tags <parameter> and </parameter> is empty, all values are retrieved.
3. In SQL table parameters of data preparations, the month and quarter parameters in the year-month parameters, year-quarter parameters, and year-month interval parameters are of text type, with values ranging from 1 to 12 and 1 to 4 respectively. The values cannot be in the format of 0 plus a single digit (such as 01), otherwise, data cannot be read correctly in widgets of the dashboard.
Microsoft SQL Server
Date Parameters
DateType | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM Internet Access Data WHERE YEAR(Sales Date)='${Year}' | Text |
Year-Month | SELECT * FROM Internet Access Data WHERE YEAR(Sales Date)='${Year}' and month(Sales Date)='${Month}' | Text |
Date | SELECT * FROM Internet Access Data WHERE CAST(Statistical Date as varchar) in ('${Date}') | Date |
Date Interval | SELECT * FROM Internet Access Data WHERE Statistical Date between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM Internet Access Data WHERE format(Statistical Date,'yyyyMM') >=concat('${Start Year}',case when '${Start Month}'>9 then '${Start Month}'else concat(0,'${Start Month}') end ) and format(Statistical Date,'yyyyMM') <=concat('${End Year}',case when '${End Month}'>9 then '${End Month}'else concat(0,'${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM Internet Access Data WHERE CAST(YEAR(Registration Date) as varchar) = '${Year}' and CAST(FLOOR((MONTH(Registration Date)+2)/3) as varchar) = '${Quarter}' | Text |
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM Internet Access Data WHERE Contract Type='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM Internet Access Data WHERE Contract Type in ('${Text Parameter}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM new_dian where 1=1 ${if(len(Region2) == 0,"","and Region in ('" + Region2 + "')")} Or SELECT * FROM new_dian where 1=1 <parameter> and Region in ('${Region}')</parameter> | Text |
Oracle (Similar to Teradata)
Date Parameters
Date Type | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM Internet Access Data WHERE TO_CHAR("Sales Date",'YYYY')='${Year A}' | Text |
Year-Month | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year}' and EXTRACT(MONTH FROM "Sales Date")='${Month}' | Text |
Date | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') in ('${Date Parameter}') | Date |
Date Interval | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYYMM') >=concat('${Start Year}',case when '${Start Month}'>9 then '${Start Month}'else concat(0,'${Start Month}') end ) and TO_CHAR("Sales Date",'YYYYMM') <=concat('${End Year}',case when '${End Month}'>9 then '${End Month}' else concat(0,'${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3)='${Quarter}' | Text
|
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type"='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type" in ('${Text Parameter}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM demo_contract WHERE 1=1 <parameter> and "Contract Type" in ('${Text Parameter}') </parameter> | Text |
Presto
Date Parameters
Date Type | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM new_salesdetail WHERE cast(year(Sales Date) as varchar)='${Year}' | Text |
Year-Month | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year}' and cast(EXTRACT(MONTH FROM "Sales Date") as varchar)='${Month}' | Text |
Date | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') in ('${Date Parameter}') | Date |
Date Interval | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and cast(FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3) as varchar)='${Quarter}' | Text |
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type"='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type" in ('${Text Parameter}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM demo_contract WHERE 1=1 <parameter> and "Contract Type" in ('${Text Parameter}') </parameter> | Text |
PostgreSQL
Date Parameters
Date Type | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year A}' | Text |
Year-Month | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY')='${Year}' and EXTRACT(MONTH FROM "Sales Date")='${Month}' | Text |
Date | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') in ('${Date Parameter}') | Date |
Date Interval | SELECT * FROM new_salesdetail WHERE TO_CHAR("Sales Date",'YYYY-MM-DD') between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM DEMO_CONTRACT WHERE TO_CHAR("Registration Date",'YYYY')='${Year}' and FLOOR((EXTRACT(MONTH FROM "Registration Date")+2)/3) ='${Quarter}' | Text |
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type"='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type" in ('${Text Parameter}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM demo_contract WHERE 1=1 <parameter> and "Contract Type" in ('${Text Parameter}') </parameter> | Text |
APACHE IMPALA
Date Parameters
Date Type | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM xxxx WHERE cast(year(Sales Date) as STRING)='${Year}' | Text |
Year-Month | SELECT * FROM xxxx WHERE cast(year(Sales Date) as STRING)='${Year}' and cast(month(Sales Date) as STRING)='${Month}' | Text |
Date | SELECT * FROM xxxx WHERE Registration Date in ('${Date Parameter}') | Date |
Date Interval | SELECT * FROM xxxx WHERE Registration Date between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM xxxx WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM xxxx WHERE cast(year(Registration Date) as STRING)='${Year}' and cast(truncate((month(Registration Date)+2)/3) as STRING)='${Quarter}'
| Text |
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM demo_contract WHERE Contract Type='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM demo_contract WHERE Contract Type in ('${Text Parameter1}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM demo_contract WHERE 1=1 <parameter> and Contract Type in ('${Text Parameter}') </parameter> | Text |
ClickHouse
Date Parameters
DateType | SQL Example | Recommended Parameter Type |
---|---|---|
Year | SELECT * FROM xxxx WHERE toString(toYear(Registration Date)) ='${Year}' | Text |
Year-Month | SELECT * FROM xxxx WHERE toString(toYear(Registration Date)) ='${Year}' and toString(toMonth(Registration Date)) ='${Month}' | Text |
Date | SELECT * FROM xxxx WHERE Registration Date in ('${Date Parameter}') | Date |
Date Interval | SELECT * FROM xxxx WHERE Registration Date between '${Start Date}' and '${End Date}' | Date |
Year-Month Interval | SELECT * FROM xxxx WHERE TO_CHAR("Registration Date",'YYYY-MM') >=concat('${Start Year}',case when cast('${Start Month}' as tinyint)>9 then '${Start Month}' else concat('0','${Start Month}') end ) and TO_CHAR("Registration Date",'YYYY-MM') <=concat('${End Year}',case when cast('${End Month}' as tinyint)>9 then '${End Month}' else concat('0','${End Month}') end ) | Text |
Year-Quarter | SELECT * FROM xxxx WHERE toString(toYear(Registration Date))='${Year}' and toString(floor((toMonth(Registration Date)+2)/3))='${Quarter}' | Text |
Text Parameters
Text Type | SQL Example | Recommended Parameter Type |
---|---|---|
Texts from which only one value can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type"='${Text Parameter}' | Text |
Texts from which multiple values can be retrieved by the parameter | SELECT * FROM demo_contract WHERE "Contract Type" in ('${Text Parameter}') | Text |
Texts from which all values (when the parameter is empty) and parameter values (when the parameter is not empty) can be retrieved. | SELECT * FROM demo_contract WHERE 1=1 <parameter> and "Contract Type" in ('${Text Parameter}') </parameter> | Text |