How to add Subquery in where clause in Report

Abhijeet Teli shared this question 2 years ago
Answered

Hi Team,

I have a report which needs a Subquery in where clause. But i am not able to find such option in Filter advanced settings.

Below example query will give me the seconds highest value for Group Transfer. Can you tell me how i can create Report using this condition.


selectMAX(Group_Transfers) from hpd_help_desk

where Group_Transfers < (select MAX(Group_Transfers) from HPD_Help_Desk)


Thanks,

Abhijeet

Comments (6)

photo
1

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

4f3fcbd118e834e1476e79de4b85ba65

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

photo
1

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

photo
1

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:


ac5ec7b7af914e59410674c945468f36

24e01b2a2a2b55349e67716f96fa40bf


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


c06dddee6d9c5f96aad7799edde6a4b9


Regards,

Nathan

photo
1

Hi Abhijeet,


Have you had any luck in resolving this one?


Regards,

Nathan

photo
1

Hey Nathan,

Apologies for the delayed response.

I found what was needed to achieve my usecases.


Thanks for the Help


Thx,

Abhijeet

photo
1

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