CODEWITHSUNDEEP
sqlsql-server
Mahmut EFE
Mahmut EFE

Reputation: 5248

Using Extraction in SQL Server

I wrote a sql query and my sample query like below: 

select 
    value1,
    value2,
    value3,
    (
        select CAST(amount1 as decimal(17,2)) from 
        table1 where something...) 
       - select CAST(amount2 as decimal(17,2)) from table1 where something...)
    ) as 'Total Purchase'

    from 
        table1
    where 
        something

But, I am getting syntax error at "-" operator.

i tried to use "Set" statement like below

Declare 
   @value1 decimal(17,2),
   @value2 decimal(17,2),
   @result decimal(17,2)

set value1 = select CAST(amount1 as decimal(17,2)) from table1 where something...);
set value2 = select CAST(amount2 as decimal(17,2)) from table1 where something...);
set result = value1 - value2;

but i am getting syntax error again,

What can i use instead of "-" operator.

Thnaks for your advice,,

Upvotes: 0

Views: 68

Answers (4)

Dudi Konfino
Dudi Konfino

Reputation: 1136

in case of 'something...' is equal in all where starments

 select 
        value1,
        value2,
        value3,
        (
            select (CAST(amount1 as decimal(17,2))  -  CAST(amount2 as decimal(17,2)) )
        ) as 'Total Purchase'

        from 
            table1
        where 
            something

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

There are lot of things wrong in your sql. try this

SELECT value1,
       value2,
       value3,
       (SELECT Cast(amount1 AS DECIMAL(17, 2))
        FROM   table1 where something...) - (SELECT Cast(amount2 AS DECIMAL(17, 2))
                          FROM   table1 where something...) AS 'Total Purchase'
FROM   table1

or

DECLARE @value1 DECIMAL(17, 2),
        @value2 DECIMAL(17, 2),
        @result DECIMAL(17, 2)

SET @value1 = (SELECT Cast(amount1 AS DECIMAL(17, 2))
               FROM   table1 where something...)
SET @value2 = (SELECT Cast(amount2 AS DECIMAL(17, 2))
               FROM   table1 where something...)
SET @result = @value1 - @value2;

Upvotes: 3

Rahul
Rahul

Reputation: 77876

If I have understood correct, Your posted query can be simplified to below

select 
    value1,
    value2,
    value3,
    CAST(amount1 as decimal(17,2)) - CAST(amount2 as decimal(17,2)) as 'Purchase'
    from 
        table1
    where 
        something

(OR) try doing the same using a outer query like

select value1, value2,value3, (amt1 - amt2) as 'Total Purchase'
from
( 
select 
    value1,
    value2,
    value3,
    CAST(amount1 as decimal(17,2)) as amt1,
    CAST(amount2 as decimal(17,2)) as amt2
    from 
        table1
    where 
        something
 ) tab

Upvotes: 1

Nightmaresux
Nightmaresux

Reputation: 538

Just a syntax error, i believe it should be like this :

( 
(select CAST(amount1 as decimal(17,2)) from table1 where something...) - (select CAST(amount2 as decimal(17,2)) from table1 where something...)
) as total purchase

Upvotes: 1

Related Questions