Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

CAST as money does not work for varchar in SQL 2000

Posted on 2011-03-16
5
Medium Priority
?
467 Views
Last Modified: 2012-05-11
Hello experts:
I have the following SQL command issued from a C# Windows application.

SELECT count(distinct ACCOUNTNUMBER) FROM Extract WHERE (TransactionType = 'CASH_WD' OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT as money) > 0 and TransactionType = 'WITHDRAWAL' and cast(transactionamount as money) > '3000')) and TaxIdOrSSN = '043305700'

It works fine in SQL Server 2005 but it's failing in SQL Server 2000 with this message:
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.


0
Comment
Question by:lancerxe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 35151911
try this.
SELECT COUNT(DISTINCT ACCOUNTNUMBER) 
  FROM EXTRACT 
 WHERE (TransactionType = 'CASH_WD' 
         OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT AS MONEY) > 0 
             AND TransactionType = 'WITHDRAWAL' 
             AND CAST(transactionamount AS MONEY) > 3000)) 
       AND TaxIdOrSSN = '043305700'

Open in new window

0
 

Author Comment

by:lancerxe
ID: 35159751
I found the solution:

SELECT count(distinct ACCOUNTNUMBER) FROM Extract WHERE (TransactionType = 'CASH_WD' OR (CAST(CASHOUTAMOUNTINSPLITDEPOSIT as money) > 0 and TransactionType = 'WITHDRAWAL' and cast((transactionamount as money) > cast( '3000' as money))) and TaxIdOrSSN = '043305700'

Do I get points if I find it myself?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35159795
Did you try my post. You need not to convert a varchar to money again. You can directly compare by removing the quotes.
0
 

Author Comment

by:lancerxe
ID: 35160028
Sorry Sharath but I did not see any difference bewtween the original script l posted and yours.

Lance
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 35160399
The difference is I removed the quotes around 3000. There is no need to take 3000 as varchar and then convert it to numerical. You can directly compare with 3000.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question