My query wont work?

[Linkan]

Hi, I am trying to set up a query that makes a value positive i is is a certain transaction.
Trying to set it up in a sproc but I just don't seem to make it work. What am I doing wrong here?
Thanks,
Linkan

DECLARE @lngType int
SELECT @lngType = (SELECT lngType FROM vLinkanTestView)
IF @lngType = '2'
BEGIN
SET NOCOUNT ON
SELECT strPosCode, SUM((lngPrice)-(lngPrice)-(lngPrice)) AS lngPrice, strName FROM vLinkanTestView WHERE lngType = '2' GROUP BY strPosCode, strName
END
ELSE
BEGIN
SET NOCOUNT ON
SELECT strPosCode, SUM(lngPrice) AS lngPrice, strName FROM vLinkanTestView WHERE lngType <> '2' GROUP BY strPosCode, strName
END

 

[AWithers]

One obvious thing

You have declared @lngType as an int but you are tesrating it as a varchar
e.g. @lngType = '2'

Andy

 

[Linkan]

Thanks for your input but it did not help.
/Linkan

 

[AWithers]

Also setting nocount on means that it returns nothing...

I would delete those lines


Andy
e.g.

DECLARE @lngType int
SELECT @lngType = (SELECT lngType FROM vLinkanTestView)
IF @lngType = 2
BEGIN
-- SET NOCOUNT ON
SELECT strPosCode, SUM((lngPrice)-(lngPrice)-(lngPrice)) AS lngPrice, strName FROM vLinkanTestView WHERE lngType = 2 GROUP BY strPosCode, strName
END
ELSE
BEGIN
-- SET NOCOUNT ON
SELECT strPosCode, SUM(lngPrice) AS lngPrice, strName FROM vLinkanTestView WHERE lngType <> 2 GROUP BY strPosCode, strName
END

 

[Linkan]

That did not help much.
This is the message i get.

Server: Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

/Linkan

 

[AWithers]

Then the problem lies with your second line

i.e

SELECT @lngType = (SELECT lngType FROM vLinkanTestView)


it is retirning more than one value and SQL cant deal with that...I would suggest you need to put in some criteria and also possibly make it distinct values

SELECT @lngType = (SELECT distinct lngType FROM vLinkanTestView where .........)

 

[Linkan]

I see what you are saying, don't know how I can do it then. I need to sort all my accounts and sum the ammount but when the account type is 2 I need to convert - to + and sum it as well.

Thanks for all your help.
/Linkan

 

[AWithers]

Not quite sure what you mean

Do you want to take all accounts and add the values and where account type =2 then convert negative values to positive?

If so then I would suggest one way to solve it is to insert the summed data into a temp table in two stages

e.g. *(SORRY NOT SYTNAX CHECKED AT ALL BUT HOPEFULLY YOU GET THE IDEA)

create table ##tempdata(strPosCode varchar(100), LngSumPrice int, Name varchar(100))
--turn messages off

set nocount on
insert into ##tempdata
--where code =2
SELECT strPosCode, SUM((lngPrice)-(lngPrice)-(lngPrice)) AS lngPrice, strName FROM vLinkanTestView WHERE lngType = 2 GROUP BY strPosCode, strName


--where code <>2
insert into ##tempdata

SELECT strPosCode, SUM((lngPrice)-(lngPrice)-(lngPrice)) AS lngPrice, strName FROM vLinkanTestView WHERE lngType = 2 GROUP BY strPosCode, strName

--turn messages on again
set nocount off

select * from ##tempdata
--drop temp data table
drop ##tempdata

 

[JamesLean]

I've not tried it but I think this should work:

SELECT strPosCode, strName, SUM(CASE WHEN lngType = 2 THEN lngPrice - lngPrice - lngPrice ELSE lngPrice END) AS lngPrice
FROM vLinkanTestView
GROUP BY strPosCode, strName

--James

 

[Linkan]

Thanks guys, great work, it works just fine now.

The second option is more simple and I had some problems with the group function when I had the temp table.

Thanks again!
/Linkan

 

[tlbroadbent]

AWithers,

SET NOCOUNT ON does not prevent rows form being returned. It inhibits the rows affected message and is often it is desirable to do this in a Stored Procedure.

Linkan,

Have you considered using the ABS function (absolute value) to make the value positive? It is much simpler to use the following query.

SELECT
strPosCode, strName,
SUM(CASE WHEN lngType = 2
THEN ABS(lngPrice) ELSE lngPrice END) AS lngPrice
FROM vLinkanTestView
GROUP BY strPosCode, strName

If lngType = 2 are the only negative values, you could even simplify as follows.

SELECT
strPosCode, strName,
SUM(ABS(lngPrice)) AS lngPrice
FROM vLinkanTestView
GROUP BY strPosCode, strName If you want to get the best answer for your question read faq183-874 and thread183-468158.


Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Linkan]

Hi Terry,

I will use that ABS function that is a great idea, thanks for your input. Much cleaner.

/Linkan