November 1, 2002 at 12:48 pm
When I try to compare value of decimal 9(15,2) column source, the code parses great, but I get a type mismatch error when it runs:
original:
Function Main()
c_amt = DTSSource("WO_c_amt")
DTSDestination("new_c_amt") = c_amt
IF isnull( c_amt ) or isempty( c_amt ) then
DTSDestination("lHasAmt_flag") = 0
else
IF c_amt > 0.0 THEN
DTSDestination("lHasAmt_flag") = 1
ELSE
DTSDestination("lHasAmt_flag") = 0
END IF
end if
Main = DTSTransformStat_OK
End Function
The line with type mismatch error is:
IF c_amt > 0.0 THEN
I've even gone so far as to define decimal global variables, one of which is set to decimal 0.01!
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
Function Main()
DTSGlobalVariables("c_amt").Value = DTSSource("WO_c_amt")
DTSDestination("c_amt") = DTSGlobalVariables("c_amt").Value
IF isnull( DTSGlobalVariables("c_amt").Value ) or isempty( DTSGlobalVariables("c_amt").Value ) then
DTSDestination("lHasAmt_flag") = 0
else
IF DTSGlobalVariables("c_amt").Value > DTSGlobalVariables("catest").Value THEN
DTSDestination("lHasAmt_flag") = 1
ELSE
DTSDestination("lHasAmt_flag") = 0
END IF
end if
Main = DTSTransformStat_OK
End Function
If I check the type of c_amt local variable before the comparison error line, it tells me type 14, defined in oledb.h, under enum DBTYPEENUM as DBTYPE_DECIMAL = 14, but when I look VarType return codes in VBScript reference help, 14 isn't included!!!
Help! Why isn't Decimal included? What should I convert it to? Can I even do that?
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
November 4, 2002 at 3:29 am
Hi did you check the datatype for the column WO_c_amt ?. I think you could just use
if c_amt > 0 then . In vb script all variables are variant.
November 4, 2002 at 4:48 am
Got to love documentation. DBTYPE_DECIMAL is an ADO/OLE data type and not really a VBScript thing. Go to http://msdn.microsoft.com and search for it for help.
Another great problem with VBScript is the fact the data is stored in a variant and by default cannot work with numeric datatypes directly. You can, however, define the subtype thru the CDbl function so it can then work with them. Now replace the bad line with
IF CDbl(c_amt) > CDbl(0.0) THEN
and see if does not work then. Should work and here is the KB article on this issue.
quote:
PRB: Use of Math Operations with DTS and Numeric Data TypesID: Q252439
--------------------------------------------------------------------------------
The information in this article applies to:
Microsoft SQL Server version 7.0
Microsoft SQL Server 2000
--------------------------------------------------------------------------------
SYMPTOMS
When you try to use ActiveX transformations with Microsoft SQL Server Data Transformation Services (DTS) where the source column is a numeric data type, the following error message can occur when you perform mathematical operations:
Error Code: 0
Error Source: Microsoft VBScript runtime error
Error Description: Type mismatch
CAUSE
Microsoft Visual Basic® Scripting Edition (VBScript) does not support the numeric data type and is unable to handle the mathematical operation of the two operands.
WORKAROUND
To work around this problem, use the Cast function to convert the unsupported data type into a data type that a script written in VBScript can support.
You can use the following T-SQL script and VBScript to demonstrate the behavior:
CREATE TABLE dtstest (
Rate1numeric(27,23),
Rate2numeric(27,23)
)
go
INSERT INTO dtstest VALUES (0.081625, 0.081625)
go
Create a DTS transformation between dtstest and a new table. In the transformation, select VBScript ActiveX transformation, and the use the following code snippet to perform the transformation:
Function Main()
Dim Rate
REM The following statement allows VBScript to perform the
REM expression against a numeric data type.
REMRate = CDbl(DTSSource("Rate1")) / CDbl(DTSSource("Rate2"))
REM The following statement causes the error because VBScript
REM is not able to handle the numeric data type. Replace the
REM following statement with the preceding statement.
Rate = DTSSource("Rate1") / DTSSource("Rate2")
DTSDestination("Rate1") = Rate
DTSDestination("Rate2") = DTSSource("Rate2")
Main = DTSTransformStat_OK
End Function
Additional query words:
Keywords : kbSQLServ700 kbSQLServ2000
Version : winnt:7.0; :
Platform : winnt
Issue type : kbprb
Technology : kbvcSearch
Edited by - antares686 on 11/04/2002 04:50:33 AM
November 4, 2002 at 6:48 am
Ha! That wasn't an RTFM response was it? Dear, dear I'm so embarrased. Thanks for the help though - I really appreciate it.
Great Thanks (even if it was a smack upside the head..)
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
CaperSailor
Outside of a dog, a book is a man's best friend. Inside of a dog, it's too dark to read." Groucho Marx, 1890-1977
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply