Hey guys ,
I’ve been trying to manipulate the variable that is passed in the SQL with REPLACE but it seems like an impossible mission.
REPLACE({{date_field}},‘Date(subs Mobile.activation Time)’ , ‘Date(subs Mobile.activation Date)’ )
I know that when you pass relative date variable the variable value changes to Date(some database.some table) and then the date expression.
My goal is to change the table value before the sql runs itself.
The value that is passed trough {{date_field}}
I’m trying to do this in order to use same date field on two different tables in the same query (And avoid implementing two separate variables in the query).
Well, maybe this will help, I’m no SQL expert but I get around. It took me some time to figure out the filters have to go in the WHERE statement!
In one of my custom queries, for example, I’ve set up two filters as Variable type>Number with Default filter widget value set to 0 in the Variable side-panel. Then in the WHERE statement I list them (min & max price) like so:
AND PRICE>=(CASE WHEN {{MNP}} = 0 THEN PRICE ELSE {{MNP}} END)
AND PRICE<=(CASE WHEN {{MXP}} = 0 THEN PRICE ELSE {{MXP}} END)
Where PRICE is a field in my SELECT statement.
So maybe if you do:
SELECT Field1, Field2, DATE, etc.
FROM Table1 INNER JOIN Table2 ON Table1.Field99=Table2.Field99 (don’t use aliases, Metabase doesn’t support them)…
WHERE 1=1 (as I recall I had to open with something stable like that first and keep the relative variables after the "AND"s for it all to play nice together) …
AND DATE={{date_field}}…
GROUP BY this, that, and the other
Would something like that work for you?
1 Like
Thanks for the reply ,
Unfortunately I think you didn’t quite understood me.
the issue is that I have in the same table two date columns which I need to address with only one date field.
My code looks like this :
SELECT np.network_name,port_in.port_in_count,port_out.port_out_count
FROM npg_providers AS np LEFT JOIN
(SELECT port_in_operator, COUNT(*) AS port_in_count
FROM subs_mobile
INNER JOIN subs_mobile_cstm ON subs_mobile.id = subs_mobile_cstm.id_c
WHERE date_port_in >= {{port_date_range_from}} AND date_port_in <= {{port_date_range_to}}
GROUP BY port_in_operator) AS port_in
ON np.provider = port_in.port_in_operator
LEFT JOIN (SELECT port_out_operator, COUNT(*) AS port_out_count
FROM subs_mobile
INNER JOIN subs_mobile_cstm ON subs_mobile.id = subs_mobile_cstm.id_c
WHERE date_port_out >= {{port_date_range_from}} AND date_port_out <={{port_date_range_to}}
GROUP BY port_out_operator) AS port_out
ON np.provider = port_out.port_out_operator
WHERE port_in.port_in_count > 0 OR port_out.port_out_count > 0
And my goal is to avoid using two single date parameters in the query (port_date_range_from & port_date_range_to) and instead use only one relative date field.
I’ve tried to use REPLACE on the field like I explained above but it looks like it does not work.
My apologies, I thought you were trying to do all this in the SELECT statement.
How about splitting the nested sql up into two or more separate ones and passing a variable say from the selected range of one as a single value to the next?
What sql db are you using? That may be helpful too.
