Hi Crispin,
Thank you, as you said, eventually I did found the "culprit". If you (or anyone else) don't mind, I would like to ask some advise for what is wrong with my query .
So, it turned, I have this query :
SELECT T1.SALESID
,
(CAST
(
(ISNULL
(
(
SELECT 1
FROM CASETABLE a,
SALESTABLE b
WHERE a.projid = b.projid
AND a.dataareaid = b.dataareaid
AND b.salesid = T1.salesid
), 0
)
) AS int
)
) AS invoiceorigin
FROM DEVICEINVOICEFACT T1
The one in red is the cause of error, and according to one website, I should put MAX in that subquery of "SELECT 1"
Can someone explain to me why ? Is it because the key T1.SALESID on the string ( " ..... AND b.salesid = T1.salesid....." ) ?
The T1.SALESID does have possibility of occurring more than 1, but if I test it with WHERE clause to put the SALESID which occurred more than 1 like this :
SELECT T1.SALESID
,
(CAST
(
(ISNULL
(
(
SELECT 1
FROM CASETABLE a,
SALESTABLE b
WHERE a.projid = b.projid
AND a.dataareaid = b.dataareaid
AND b.salesid = T1.salesid
), 0
)
) AS int
)
) AS invoiceorigin
FROM DEVICEINVOICEFACT T1 Where T1.SALESID = '000408" (which indeed that salesid in T1 table have more than 1 rows), the query does execute completely with no error.
So basically I need to change my query to be like this :
SELECT T1.SALESID
,
(CAST
(
(ISNULL
(
(
SELECT MAX(1)
FROM CASETABLE a,
SALESTABLE b
WHERE a.projid = b.projid
AND a.dataareaid = b.dataareaid
AND b.salesid = T1.salesid
), 0
)
) AS int
)
) AS invoiceorigin
FROM DEVICEINVOICEFACT T1
Just want to understand why we need that MAX()
Kindly advice.
Thanks
