| Need a Logic to check matching rows in the same table [message #392840] |
Thu, 19 March 2009 08:15  |
bahubcd
Messages: 40
Registered: July 2007
Location: Bangalore
|
Member |
|
|
Hi all,
Though my query is based on a PLSQL issue, I am posting it in the FORMS topic here because:
1. I need the logic to work in forms eventually.
2. A forms developer can better understand the issue due to his knowledge about Post and Commit_Form statements in the forms.
I am having a datablock in my form based on a table called CATEGORY_BRACKET with the structure:
CATEGORY_ID NUMBER
DEPT NUMBER
CLASS NUMBER
SUBCLASS NUMBER
So effectively for a given CATEGORY_ID, there can exist a combination of DEPT,CLASS & SUBCLASS.
In my form I am creating a Variation that has Categories grouped inside it. The category and variation combination exists in another table CATEGORY_VARIATION which has these 2 columns.
Imagine I have created Variation A and it has got Categories-1,2,3 existing which will have data in the table like below:
CATEGORY_VARIATION:
VARIATION_ID CATEGORY_ID
A 1
A 2
A 3
CATEGORY_BRACKET:
CATEGORY_ID DEPT CLASS SUBCLASS
1 10 100 1000
1 20 100 1000
1 10 200 1000
2 10 100 1000
2 20 100 1000
3 10 100 1000
3 20 100 2000
Let us assume I go to create a Variation B now. I try to create a category 4 with the below data in the data block in the form and Post the records to the table using Post;
CATEGORY_VARIATION:
VARIATION_ID CATEGORY_ID
B 4
CATEGORY_ID DEPT CLASS SUBCLASS
4 10 100 1000
4 20 100 1000
4 10 200 1000
When I click on the SAVE Button on my form, I should not be allowed to Commit the changes to the database for this Category:4 because it has got the all the same rows for the columns DEPT,CLASS&SUBCLASS combination w.r.t the Category 1. Instead the user should be asked to associate the Category 1 to this Variation B.
Now can some one tell me the most effective way to write a back-end function that takes in Variation id:A as input and gives me out Category Id:4 and Category Id:1 to be shown to the user as an error message i.e. ask the user to delete Category 4 and copy category 1 for his variation.
I am planning to loop record by record and compare DEPT,CLASS&SUBCLASS combination for different categories in the CATEGORY_BRACKET table, but It will surely have performance issues.
Suggestions and feedback on this issue are most welcome.Please bring in your thoughtful ideas in helping me out. Thanks for all your attention and co-operation.
Bahubcd.
|
|
|
|
| Re: Need a Logic to check matching rows in the same table [message #394921 is a reply to message #392840] |
Mon, 30 March 2009 18:36  |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
Have you solved your problem?
I suggest passing the items from your form as parameters to a function or procedure either in the form, a library, or (my preference) the database. Use these values to do a 'select' which will tell you whether the combination already exists and perhaps pass back the 'category_id' if it is found.
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
