This content has been marked as final. Show 4 replies
How do i pass a value to department prompt with value like MK0% or MK% and return with all department data?Exactlly as you say it, because the prompt is of no table edit, you can enter what you want and in order to properly construct your sql you need to enter MK% in the prompt as value.
I created a query with the following sql:
SELECT A.OPRID, A.EMPLID
FROM PS_OPRDEFN2 A
WHERE ( A.OPRID LIKE :1 )
Where :1 is a prompt on PSOPRDEFN with no table edit.
When I run the query the prompt pops up, when I enter V% it returns all OPRIDs starting with V
Thanks for the reply.
Actually my concern is that user will select/click looking at the description not the code. And the prompt table will pass deptid e.g. MK00000
User will not type anything e.g. MK% . He/she will only click on look up value from prompt table and select a department.
Now when he/she selects one of the department e.g. MK00000 i need to pass value to query deptid like *'MK000%'*
That can only be accomplished by creating a view which supplies a 'fake' value back to the query. You'd have to create a view which has, besides all the other fields in the lookup you're using now (probably the DEPT_TBL), an extra field with something like "SUBST(DEPTID,1,5)||'%'". You then use this view as the table prompt for the prompt value.
When a user runs this query he will be able to select a DEPTID. When the DEPTID is selected the first key value will be supplied back to the query, which in this case will be 'MK000%'.
Thanks. Your Suggestion was really helpful.