How to Provide where clause condition in Sql Query of a View

Sairamreddy shared this question 8 years ago
Completed

Hi Yellow Fin Team,


How can we specify a where clause condition while creating a View.


If you click on Model Tab of a View, will see all the Tables with there relations, Click on Table settings, we can make use of conditions but this is used only for particular Table not for the entire SQL statement.


Scenario : I have Table with status as Priority 1 (5 Records), Priority 2 (5 Records) and some data with Null (5 Records), The data with null value should be updated to Priority 2.

So when i make count while processing report it should show

Priority 1 (5 Records), Priority 2 (10 Records)

But its not possible directly if i use and Calculated fields then also its not able to sum.


Kindly required your help

Thank You.

Replies (6)

photo
1

Hi Sairamreddy,


Unfortunately I am a bit confused as to what you are looking for here. Would you be interested in scheduling a time to screen-share? If so, please let me know what time works best for you. I am on United States Mountain Time.


Regards,

Nathan

photo
1

Hi Nathan,


Thanks for your response,

I will be available from 9:00 AM to 8:00 PM IST, In between these timings we can have a session or else you can send me any other timing with which you will be comfortable so that i will plan accordingly.


Regards,

Sairam.K

photo
1

Hi Sairam,


I have sent you an invitation for 7:30PM IST tomorrow, please let me know if this does not work for you.


Regards,

Nathan

photo
1

Hi Nathan,


I will be available in this time to have a session with you.


Thanks & Regards

Sairam.K

photo
1

Hi Nathen, Thank you for the Session.


We discussed regarding the issue of Is Null function and is fixed now.

But we required how to apply Where Clause in the Query.


Request you to send us the fix for Out of Memory issue that you have seen with in session.


Regards

photo
1

Hi Sairam,


Glad to hear that! Here is a link to an article on how to adjust the perm gen settings within Yellowfin:


https://www.yellowfinbi.com/resources/forum/yfforum-outofmemoryerror-permgen-space-thread-97443


I will look into the "where clause" issue and get back to you shortly.


Regards,

Nathan

photo
photo
1

Hi Sairam,


Applying a where clause in the view query depends highly on what you are trying to achieve, what exactly would you like to filter on through this clause?


Typically the best way to apply filters over the entire view is to employ access filters which allow you to limit data by what user/client organization is accessing the data. http://wiki.yellowfin.com.au/display/USER73/Access+Filters


Other than this, your best bet will be to apply these filters at the report level.


From my understanding the "null values" and "where clause" are separate issues, can you please elaborate a bit on this?


Regards,

Nathan

photo
1

Hi Sairam,


Have you had any luck in resolving this?


One additional thought here is to employ filter groups, which make the application of common filters far simpler than creating them for each report. Please let me know if there is anything I can do to help here.


Regards,

Nathan

photo
1

Hi Sairam,


I am going to set this ticket to closed for now, but if there is anything else I can do to help here, please just let me know and the case will be re-opened!


Regards,

Nathan

photo
1

Thanks Nathan,


You can proceed towards closure of this topic.

photo
photo
1

For anyone in 2025 looking for the answer to this. The where clause is in the condition on the primary table. For demonstration purposes, if you build a view and then enter a condition on the first table using drag and drop, the condition will populate in the where clause. If the table is a joined table then the condition stays local to the table join.

Virtual table conditions currently drops to the where clause so try to avoid those.

photo
1

Hello Larry

Greetings of the day

Thank you for contacting Yellowfin support. I have noted your response regarding this case. I would like to clarify that when creating a view in Yellowfin, any conditions applied to the primary table will be reflected in the WHERE clause of the SQL statement. Conversely, if conditions are applied to a joined table, they will remain specific to that table join, meaning they will be included in the ON clause of the join.

Understanding this behavior is crucial, as placing conditions in the WHERE clause can alter the nature of the join. For instance, a LEFT JOIN with a condition in the WHERE clause may effectively transform into an INNER JOIN, which might not be the intended result.

I see that you have already submitted a new question ticket to log this as an idea, and our team is currently reviewing it. Additionally, this ticket has been reopened after a considerable time. Please let me know if we can proceed to close this case or if you require any further assistance.

Sincerely,

Siddartha Pathi

Yellowfin Technical Support Engineer

Leave a Comment
 
Attach a file