If result is 4, then multiply other fields by -1?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

If result is 4, then multiply other fields by -1??

Author

Topic

econrad
Starting Member

6 Posts

Posted - 2011-08-30 : 15:28:43

I need to add code that does the following:
If dbo.SOP30200.SOPTYPE = 4, then dbo.SOP30300.QUANTITY * -1
If dbo.SOP30200.SOPTYPE = 4, then dbo.SOP30300.EXTDCOST * -1
If dbo.SOP30200.SOPTYPE = 4, then dbo.SOP30300.XTNDPRCE * -1

Who is this done?

SELECT TOP (100) PERCENT dbo.IV00101.PriceGroup, dbo.SOP30200.CUSTNAME, dbo.SOP30200.CUSTNMBR, dbo.SOP30300.ITEMNMBR,
dbo.SOP30300.QUANTITY, dbo.SOP30200.SOPTYPE, dbo.SOP30200.SOPNUMBE, dbo.SOP30200.DOCDATE, dbo.SOP30200.DOCID AS SalesPerson,
dbo.SOP30300.EXTDCOST, dbo.SOP30300.XTNDPRCE, dbo.SOP30200.FRTAMNT, dbo.IV00101.ITMCLSCD, dbo.SOP30200.CSTPONBR
FROM dbo.SOP30200 INNER JOIN
dbo.SOP30300 ON dbo.SOP30200.SOPTYPE = dbo.SOP30300.SOPTYPE AND dbo.SOP30200.SOPNUMBE = dbo.SOP30300.SOPNUMBE INNER JOIN
dbo.IV00101 ON dbo.SOP30300.ITEMNMBR = dbo.IV00101.ITEMNMBR
WHERE (NOT (dbo.SOP30300.ITEMNMBR LIKE 'BOD%')) AND (NOT (dbo.SOP30200.SOPTYPE IN (1, 2))) AND (dbo.SOP30200.DOCDATE BETWEEN
@STARTDATE AND @ENDDATE)
ORDER BY dbo.SOP30200.DOCDATE, SalesPerson

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 16:04:19

Use a CASE expression.

CASE WHEN ... THEN ... ELSE ... END

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

econrad
Starting Member

6 Posts

Posted - 2011-08-30 : 16:06:13

Can you give me an example, using my code? I am at a loss here.

Thank you

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 16:09:19

It's best to try it on your own that way you learn the syntax. Here are plenty of examples: http://msdn.microsoft.com/en-us/library/ms181765.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 16:15:20

Aren't the column names malformed?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-08-30 : 16:17:18

Yes that's what I thought too, but I figured the OP must be posting a working query so I figured I wouldn't mention it. I don't think you can use 3-part name for columns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2011-08-30 : 16:21:32

guess not


CREATE TABLE dbo.sop30200_bk (soptype int, quantity int)
GO
SELECT CASE WHEN dbo.sop30200_bk.soptype = 4 THEN dbo.sop30200_bk.quantity * -1 
											 ELSE dbo.sop30200_bk.quantity 
	   END AS dbo.sop30200_bk.quantity
FROM dbo.sop30200_bk
GO

DROP TABLE dbo.sop30200_bk 
GO


-- I Prefer

CREATE TABLE sop30200_bk (soptype int, quantity int)
GO
SELECT CASE WHEN a.soptype = 4 THEN a.quantity * -1 
							   ELSE a.quantity 
	   END AS [quantity]
FROM sop30200_bk a
GO

DROP TABLE dbo.sop30200_bk 
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-31 : 06:47:43

3 part name for a column? oooo the pedants should LOVE this!

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources