How to create a View with filter?

Asked

Viewed 507 times

2

I created the following query in my BD, I need to create a View from this query, the same should return the 3 initial columns realized, meta and accumulated, according to which the select is performing, but the moment I am performing a select in the view I must pass a Where informing the channel code, that part I am unable to create, could help me?

sql-server

  • Can better exemplify Gabriel, what she is returning in the view and which field within the view you will filter ?

  • @badCode today she’s returning the columns A.REALIZADO, A.META e ACUMULADO in that same View I need to pass the field VEN.AD_CODCANALas parameter, this field returns the channel in which it is being invoiced, because to insert in my application this would be the best way.

  • 1

    You need to include in the view return this VEN.AD_CODCANAL to be able to filter, if you don’t have it today you will have to link by Join until you find the VEN.AD_CODCANAL and then you will filter with Where (select * from minhaview Where AD_CODCANAL = 'xxx') got it ?

  • @badCode there is the problem, in my select it is already being filtered, it is repeated three times as you can see above AND VEN1.AD_CODCANAL=1 , AND VEN1.AD_CODCANAL=1 , AND VEN1.AD_CODCANAL=1 , it brings the result of the correct channel, but I want to be able to filter outside the View, because I have more than one channel to display, and it would not be feasible to create a view per channel...

  • So I think it went bad, because there is no way to pass parameter like this, the view is the place for you to save the state of what you need and apply the query on top of the data...

  • 1

    You can turn the display (view) in a user function of type inline table-Valued, which is efficiently executed. See https://answall.com/questions/207629/view-com-par%C3%a2metros-sem-Where-external/

  • 1

    To understand how the expansion of an exhibition takes place (view), vide https://answall.com/questions/224619/select-em-view-gera-subquery/

Show 2 more comments

1 answer

3

One view does not accept parameters as a function or stored procedure, then you can’t change the where dynamically.

You can filter the result of a view from a select if this is no problem (many results for example):

SELECT * 
  FROM [dbo].[VWmarcas]
 WHERE  ...

  • How would I do in this case Ricardo? Because I see that it was still not possible...

  • The ideal is to place as few filters as possible where of the view, and go filtering in the select, your view by what I saw is very specific, for a month/year, she should Chara Vwmarcasjul2018 for example if it is like this

Browser other questions tagged sql-server

You are not signed in. Login or sign up in order to post.