OraFAQ Forum: Server Administration » duplicate value in SQL statement

Home » RDBMS Server » Server Administration » duplicate value in SQL statement

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

duplicate value in SQL statement [message #58459]

Thu, 28 August 2003 09:15

Nguyen Ba Tu
Messages: 4
Registered: August 2003

Junior Member

hi all,
I have a question to you(s) as follows.
- I use Oracle 9i (9.0.1.1) on server IBM eserver(4 cpu)
- I use ADO ver 2.1 above to connect to DB in my program.
- I have three tables be CUSTOMER, PAYMENT_TRACKING, and PAYMENT_DETAIL

Create table CUSTOMER
(CUSTOMER_CODE VARCHAR2(15),
CUSTOMER_NAME VARCHAR2(75),
CUSTOMER_ADDR VARCHAR2(75),
....
)
with primary key be CUSTOMER_CODE,

Create table PAYMENT_TRACKING
(
PAYMENT_ID NUMBER(10),
CUSTOMER_CODE VARCHAR2(15),
CASHIER_CODE VARCHAR2(15),
PAYMENT_DATE DATE,
....
)
with PAYMENT_ID is unique key

Create table PAYMENT_DETAIL
(
PAYMENT_ID NUMBER(10),
PAYMENT_DEBT NUMBER(10),
VAT_DEBT NUMBER(10)
.....
)

with PAYMENT_ID is unique key

- I have SQL statement as follow:
SELECT A.CUSTOMER_CODE, A.CUSTOMER_NAME,
SUM(C.PAYMENT_DEBT) AS PAYMENT_DEBT,
SUM(C.VAT_DEBT) AS VAT_DEBT
FROM CUSTOMER A, PAYMENT_TRACKING B, PAYMENT_DETAIL C
WHERE B.PAYMENT_ID = C.PAYMENT_ID
AND B.CUSTOMER_CODE = B.CUSTOMER_CODE
AND B.PAYMENT_DATE = TO_DATE('01/08/2003','DD/MM/YYYY')
AND B.CASHIER_CODE = 'MY'
GROUP BY A.CUSTOMER_CODE, A.CUSTOMER_NAME

When I executed my SQL in SQL Navigator of Quests software, it always runed exactly, but in my program using Active report is written by VB, sometime, the value of two fields (PAYMENT_DEBT, VAT_DEBT) is duplicate (not always) so it very dificult to debug.
Example (PAYMENT_DEBT; VAT_DEBT) is (1,000,000 ; 100,000) in database, but show in display is (2,000,000 ; 200,000)
May be, I restart my computer and reopen my program, it run exactly, not duplicate. But until tomorow, it can duplicate

I tested my code, it is exactly. If i use Oracle 8i 8.1.7, my program is always exact. Never duplicate of value

I don't know what happen

Please help me

Report message to a moderator

Previous Topic:	why avg(newwrites.value-oldwrites.value) is writes
Next Topic:	ORA-12700

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 13:45:56 CDT 2024