How to add Subquery in where clause in Report

Hi Abhijeet,

This will need to be a custom SQL query filter that is partly user defined.

• Change the operand to less than.
• Select Slider > Custom Query
• Enter a SQL statement along the lines of SELECT 0, MAX(Group_Transfers) from HPD_Help_Desk

This will create a slider between the value of 0 and your maximum group transfers. You can set it to default to the values of your choosing, but the user will still need to click go.

Please let me know if this does not work for you.

Regards,

Nathan

Hi Nathan,This is OK as a workaround for mentioned use case. But this is not what i am looking for i need to know is there any way to write select statement in a where clause, there could be other complex dynamic condition scenarios where without subqueries it cant be done.

If you take an example of existing advanced Filter Date_Field = (Max date or Min Date) which generates perfect example of subquery. is there any way to write similar for other scenarios ?

Thanks,

Abhijeet

Hi Abhijeet,

I am a little confused as to how you envison this working. Yellowfin filters simply apply the desired condition to the underlying SQLs WHERE clause, so by applying a select statement as your custom filter, you are essentially applying a WHERE IN (SELECT ....) to your report.

Alternatively, I was also able to create a Freehand SQL report on top of my configuration database using the following query:

1. SELECT * FROM 73yellowfin20170208.person WHERE IpPerson IN (SELECT IpChild FROM 73yellowfin20170208.iprltshp WHERE EndReasonCode = 'DELETED');

In the screenshots below I have created two reports. The first is a freehand SQL report using the query above, the second is a report built off the Yellowfin configuration database, where I have applied a custom SQL filter (SELECT IpChild FROM 73yellowfin20170208.iprltshp WHERE EndReasonCode = 'DELETED') . Note that both of these return the same 3 users.

Custom filter:

Freehand SQL (using: SELECT * FROM 73yellowfin20170208.person WHERE IpPerson IN (SELECT IpChild FROM 73yellowfin20170208.iprltshp WHERE EndReasonCode = 'DELETED');)

Regards,

Nathan

Hi Abhijeet,

Have you had any luck in resolving this one?

Regards,

Nathan

Hey Nathan,

Apologies for the delayed response.

I found what was needed to achieve my usecases.

Thanks for the Help

Thx,

Abhijeet

Hi Abhijeet,

Glad to hear it. I am going to close this ticket, but if you run into any further issues with this, just respond and the case will be re-opened.

Regards,

Nathan