Firstly, and stating the obvious - but make sure that you really really need to use free-hand SQL in your chart.

OK - so your sticking with writing free hand SQL. The good news is that you can still reference [[VARIBALES]] and {{Object}} selections in the SQL.

Open

Use a [[VARIABLE]]

You can reference a [[VARIABLE]] anywhere in your SQL. You need to decide whether you need to use '' quotes , i.e. dates / strings, or no quotes if it is a number.

So I want to limit sales in free hand sql based on sales dates.

Open

In the edit chart screen as above - you will see your [[VARIABLE]] placeholder. However, if you save the chart and view the SQL in situ then you will see how the final SQL replacement looks, and exactly what has been executed on the DB.

[[VARIABLE]] replacement can take place in any part of the query.

{{Object}} replacement in SQL.

In my Example I now add three category objects.

You will notice that these do not apply to your free hand SQL elements. Are you still sure you want to use Free Hand SQL??.

The good news is though, that as with [[VARIABLES]] we can apply in the SQL itself.

So if I want region to apply I do the following;

If I look at the executed SQL I will see it is changed when I change the drop list value.

This is Good - but I get a problem if nothing is selected in the droplist.

When -All- is selected in the droplist then the {{object}} reference is not replaced in the SQL.

Fixing All selections

I can fix the problem above by introducing an OR clause. This is how I do this, others may have other strategies for this.

When All is selected the generated SQL looks like

So now the OR part of the query is enacted when All is selected and I see all regions.

Multi-picker

The {{object}} solution above is fine for the above but when you use a Multipicker you will see a list seperated by ::::

But you’ll get no data because the SQL becomes.

Now you may need to google your DB’s sql syntax here - as it could be different to my (MySQL) solution here.

Then the SQL resolves to

I can combine this with a element to cope when the user makes no selection.

In your db - you can find your syntax from the web / your favourite LLM

i.e.

Handling a Between (Dates)

There are two values in a date range

You can find them by select the {{object[0]}} and {{object[1]}}

So Our SQL becomes

When executed it will look like

Note that the date is in a strange format - this is what the browser returns! - so we need to decode our date.

(DATE_FORMAT(nr_sales.SALE_DATE, '%d %M %Y') BETWEEN '{{Sale Date[0]}}' AND '{{Sale Date[1]}}')

NOTE: some browsers return Sep (September) as Sept. So everything is 3 chars except Sept.

So you may even need to

(DATE_FORMAT(nr_sales.SALE_DATE, '%d %M %Y')

BETWEEN REPLACE('{{Sale Date[0]}}','Sept','Sep') AND REPLACE('{{Sale Date[1]}}'),'Sept','Sep')

Putting it all together.

So the SQL that does everything above looks like.