Search

🏷️

BDS Source Queries (Bracket Flags & Examples)

Flags Reference

On BDS Sources Query creation, you have the ability to interpolate certain values during the BDS Order execution by using interpolation with brackets {{ }} inside the query structure

Additionally, we have another feature to extend the functionality of this interpolation by adding a pipe character after the variable name and a flag shown here:

{{FIELD_NAME | TAG_NAME}}

(OUTER) Skip records if no attribute is selected

You may know the existing variable {{WHERE}}, which is used to interpolate all the selected attributes when we execute our BDS Order, E.G.:

// BDS Source Query before execution
SELECT * FROM MYTABLE WHERE {{WHERE}}

// BDS Source Query After Execution (if you don't select any attribute)
SELECT * FROM MYTABLE WHERE 1=1

//// BDS Source Query After Execution (if you select some attributes)
SELECT * FROM MYTABLE WHERE (FIELD1 IN (1,2,3) AND FIELD2 = 'ABC')

As you see, if you don’t select any attribute, the {{WHERE}} variable is replaced by default with 1=1 value which means select all records if no attribute is selected

If you want to change the rule to Skip records if no attribute is selected you need to use the OUTER flag like this:

// BDS Source Query before execution
SELECT * FROM MYTABLE WHERE {{WHERE | OUTER}}
//                                      ^ flag

// BDS Source Query After Execution (if you don't select any attribute)
SELECT * FROM MYTABLE WHERE 1=0

(TEXT, LIST, NUMBER, BOOLEAN) Infer static variable data type

On your BDS Queries you can also interpolate static fields, like this:

// BDS Source Query before execution
SELECT * FROM TABLE WHERE {{WHERE}} LIMIT {{DYNAMIC_LIMIT}}
//                                          ^ A static field to 
//                                            indicate the limit by each segment

// BDS Source Query After Execution (DYNAMIC_LIMIT = 100)
SELECT * FROM TABLE WHERE (FIELD1 IN (1,2,3) AND FIELD2 = 'ABC') LIMIT 100

But when you are writing your BDS Source Query, you first have to pass validation before saving it; this is necessary to check if the syntax is correct and objects exist on your target data source

During this validation, the query is sanitized, and all the interpolated variables are replaced with dummy values

By default, the validation process replaces WHERE variable as 1=0 with static variables with a number 1 value, E.G.:

// BDS Source Query before validation
SELECT * FROM TABLE WHERE {{WHERE}} LIMIT {{DYNAMIC_LIMIT}}

// BDS Source Query sanitized for Validation
SELECT * FROM TABLE WHERE 1=0 LIMIT 1

But sometimes your static variable could not be a Number; it could be a different type of value like a list, text or boolean. and the validation process could fail, E.G.:

// BDS Source Query before validation
SELECT * FROM TABLE WHERE {{WHERE}} AND FIELD1 IN {{STATIC_LIST}}

// BDS Source Query sanitized for Validation 
SELECT * FROM TABLE WHERE 1=0 AND FIELD1 IN 1
//                                          (❌ This is going to fail and 
//                                               not pass validation)

To avoid this problem, you can infer the data type of your values by using the (NUMBER, TEXT, LIST, BOOLEAN)

So this way, the validation process will treat your static variables with the correct data type, E.G.:

// BDS Source Query before validation
SELECT * FROM TABLE WHERE {{WHERE}} AND FIELD1 IN {{STATIC_LIST | LIST}}
//                                                                ^ Flag

// BDS Source Query sanitized for Validation 
SELECT * FROM TABLE WHERE 1=0 AND FIELD1 IN ('1')
//                                          (✅ This is going to pass validation)

Table of dummy values depending on flags:

no flag
1
| NUMBER
1
| TEXT
'1'
| BOOLEAN
true
| LIST
('1')

(BASEQUERY) Wrap BDS Query

When you execute a BDS Order, the BDS Query that you defined is going to be transformed based on the number of segments you have created and the list of attributes you want to match or exclude. The BDS service will transform all your selections into a complete SQL Query that will accomplish your target criteria, E.G.:

BASEQUERY is a flag used to encapsulate your output query; it focuses the BDS Order execution only on internal SQL logic.

In the brackets interpolation, we are going to write our base query; outside of them we are going to write the desired wrapper logic. Here is an example:

As you see, the execution only duplicates the SQL code inside brackets by using the BASEQUERY flag; this is especially useful for certain scenarios where we want to process the output data after ORDER execution.

Recommended Syntax for BASEQUERY

You could copy these query examples and modify as you need it

Counts

SELECT SEGMENT_NAME, BDS_SEG_N, COUNT(R.*) FROM (
																//^ You could change this selector
  {{
	  SELECT * FROM MYTABLE {{SUPPRESSION_LOGIC}} WHERE {{WHERE}} LIMIT {{BDS_SEG_LIMIT}} // -> your base query
	 | BASEQUERY }}

) AS R GROUP BY SEGMENT_NAME, BDS_SEG_N ORDER BY BDS_SEG_N

Exports 

SELECT SEGMENT_NAME, BDS_SEG_N, R.* FROM (
																//^ You could change this selector
	{{ 
		SELECT * FROM MYTABLE {{SUPPRESSION_LOGIC}} WHERE {{WHERE}} LIMIT {{BDS_SEG_LIMIT}} // -> your base query
	| BASEQUERY }} 
	
) AS R ORDER BY BDS_SEG_N

Notes

  • BASEQUERY is going to be deprecated soon by a simpler way to write SQL queries
  • BASEQUERY is required to make suppression work on COUNT executions (This is going to be optional after the above implementation)