Parameter in Subquery

Harish shared this question 4 years ago
Answered

Hi Team,

Example:

SELECT JS.*

FROM JOB_STATUS JS

WHERE JS.STATUS_VID =

(SELECT MAX(STATUS_VID)

FROM JOB_STATUS JS1

WHERE JS.STATUS_ID = JS1.STATUS_ID AND

CONVERT(DATETIME,JS1.EFFECTIVE_DATE,101) <= CONVERT(DATETIME,@prm_to_date, 101)

Note: STATUS_ID & STATUS_VID data like this

ID VID

--- --

1 1

1 2

1 3

2 2

2 3

3 3

3 4

3 5

4 4

Q: How can I use parameter in subquery ?, I need "Job_Status" based on user provided parameter.

Q: Can I use parameter at view level? Does there any session variable in yellowfin BI tool ?

Q: Can I use parameter in virtual table ?

Comments (2)

photo
1

Hi Harish,

1. You can use a parameter in a subquery by replacing the @prm_to_date with {?}

2. No, unfortunately you can only use the {?} in a Freehand SQL Report, not a Freehand SQL View. There is no session variable when using Freehand SQL.

3. No, you can't use a parameter in Virtual Tables.


The only other alternative I can think of (if you really want to use Freehand SQL) is that you could create a Stored Procedure in your database using the SQL code you already have, and then create a Yellowfin View based on that Stored Procedure.


I hope that helps you meet your reporting requirements.

regards,

David

photo
1

Hello David,

Thanks for your prompt feedback !

In Freehand SQL Report, I have multiple parameters in query & could not able to "Link Filter" in FHSQL.

Here is the screenshot.

/fJ+DjlvQgrMAAAAASUVORK5CYII=


Regards

Harish Bhairy

photo
photo
1

Hi Harish,

yes that's correct, the Link To Filter feature isn't supported in Freehand SQL Reports.

Generally speaking, Freehand SQL Reports and Views and Stored Procedure Views can offer less features than Drag and Drop Reports and Views.

And it is for this reason that in fact we would mostly recommend Drag and Drop Reports and Views instead of Freehand SQL Reports and Views. The Freehand SQL option is there mainly for if you have a wealth of pre-existing Freehand SQL code from another system that you would like to transfer over to Yellowfin.


regards,

David