Different answer sql*plus and oracle forms

[ghssal]

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.

[Pilgrim333]

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.

[amitpatel66]

under which trigger you have defined this code in forms??.

[ghssal]

The trigger i have defined this code in forms under
WHEN-VALIDATE-ITEM

[ghssal]

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;

[Pilgrim333]

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.

[amitpatel66]

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.

[Pilgrim333]

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

select .....
from  customer_order c
where c.customer_id = :customer_id 
and   c.order_date = 
    (select max(o.order_date) 
     from customer_order o
     where o.customer_id = c.customer_id
    ) 
 

Pilgrim.

[ghssal]

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.

[ghssal]

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

select .....
from  customer_order c
where c.customer_id = :customer_id 
and   c.order_date = 
    (select max(o.order_date) 
     from customer_order o
     where o.customer_id = c.customer_id
    ) 
 

Pilgrim.

Thanks to you because of your efforts. The problem are solve