Reusing user prompts in Freehand SQL

Carine Nkeangnyi shared this question 27 days ago
In Progress

Is it possible to reuse a user prompt in Freehand SQL?

Context

I have multiple subqueries which are supposed to use the same user prompt. For example

(Select user_id, SUM(field1) FROM users WHERE insert_date <= {end_date} GROUP BY user_id) u1
INNER JOIN 
(select user_id, field2, field3 FROM users WHERE insert_date = {end_date)) u2
ON u1.user_id = u2.user_id
In this case, end_date is a user input . If i use {?} as the user guide recommends, the user gets asked for end_date twice. This makes the filters redundant and gives users the chance of putting in different dates (which messes up the query logic). Is there a way to perhaps name a user prompt, as above, so that it can be reused in queries?

Comments (3)

photo
1

My understanding is that Yellowfin supports multiple statement execution freehand sql for many DBs although not all.

If what you are using is one of them, something like the below might work for you (will likely need to adapt based on your DB's syntax). The idea being that you use a variable to set the parameter and the query engine does the substitution for you. That way, you only need to set it once and so Yellowfin only displays 1 user prompt


SET end_date={?};

(Select user_id, SUM(field1) FROM users WHERE insert_date <= $end_date GROUP BY user_id) u1
INNER JOIN 
(select user_id, field2, field3 FROM users WHERE insert_date = $end_date) u2
ON u1.user_id = u2.user_id;

photo
1

Hey Dean,

Thanks for your prompt reply. My Yellowfin uses an oracle DB and this method doesn't work for me. I get the following error:

/5016f4e448e95b185788761d15efcf40


ERROR: syntax error at or near "DATE"
Position: 23

IP.S don't have access to the DB so creating a stored procedure is not an option.

photo
1

I'm not familiar with Oracle but the error suggests it is the DB giving that back, not Yellowfin itself.

Perhaps try declaring it as a varchar? You then might need to add a TO_DATE or similar to any parts of the query that reference the variable.

You may also need to wrap the parameter substitution character in quotes so '{?}' instead of just {?}

Unfortunately I can't say for sure as we're using one of the unsupported DBs so I've never been able to get this far

photo
1

Hello Dean.

I've tried declaring varchar, varchar2 and Date variables and none of them has worked so far.. Yellowfin doesn't recognize the prompt symbol ({?}) when wrapped with quotes

Any other suggestions? Thanks for your help so far..

photo
1

Hi Carine,

I'm afraid I don't have any other suggestions. Since I am using one of the unsupported DBs (for multiple statement execution) I've never got beyond exploring the idea. I'm also not familiar with Oracle syntax and handling of variables so not much help there either. The only thing I could suggest is to see if there is other ways to set and use variables in Oracle which may be more compatible? Often, variable handling is dependent on the client so perhaps Yellowfin doesn't like this method

Hopefully support can jump in here with more guidance

Sorry I couldn't be of more help


Thanks

Dean

photo
1

Thanks for your time and suggestions, Dean.

photo
photo
1

Hi Carine,

Are you able to confirm this query works outside Yellowfin, using the same JDBC driver and connection parameters? Can you narrow it down to a particular element of the query? I have not seen a"declare" statement used in freehand SQL content before for example, this might be contributing to thie issue. If so I may be able to get a bug to developers.

Thanks,

Eric

photo
1

Hi Carine,

Just wanted to check in to see if you had a chance to review my reply at this time.

Thanks,

Eric