calculation on subqueries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

calculation on subqueries

Author

Topic

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-21 : 12:50:25

Can someone explain why this statement does not work

(SELECT sum(SM_QUANTITY) FROM STK_MOVEMENTS WHERE SM_STOCK_CODE = SA1.AS_PART_CODEA ND SM_TYPE = 'A' AND SM_REFERENCE = cast(OH.OH_ORDER_NUMBER AS VARCHAR(10) AND SM_STATUS = 'I' and SM_LOCATION = LC2.LOC_CODE2) -

(SELECT sum(SM_QUANTITY) FROM STK_MOVEMENTS WHERE SM_STOCK_CODE = SA1.AS_PART_CODEA ND SM_TYPE = 'A' AND SM_REFERENCE = cast(OH.OH_ORDER_NUMBER AS VARCHAR(10) AND SM_STATUS = 'O' and SM_LOCATION = LC2.LOC_CODE2)

Both statements bring back the correct data if run individually, but if I try to subtract one from the other I get an error message, regarding the minus sign.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-21 : 13:09:06

Try it like this:

SELECT
	SUMA = 	sum(case when SM_STATUS = 'I' then SM_QUANTITY else 0 end) -
		sum(case when SM_STATUS = 'O' then SM_QUANTITY else 0 end)
FROM
	STK_MOVEMENTS
WHERE
	SM_STOCK_CODE = SA1.AS_PART_CODE AND
	SM_TYPE = 'A' AND 
	SM_REFERENCE = cast(OH.OH_ORDER_NUMBER AS VARCHAR(10)) AND 
	SM_STATUS in ('I','O') and 
	SM_LOCATION = LC2.LOC_CODE2

CODO ERGO SUM

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-21 : 13:09:09

--this doesn't work because its just 2 expressions and an operator like: 10-5
(select 10) - (select 5)

--this does because it's a Select Statement like: select 10-5
select (select 10) - (select 5)

Be One with the Optimizer
TG

wotrac
Yak Posting Veteran

98 Posts

Posted - 2005-04-21 : 13:17:36

Cheers

I tried TG's option first, but couldn't get it to work,
so opted for Mikes. Worked first time, many thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources