SAP (Documents/Screen shots/Complete Solution): RE:[peopletools-l] SQL Error - No Matching Values Found in Dynamic Prompt

Reply from ankit7 on Jul 3 at 2:42 AM

Thanks for the reply.

Actually, in my case the PS_VALID_COMBO_TBL and PS_BUDGET_DEPT_TBL are the same.
It is the PS_DEPT_TBL and the BUDGET_DEPTID is not a prompt field in this record. This value will be associated with each and every department.

I created a SQL view with only one field BUDGET_DEPTID and assigned the view as Prompt table for the same field BUDGET_DEPTID in my run control record.

The SQL for the view I used was:

SELECT distinct BUDGET_DEPTID from PS_DEPT_TBL

This SQL is returning the rows when executed on Database.
But when I click on the prompt on the Page, it still gives an sql error and says No Matching values were found.

---------------Original Message---------------
From: ankit sharma
Sent: Monday, July 02, 2012 11:59 PM
Subject: SQL Error - No Matching Values Found in Dynamic Prompt

Hi, I am using a dynamic prompt in for one of the fields(BUDGET_DEPTID) of Dept_Tbl. The requirement is that if user does not enter/select any value of Dept ID in the page, then the BUDGET_DEPTID should have all the distinct values in the Prompt. To achieve this I did the following:

i) Created an SQL view with one field BUDGET_DEPTID and mentioned the SQL body as : SELECT DISTINCT budget_deptid
FROM ps_dept_tbl

When I am running this SQL in database, it is returning the values.

ii) In the Table Edit property of Budget_Deptid field of the custom record, set the Edit Type property as "Prompt Table Edit" and Prompt Table name as : %Edittable

iii) There is one more field "DEPTID" in the same custom record. If the DEPTID field is blank then only the prompt of Budget_Deptid field should list down all the distinct values of budget_deptid field from DEPT_TBL.
So, I wrote the following PeopleCode in the Rowinit and FieldChange event of DEPTID field :

If None(Table_Name.DEPTID) Then
DERIVED.EDITTABLE = "View_Name";
End-If;

Also, I inserted the DERIVED.EDITTABLE field on the page and made it readonly. Not sure if it is required to be done or not.

iv) Now, when I click on the prompt of BUDGET_DEPTID on the page, it throws an SQL error message, the look up page opens up and says "No Matching Values were found"

Please let me know, if I am missing anything or something is not done correctly.

Thanks in advance.

Reply to this email to post your response.

__.____._

Manage Settings | Unsubscribe | Create FAQ | Send Feedback