You can translate the question and the replies:

views exclusion parameters

Hi team, Is it possible to create a denodo view which contain two parameters, but when queries running users cannot input the two paramters together, they can only input value to either one parameter, if input two of them, we want to the error retruned. Thanks.
27-06-2023 02:59:42 -0400

1 Answer

Hi, I made some test and I don't see any available method to do this with view parameters. However you can make use of a Stored Procedure that take in input the two parameters, make some check on their validity and return the result of the view. The error type of course depends on the consuming application, in my example I inserted only a `RAISE EXCEPTION` which stops the interpreter with an error. After that you will need a cursor to return the view. Your code will look like this: ``` ("param1" IN VARCHAR, "param2" IN VARCHAR, out_1 OUT VARCHAR, out_2 OUT VARCHAR, ..) AS ( # procedure variables exception1 EXCEPTION; CURSOR cursorData IS 'SELECT "view_field_1", "view_field_2", .. from test_database.<base_view_name> WHERE view_param1 = '':param1'' and view_param2 = '':param2'''; res cursorData%ROWTYPE; ) BEGIN # parameter check IF param1 IS NOT NULL AND param2 IS NOT NULL THEN RAISE EXCEPTION1; END IF; IF param1 IS NULL AND param2 IS NULL THEN RAISE EXCEPTION1; END IF; [...] # ok, proceed by returning the data OPEN cursorData; LOOP FETCH cursorData INTO res; RETURN ROW ( "out_field_1", "out_field_2", .. ) VALUES ("view_field_1", "view_field_2", ..); EXIT WHEN cursorData%NOTFOUND; END LOOP; CLOSE cursorData; END ``` Please note *:param1* and *:param2* that are the original params of the view. Hope this helps
Denodo Team
28-06-2023 04:30:17 -0400
You must sign in to add an answer. If you do not have an account, you can register here