Home » Developer & Programmer » Forms » Need a Logic to check matching rows in the same table (Oracle Forms 10G, Oracle 10G DB, Unix OS)
Need a Logic to check matching rows in the same table [message #392840] Thu, 19 March 2009 08:15 Go to next message
Messages: 36
Registered: July 2007
Location: Bangalore
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:

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:

A                  1
A                  2
A                  3

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;

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.

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 Go to previous message
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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.

Previous Topic: copy records
Next Topic: how to use the chart Wizard
Goto Forum:

Current Time: Tue Feb 28 02:12:33 CST 2017

Total time taken to generate the page: 0.20178 seconds