SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Table two Column


Table two Column

Author
Message
jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
Hello
I have a table that gives me information, it is for example in column 3 appears OK the values ​​and delete.
I would like the values ​​in column 2 when the value in column 3 was Delete, puts me up values ​​from column 2 to zero.
It is possible to do through the Reporting Services?
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9588 Visits: 9755
Table DDL, sample data and query please.
What do you expect to see in each of the columns with the sample data?


Far away is close at hand in the images of elsewhere.

Anon.


jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
My table has the following results

col1 col2 col3
test1 10 OK
test1 10 DEL
test2 5 OK

this is the way I have this moment where col3 data are OK or DEL

I wanted the result to be:

col1 col2 col3
test1 10 OK
test1 0 DEL
test2 5 OK

ie, when the field was col3 DEL, put to zero the value of col2
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9588 Visits: 9755
Either change the sql query

SELECT col1, CASE WHEN col3 = 'DEL' THEN 0 ELSE col2 END AS [col2], col3
FROM [table]



Or use an expression for column 2
=IIF(Fileds!col3.Value = "DEL",0,Fileds!col2.Value)


Far away is close at hand in the images of elsewhere.

Anon.


jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
Thanks for the help was really what I needed.

Now let me just put one more question.

Did you do the SUM of col2 but I failed. the textbox returns only # errors
I'm using
= SUM (IIF (Fields!col3.Value = "DEL", 0, (Fields! col2.Value)))
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9588 Visits: 9755
Expression looks OK
Check all the values in col2, looks like you have non integer value or NULL.


Far away is close at hand in the images of elsewhere.

Anon.


jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
I have a value 59.90

I can not use numbers with decimals?
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9588 Visits: 9755
Sorry my bad it is incompatible data types, try converting to double or decimal

=SUM(IIF(Fields!col3.Value = "DEL",CDbl(0),CDbl(Fields!col2.Value)))
or
=SUM(IIF(Fields!col3.Value = "DEL",CDec(0),CDec(Fields!col2.Value)))


Far away is close at hand in the images of elsewhere.

Anon.


jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
Thank you very much. Your solution worked.
jafm
jafm
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 189
Now needed something else. placing the value of the negative col2 instead of 0.
I've tried doing the following ways and not resulted, where I am going wrong?

=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)-(Fields!col2.Value),CDec(Fields!col2.Value)))

=SUM(IIF(Fields!col3.Value = "DEL",CDec(Fields!col2.Value)*(-1),CDec(Fields!col2.Value)))
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search