-2

I have a view with multiple values. This view is called directly into a dropdown list in my ERP. I would like to know if is it possible to limit the values of this view according to another field in the ERP?

The page that opens the form with the fields calls 3 procedures : before_open, before_close, after_save

I can't find any information on that.

I already tried creating a using the clause Where to limite the values but it didn't work.

//My view

CREATE VIEW Values
AS
SELECT N_values = 1 , Value = 'Working'
UNION ALL
SELECT N_values = 2 , Value = 'Pause'
UNION ALL
SELECT N_values = 3 , Value = 'Home'
UNION ALL 
SELECT N_values = 4 , Value = 'Vacations'
GO

//To limit the values i did this where ID like 1,5,6

IF(field = 'Not at work') SELECT field FROM view WHERE N_values IN(3,4)

I expect the output of the dropdown list to be only [N_values] 3 and 4, but i get the all list.

Francisco Lopes
  • 33
  • 1
  • 5
  • What exactly did you try? This seems easy, `WHERE` should have worked. – George Menoutis May 06 '19 at 13:12
  • You can directly use WHERE condition while selecting records from a view. – mkRabbani May 06 '19 at 13:13
  • 1
    Your goal cannot be achieved without providing some way to link the rows returned in the query to this "other field". I suspect you need to use code in the ERP to filter the contents. How one does that requires expertise in your ERP. – SMor May 06 '19 at 13:45
  • Why even create this view? Why not store this information in a table? – Tab Alleman May 06 '19 at 13:53
  • 1
    What is 'field', please provade a minimal complete verifiable example: https://stackoverflow.com/help/mcve – DDS May 06 '19 at 14:21
  • Changing the view will modify data for all connections, all users opening this page at the same time will see the same values. You need to implement it as a WHERE in the ERP. – Piotr Palka May 06 '19 at 14:48

2 Answers2

0

Are you looking for a where clause?

SELECT field
FROM view
WHERE (@field = 'Not at work' and N_values IN (3, 4)) OR
      (@field <> 'Not at work');
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Why don't you add an additional (calculated column) working

CREATE VIEW Values
AS
SELECT N_values = 1 , Value = 'Working'
UNION ALL
SELECT N_values = 2 , Value = 'Pause'
UNION ALL
SELECT N_values = 3 , Value = 'Home'
UNION ALL 
SELECT N_values = 4 , Value = 'Vacations'
GO


//this view add a 'calculated field' that is true if forking
CREATE VIEW values_extended AS
SELECT *, case when n_values >2 then 'not-working' else 'working' end
FROM values
GO

so you can pick

select n_values, Value
from value_extended
where working like 'not-working'

will give you

3, Home
4, Vacations
DDS
  • 2,340
  • 16
  • 34