Fixed Asset Query Correction

[Former Member]

Dear Experts,

I have created the below query for the Fixed Asset Register. But in the last column 'Ordinary' i cannot join the subquery with the main query query I keep getting error message:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =,!=,<,<=,>,>= or when the subquery is used as an expression."

Please correct the query and if possible explain the answer.

I'll be gratefull

SELECT DISTINCT T0.U_AsstNum, T0.U_AcqDate, T0.U_Desc, T1.U_DprDate, T1.U_DprTypID, T2.U_APC, T2.U_OrDpAcc, T3.U_Price AS 'Acquisition',

+(SELECT T4.[U_OrDpPlan] FROM [dbo].[@BA_ODPVN] T4 WHERE T4.U_AsstNum = T1.U_AsstNum) AS 'Ordinary Depreciation'+

FROM

[dbo].[@BA_OAMD] T0 INNER JOIN [dbo].[@BA_OAMD2]

T1 ON T0.U_AsstNum = T1.U_AsstNum LEFT JOIN [dbo].[@BA_OAMD3] T2

ON T0.U_AsstNum = T2.U_AsstNum AND T1.U_AsstNum = T2.U_AsstNum LEFT JOIN [dbo].[@BA_ODOC2]T3

ON T0.U_AsstNum = T3.U_Account AND T1.U_AsstNum = T3.U_Account AND T2.U_AsstNum = T3.U_Account

[Former Member · Accepted solution]

Hi, right now i can only explain the error to you, can't correct your syntax, becouse this is not such an error, and without knowing more (your dataset and your goal in detail) i can't help any more.

THe error means, you want SQL to give back a value in a field, but the subselect you are useing gives back more than one piece of data, and only one piece of data may be fitted into a field by SQL.

Here is a little example

i have 2 tables:

Table One: ID of the costumer | Name of the costumer

1 | Jack
2 | John
3 | Jill
4 | James

Table Two: ID of the costumer | Value of order

1 | 10
1 | 20
2 | 20
3 | 30
4 | 40

When i query these two tables the same way you are doing it:

select t1.name , (select t2.Value from tabletwo t2 where t2.ID=t1.ID) from tableone t1

logicaly we can see that Jack has two orders, so for this one grid field the select

"select t2.Value from tabletwo t2 where t2.ID=1"

gives us back two values.

So i suggest you to run your sub query alone, and vertify.

If you get multiple values back on any items, you can do may things based on your situation:

(as for my example) can sum the values into one value

add further where conditions to filter only one value

group, avg, sum, etc

Hope this helps.

Regards,

D

[Former Member]

Thanks for the hints

Edited by: Faisal Baloch on Mar 4, 2011 10:52 AM

[Former Member]

Hi,

Try this first:

SELECT DISTINCT T0.U_AsstNum, T0.U_AcqDate, T0.U_Desc, T1.U_DprDate, T1.U_DprTypID, T2.U_APC, T2.U_OrDpAcc, T3.U_Price AS 'Acquisition',T4.[U_OrDpPlan] AS 'Ordinary Depreciation'

FROM [dbo].[@BA_OAMD] T0
INNER JOIN [dbo].[@BA_OAMD2] T1 ON T0.U_AsstNum = T1.U_AsstNum
LEFT JOIN [dbo].[@BA_OAMD3] T2
ON T0.U_AsstNum = T2.U_AsstNum AND T1.U_AsstNum = T2.U_AsstNum 
LEFT JOIN [dbo].[@BA_ODOC2]T3
ON T0.U_AsstNum = T3.U_Account AND T1.U_AsstNum = T3.U_Account AND T2.U_AsstNum = T3.U_Account
LEFT JOIN [dbo].[@BA_ODPVN] T4 ON T4.U_AsstNum = T1.U_AsstNum

Thanks,

Gordon