By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

Different answer sql*plus and oracle forms

P: 10
HI

I am using ORACLE forms and trying to insert a new order, by enter the customer_ID and i will get the latest order
för this customer and save the new order with new order_date.

I use this Trigger
select .....
from customer_order, (
select customer_ID AS B, MAX(order_date) AS S
from customer_order
GROUP BY customer_ID)
where :customer_ID = B and order_date = S;

IN ORCLE FORMS mode i get another customer_ID and order who share the 'order_date' and inserted before a pressing issue.

When i try in SQL PLUS i get the right customer_ID but i get three record for one order as


customer_ID order_date
------------ ----------
1945 2008/08/12
1945 2008/08/12
1945 2008/08/12


the table

customer_ID order_date
------------ ----------
1945 2007/04/15
1945 2007/05/01
1945 2007/05/04
1945 2008/08/12* This record came in SQL plus tree timesa.
1961 2008/09/09
1961 2007/02/07
1961 2008/08/12
1961 2001/01/01
1961 2004/04/04
1961 2006/06/06
1961 2008/08/12* this what i get in FORMS mode instead for customer_ID=1945.
1961 2008/08/12
1981 2008/08/12


Can you explian how i solve the problem and get the right order for the right customer_ID.
Nov 4 '08 #1
Share this Question
Share on Google+
9 Replies


100+
P: 127
Hi,

Just a few questions.

What kind of trigger is it?
Did you run the query in SQL*PLUS or did you just insert a record in SQL*PLUS?
Can you post the exact query you are using? The where part seems a bit faulty, you are using
:customer_ID = B

Pilgrim.
Nov 4 '08 #2

amitpatel66
Expert 100+
P: 2,367
under which trigger you have defined this code in forms??.
Nov 4 '08 #3

P: 10
The trigger i have defined this code in forms under
WHEN-VALIDATE-ITEM
Nov 4 '08 #4

P: 10
1- The trigger i have defined this code in forms under
WHEN-VALIDATE-ITEM

2- I run the query in SQL*PLUS as
select customer_ID, order_date,......
from customer_order, (
select customer_ID AS B, MAX(order_date) AS S
from customer_order
GROUP BY customer_ID)
where customer_ID = 1945 and order_date = S;
Nov 4 '08 #5

100+
P: 127
Hi,

I think something is going wrong with the customer_id that is used in the trigger. Just after the query in your form, let a messagebox pop up with the value of the customer_id. If this is 1961, then put the messagebox at the start of the trigger and show it then.

Post here what the results were.

Pilgrim.
Nov 4 '08 #6

amitpatel66
Expert 100+
P: 2,367
1- The trigger i have defined this code in forms under
WHEN-VALIDATE-ITEM

2- I run the query in SQL*PLUS as
select customer_ID, order_date,......
from customer_order, (
select customer_ID AS B, MAX(order_date) AS S
from customer_order
GROUP BY customer_ID)
where customer_ID = 1945 and order_date = S;

Please clarify couple of things:

1. The customer_id is passed from forms field?
2. Order date is passed from forms field?

Your query is incorrect.I would help you out in reframing your query once you clarify the above two points.
Nov 5 '08 #7

100+
P: 127
Indeed you query is incorrect.
The problem you have, is that you are comparing the value with form with the wrong table. Try this and see if it gives the same results as the query you want

Expand|Select|Wrap|Line Numbers
  1. select .....
  2. from  customer_order c
  3. where c.customer_id = :customer_id 
  4. and   c.order_date = 
  5.     (select max(o.order_date) 
  6.      from customer_order o
  7.      where o.customer_id = c.customer_id
  8.     ) 
  9.  
Pilgrim.
Nov 5 '08 #8

P: 10
Please clarify couple of things:

1. The customer_id is passed from forms field?
2. Order date is passed from forms field?

Your query is incorrect.I would help you out in reframing your query once you clarify the above two points.
Yes, both the customer_id and Order date is passed from forms field.
Nov 7 '08 #9

P: 10
Indeed you query is incorrect.
The problem you have, is that you are comparing the value with form with the wrong table. Try this and see if it gives the same results as the query you want

Expand|Select|Wrap|Line Numbers
  1. select .....
  2. from  customer_order c
  3. where c.customer_id = :customer_id 
  4. and   c.order_date = 
  5.     (select max(o.order_date) 
  6.      from customer_order o
  7.      where o.customer_id = c.customer_id
  8.     ) 
  9.  
Pilgrim.


Thanks to you because of your efforts. The problem are solve
Nov 7 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.