Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Merging two select statements (one using Count(*))

618026Jul 9 2008 — edited Jul 9 2008
Hi!

Given the table SALESMEETING_CUSTOMERLIST:
SALESMEETINGID------------CUSTOMERID
10000-------------------------------20001010
10001-------------------------------20002020
10001-------------------------------20001010
10001-------------------------------20007070
10002-------------------------------20003030
10003-------------------------------20001010
10004-------------------------------20002020
10004-------------------------------20005050
10005-------------------------------20006060


I'm using the following statement to select all CUSTOMERID's which occur more than once in a SALESMEETING_CUSTOMERLIST:

SELECT CUSTOMERID, COUNT(*) TotalCount
FROM SALESMEETING_CUSTOMERLIST
GROUP BY CUSTOMERID
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

This gives me the following output:
CUSTOMERID------------TOTALCOUNT
20001010------------------ 3
20002020------------------ 2

Then I have the following script in which I insert the found CUSTOMERID's to:

SELECT * FROM SALESMEETING_CUSTOMERLIST
WHERE SALESMEETINGID IN(SELECT SALESMEETINGID
FROM SALESMEETING_CUSTOMERLIST
WHERE CUSTOMERID IN(20001010,20002020))

This gives me the following output:
SALESMEETINGID------------CUSTOMERID
10000-------------------------------20001010
10001-------------------------------20002020
10001-------------------------------20001010
10001-------------------------------20007070
10003-------------------------------20001010
10004-------------------------------20002020
10004-------------------------------20005050

(20003030 and 20006060 are omitted since these are the only customers who doesn't occure more than once in SALESMEETING_CUSTOMERLIST or is in the same salesmeeting as a customer who in turn is present more than once in SALESMEETING_CUSTOMERLIST)


So, I want to write this in ONE script so I don't have to go through these steps every time. Basically I want to merge the two scripts above in to one =)

Any ideas on how to do this?

Thanks!

/Björn

Comments

Need more details, including the Forms and DB versions you are using.
Are you using Forms blocks based on DB (i.e. "Data Blocks") or you are executing your own DML and populating (and altering) fields or both? And, how are you "committing" (COMMIT_FORM, or Save button or something else)? Be specific; if you are executing the "COMMIT" - are you using COMMIT or COMMIT_FORM?

MarcLaf

Using Oracle Forms 12c and Oracle DB 19. Yes, I'm using data blocks, based on tables from DB. User clicks a button to save and I'm doing a COMMIT_FORM.
Thanks,
Marc L

MarcLaf

Here's part of my Key-Commit code:
image.pngMarc L

Hmmm - If you are using a true "data block", there is often little reason to execute your own POST. Forms will do this automatically as soon as a the first change is made. This will result in a record lock. I'm not saying that doing a POST (or COMMIT_FORM) is a bad thing. I'm just saying that in a block managed by Forms, most of the "right" processing will happen automatically (a benefit of using Forms).
To your question, I have not tested, but suspect the behavior based on your description of what you are doing, is correct. Validation must occur upon POSTing, but also again when COMMITing. If you look at the Builder Help related to POST and COMMIT_FORM you will see that it supports this belief.
If you wanted to continue on this path, it might help to change the Validation Unit property (form level), as this might offer some relief. Of course doing this could also change the desired behavior of something else. Again, check the Builder Help for details on this property.

1 person found this helpful
1 - 4
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 6 2008
Added on Jul 9 2008
3 comments
4,096 views