Re: Accept Command

stuart_at_sm1thy.fsnet.co.uk (CreativePresence) writes:

> I have the following command where it allows the user to specify an
> particular salesman id (repid [4 DIGIT NUMBER]) or by typing
> "ALL"(CHAR) will display all records, but i also require the user to
> be able to enter a date & if the date matches an order date to bring
> up this record. The order date is on the table called "ord" and is
> called "OrderDate" and the date on the order table is formated as
> "DD-MMM-YY".
> To put it directly, i want one accept command where the user can
> either enter a repid, All or a date.
> I have tried various ways, but with little joy & therefore turning to
> the experts. Any advise or assistance would be greatful. Is this
> possible or would i need to approach it from a diff. angle?
> Set Linesize 150
> Set Pagesize 75
> PROMPT To view a report on sales orders based on a salesman, please
> type his/her "Rep ID" number or type
> PROMPT "ALL" to view all orders by all salemen. To view an order by
> date, enter a valid date.
> Accept UserRequest CHAR PROMPT 'Please enter "RepID, ALL or Date of
> Order.: '
> Set Verify Off
> Select Distinct Cust.RepID, Cust.Name "Customer Name", Ord.OrdID
> "Order ID", Ord.OrderDate "Order Date",
> Item.ItemTot "Item Total", Item.Qty "Quantity", Ord.Total "Order
> Total"
> From Emp, Customer Cust, Item, Ord
> Where ('&USERREQUEST'='ALL' OR instr('&USERREQUEST',
> to_char(Emp.EmpNo))>0)
> and Emp.Empno = Cust.RepID
> and Cust.CustID = Ord.CustID
> and Ord.OrdID = Item.OrdID
> /

I gather the problem you have been getting is when you try to add the date comparison, you get an exception concerning bad date formats whenever the user enters a value which is not in a valid date format?

Personally, I don't like a prompt which can take different kinds of data as its too difficult to verify the user input. However, what you could possibly do is create a pl/sql function which accepts the user input, attempts to convert it to a date and if it succeeds, return that date. If it fails, catch the exception and return some date which you could compare to your date column which is outside any expected date (so that it does not return any records).

There may still be a problem though - I'm not sure how you will determine the difference between the reID and date inputs - currently you use the instr and just look for the employee number in the user input. o, if someone entered the date 17-SEP-2002, this could match employee IDs 17 and 2002. Maybe extend the above idea to accept the user input and have it return some value based on the type of input. E.g 1 = 'ALL', 2=<some id number not a date>, 3= date, then use a decode statement to control what happens in the where clause. It still feels a bit messy to me though.

Possibly a clearer and easier to maintain solution would be to use an additional prompt to indicate what type of input the user was going to provide and then use some sqlplus to determine which sub-program to call based on that input e.g using @ and have 3 different files - one for each type of query.

All just off the top of my head which I must admit is a little foggy after a weekend of pre-holiday celebrations!

Tim

-- 
Tim X.
tcross (at) northnet com au