MrHelpMe wrote:
> On Apr 30, 3:08 pm, Ed Prochak <edproc..._at_gmail.com> wrote:
>> On Apr 30, 12:38 pm, MrHelpMe <clintto..._at_hotmail.com> wrote:
>>
>>
>>
>>
>>
>>> On Apr 30, 12:09 pm, DA Morgan <damor..._at_psoug.org> wrote:
>>>> MrHelpMe wrote:
>>>>>> Why two procedures and not one?
>>>>>> --
>>>>>> Daniel A. Morgan
>>>>>> University of Washington
>>>>>> damor..._at_x.washington.edu
>>>>>> (replace x with u to respond)
>>>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hidequotedtext -
>>>>>> - Show quoted text -
>>>>> Hi DA Morgan,
>>>>> Thanks for the reply. I used 2 procedures originally because I wasn't
>>>>> sure how to accomplish this in 1 procedure. To be honest I just
>>>>> managed to figure this out and it looks like it is definitely working
>>>>> now, however, I need your assistance and ask you and other experts
>>>>> what I can do in the below situation.
>>>>> Now that I have the insert working, how can I say the following. Upon
>>>>> insert if the FirstName, LastName and Email address are the same do
>>>>> not insert into the Users table but insert into the other 2 tables all
>>>>> the info. plus the appropriate user id.
>>>> What you are asking is unclear but look at the Merge Statement if the
>>>> choice in a single table is insert/update. Look at the INSERT FIRST,
>>>> INSERT ALL, and INSERT WHEN statements.
>>>> Also consider doing a SELECT COUNT(*) to determine if the record has
>>>> already been inserted.
>>>> You can find all of these in Morgan's Library atwww.psoug.org.
>>>> Look up MERGE and INSERT.
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor..._at_x.washington.edu
>>>> (replace x with u to respond)
>>>> Puget Sound Oracle Users Groupwww.psoug.org-Hidequoted text -
>>>> - Show quoted text -
>>> Sorry DA Morgan let me clarify. I'm not sure how much you know about
>>> front end coding but I will keep it simple. I have an ASP page that
>>> when the user clicks on the page he is asked a serieds of question.
>>> Like what is your first name, what is your last name, what is your
>>> email adress, what kind of equipment do you want, for what purpose
>>> etc. After the user fills in all this information he/she will click a
>>> submit button. When they click the submit button, behind the scenes I
>>> do an insert into(based on the insert statement that I showed you
>>> above) into the users table, hardware table and approver table. Now
>>> if Joe Smith fills out this form and then clicks submit and then comes
>>> to the page again, fills out the form again and clicks submit, well I
>>> would not want to insert his name twice into the users table but
>>> rather have unique users being captured in this table. So therefore,
>>> how do I code this in the backend to say if the user is not unique
>>> don't insert the entry into the users table but all the other info.
>>> that he/she submitted will be inserted into the respective tables. I
>>> hope this is clear. Please let me know if it is not and in the mean
>>> time I will have a look at what you mentioned
>> Such is the folly of ID columns as the primary key.
>>
>> Change your data model!- Hide quoted text -
>>
>> - Show quoted text -
>
> Sorry guys I am still a bit confused. I was hoping for my primary key
> to be the next value in oracle.
There is no such concept in relational databases.
> This is an auto incremented number.
These kudges exist in some products but not Oracle.
> DA morgan what would the select count(*) do for me and where would I
> include this(before the insert). Sorry I am just a bit confused as I
> have never done this before.
I would suggest you push back from the keyboard and taking a beginning
class in relational databases.
I don't mean to be rude here but if you have to ask that question you
are just not qualified to do the work indicated.
Assuming you are, in fact, a student here is the answer:
If the count is zero then no corresponding record exists. If the count
is one then one record already exists. If the count is two or more you
have a violation of your stated business rule.
If you are a student you need to say that. Otherwise you really need
to hand this project to someone qualified to do it. Right now you
look like an bartender trying to design a suspension bridge.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue May 01 2007 - 10:15:24 CDT
