What is wrong with this query?
This query only need to select the record where the id is 23.
The problem occurs with this line: AND B.CARDNUMBER <> CARDNUMBER.
But it doesn't give me a record at all.
If I leave this code out of the query it gives me the records 23 and 29.
But I only need the records where the cardnumbers are not equal.
SELECT CARDNUMBER
FROM CUSTOMER_HISTORY
WHERE CAST(DATE_CREATED AS DATE) = (SELECT MAX(CAST(A.DATE_CREATED AS DATE))
FROM CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND CAST(A.DATE_CREATED AS DATE) <'2007-08-27'
)
AND CUSTOMER_HIST_ID IN (SELECT B.CUSTOMER_HIST_ID
FROM CUSTOMER_HIST B
WHERE CAST(B.DATE_CREATED AS DATE) = '2007-08-27'
AND B.CARDNUMBER IS NOT NULL
AND B.CARDNUMBER <> CARDNUMBER
)
ID, CUSTOMER_HIST_ID, CARDNUMBER, DATE_CREATED
23, 41, 3, 26/08/2007 11:58:05
24, 41, 4, 27/08/2007 11:58:10
25, 42, NULL, 27/08/2007 11:58:10
26, 42, 5, 27/08/2007 11:58:16
27, 43, NULL, 27/08/2007 11:58:16
28, 43, 6, 27/08/2007 11:58:20
29, 44, 6, 26/08/2007 11:58:20
30, 44, 6, 27/08/2007 11:58:42
Sorry for my bad English.
Thanks in advance
Nyh
This query only need to select the record where the id is 23.
The problem occurs with this line: AND B.CARDNUMBER <> CARDNUMBER.
But it doesn't give me a record at all.
If I leave this code out of the query it gives me the records 23 and 29.
But I only need the records where the cardnumbers are not equal.
SELECT CARDNUMBER
FROM CUSTOMER_HISTORY
WHERE CAST(DATE_CREATED AS DATE) = (SELECT MAX(CAST(A.DATE_CREATED AS DATE))
FROM CUSTOMER_HISTORY A
WHERE A.CUSTOMER_HIST_ID = CUSTOMER_HIST_ID
AND CAST(A.DATE_CREATED AS DATE) <'2007-08-27'
)
AND CUSTOMER_HIST_ID IN (SELECT B.CUSTOMER_HIST_ID
FROM CUSTOMER_HIST B
WHERE CAST(B.DATE_CREATED AS DATE) = '2007-08-27'
AND B.CARDNUMBER IS NOT NULL
AND B.CARDNUMBER <> CARDNUMBER
)
ID, CUSTOMER_HIST_ID, CARDNUMBER, DATE_CREATED
23, 41, 3, 26/08/2007 11:58:05
24, 41, 4, 27/08/2007 11:58:10
25, 42, NULL, 27/08/2007 11:58:10
26, 42, 5, 27/08/2007 11:58:16
27, 43, NULL, 27/08/2007 11:58:16
28, 43, 6, 27/08/2007 11:58:20
29, 44, 6, 26/08/2007 11:58:20
30, 44, 6, 27/08/2007 11:58:42
Sorry for my bad English.
Thanks in advance
Nyh