471,319 Members | 1,695 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

Different answer sql*plus and oracle forms

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
9 3115
Pilgrim333
127 100+
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
2,367 Expert 2GB
under which trigger you have defined this code in forms??.
Nov 4 '08 #3
ghssal
10
The trigger i have defined this code in forms under
WHEN-VALIDATE-ITEM
Nov 4 '08 #4
ghssal
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
Pilgrim333
127 100+
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
2,367 Expert 2GB
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
Pilgrim333
127 100+
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
ghssal
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
ghssal
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.

Similar topics

3 posts views Thread by khangu | last post: by
3 posts views Thread by Peter | last post: by
2 posts views Thread by Ant | last post: by
reply views Thread by rosydwin | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.