Parameterized View - Can we open multiple instances using aliases?

I have a parameterized view (called ACCOUNT_PRICESET) which works as advertised.

However, in one of my routines, I open the view and use it update other versions of the same view.

(if it helps to understand, the first view is the Price Rules for a head office and we need to create a copy of that set of rules for each subsidiary account assigned to that head office. Why? Because the subsidiaries are allowed to modify some of the rules but their starting point is what the head office negotiated)

The parameter is simply the Account Reference (PRICESETS.ACCREF=?startit.current_accref is the SQL code). So I open the head office view with the parameter set to the head office account ref. Then I change "startit.current_accref" to one of the subsidiary accounts  and use the following command


expecting a new small view consisting, this time, of the subsidiary's price set records.

Instead, what I'm getting is another copy of the Head Office records, despite the change in parameters.

This is, I think, the first time I've tried opening a parameterized view while there is already one open, so perhaps I'm breaking some rule I don't know about or, perhaps there is a procedure that will accomplish what I need in some other way.

I could, for example, set up an identical view with a different name, just for this purpose, which would be simple enough, but I'd like to understand why I can't do it as above...

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

The view is defined as one set of records in the database. This set of records is identified by the view name, so any change in the view definition must be propagated to all workareas where the view is open under different aliases. (You may open the database as a standard table - USE database.DBC NOUPDATE - and look at its records e.g. in BROWSE.)

It is even possible the database record belonging to the view is locked and then you cannot save changes made in the view definition.

The work around is to define new differently named view as you stated above (which is correct) or you may define the view without parameters and after its opening set filter to appropriate expression.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mjacobs2929Author Commented:
always good to have your own guesses confirmed!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.