Re: possible to create a cl on this?

On 6/24/2015 6:09 PM, Hoteltravelfundotcom wrote:

I have created some views, 3 of them which gets the data needed as the
basis for some reports.

The database does not have a category for sales person type.

We have a sp type called a closer. Every so often someone else is moved up
to closer.

I then have to hard code the closers USERID in the first view, then simply
recreate the second and third. Then the reports are fine and have the new
person.

I would like the user to just enter the closer USERID when they get a new
one.

But my understanding is I cannot do this in a CL. I don't want to make a
small file of closers. How can I automate this way when using views.

The SQL way is to remove the hard coded WHERE USERID IN ('A', 'B'...)
and instead JOIN to a file of user IDs which represent your closers:
WHERE USERID IN (SELECT USERID FROM CLOSERIDS)

As for the CL question, if I understand it properly, you want to let an
end-user add to the list of closers (Add only? None ever leave?) by
using a CL program. I run ad-hoc SQL with a generic QMQRY. There's an
example in the Wiki:
http://wiki.midrange.com/index.php/SQL#RUNSQL_command_for_ad-hoc_SQL_statements
You don't need to make a completely generic QMQRY; you could construct
an INSERT INTO CLOSERIDS VALUES (&A) and simply provide the user ID as
the substitution value.

I personally would argue against this; it would be far better to write a
full blown maintenance program that lets someone add, remove or change
(spelling errors do happen) closer IDs.