Parameter in Subquery
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 ?
The same question
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
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
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
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
Replies have been locked on this page!