New Topic

Community Topic Question Report Builder

Eric Hagfors

Yellowfin Support Team

Assigned

Category Report Builder

Product Version 9.0

Reusing user prompts in Freehand SQL

Carine Nkeangnyi shared this question ago

Answered

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?

The same question

Replies (4)

Dean Flinter ●

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;

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

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;

Reply URL

Carine Nkeangnyi ●

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.

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.

URL

Dean Flinter ●

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

URL

Carine Nkeangnyi ●

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..

URL

Dean Flinter ●

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

URL

Carine Nkeangnyi ●

Thanks for your time and suggestions, Dean.

URL

Eric Hagfors ●

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

Reply URL

Eric Hagfors ●

Hi Carine,

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

Thanks,

Eric

Reply URL

Eric Hagfors ●

Hi Carine,

I'm going to go ahead and mark this question as Answered due to inactivity at this time. Feel welcome to reach out in the future.

Thanks,

Eric

Reply URL