SELECT/WHERE statement for negative values

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 2005 Forums

Transact-SQL (2005)

SELECT/WHERE statement for negative values

Author

Topic

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 09:05:09

Hey All,

In my table I have a Amount column with values of -25, among others.

I can select the -25 from the SELECT statement, however, I would like to do this in the WHERE statement. The purpose is to return all amounts that have -25. Here is the script I am trying to run:

SELECT rowID_PK, Category, Type, Amount
FROM Transactions
WHERE Amount = '-25' AND Type LIKE 'DEP_%';

Thanks...

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 09:08:20

what is the datatype of the Amount Column??

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 09:10:24

Did you try running that query?
Did you get any error?

Madhivanan

Failing to plan is Planning to fail

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 09:15:49

Hey All,

Yes I tried running the following query and this is what I got:

SELECT rowID_PK, Category, Type, Amount
FROM Transactions
WHERE Amount = '-25' AND Type LIKE 'DEP_%';

ERROR>>>>>Disallowed implicit conversion from data type varchar to data type money. Use the CONVERT function to run this query.

So the datatype I am using for that column is: Amount. Now I can't change or modify my tables, so how could I run this query to return all Amounts with -25 in them?

Thank you all for the help, very much appreciated.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 09:18:48

[code]
Declare @Table Table
(
Amount Money
)

Insert @Table
Select -25

Select * From @Table Where Amount = -25
[/code]

Chirag

http://chirikworld.blogspot.com/

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 09:34:01

I am getting an error saying invalid column names, this is the query I am running:

Declare @Transactions Table(Amount Money)
Insert Into @Transactions(Amount)
Select -25

SELECT rowID_PK, Category, Type, Amount
FROM @Transactions
WHERE Amount = '-25' AND Type LIKE 'DEP_%';

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 09:35:51

That was the sample which i had given.. you only have to run this much..!!!!



SELECT rowID_PK, Category, Type, Amount
FROM Transactions
WHERE Amount = -25 AND Type LIKE 'DEP_%';

Chirag

http://chirikworld.blogspot.com/

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 09:39:16

Haa haa thats it? Thank you Chirag I appeciate it.

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 10:12:15

I have just one more question, I have the following query:

INSERT INTO EMPLOYEES(Rtype, Amount, Refnumber, Date, Color)
SELECT Rtype, Amount, Refnumber, (TODAYS DATE), Color
FROM Employees
WHERE Rtype = 'RD';

Now if I want to create another row, except for the date, I would like to use todays date instead? What would I do for (Todays Date)?

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 10:31:54

The code I am using is:

INSERT INTO Employees(Rtype, Amount, Refnumber, Color, Date)
SELECT Rtype, 34, Refnumber, Color, Date(CURRENT_TIMESTAMP)
FROM Employees
WHERE Amount=-34 AND Rtype = 'AD';

It says date is not a current function

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 11:01:20

INSERT INTO Employees(Rtype, Amount, Refnumber, Color, Date)
SELECT Rtype, 34, Refnumber, Color, CURRENT_TIMESTAMP
FROM Employees
WHERE Amount=-34 AND Rtype = 'AD';

Madhivanan

Failing to plan is Planning to fail

markwest05
Starting Member

12 Posts

Posted - 2007-06-16 : 11:13:49

Thank you very much I appreciate it.....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources