Chain Multiplication on a column

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

Quote:

Originally Posted by janet04

Can someone point me out a function that return the multiplication of all row contents from a column?

It should work like the sum() function, but instead of the summary.. it will return the chain multiplication result. Here is what I need it to do.

x
---
4
2
3
7

I need a function "multiply" so that when I issued "Select multiply(x) ...group by..." It would return 168 which is 4*2*3*7

I need to do this in DB2, I checked the DB2 UDF, but looks like there is no simple way to create a customized column function like this.

I'll be really appreciated if someone can help.

--------------
Reply : select exp(sum(ln(val_num))) from test_multiply;|||select exp(sum(ln(val_num))) from test_multiply;

It will solve your purpose

Cell Formatting

Hi,

On importing an XML file using VBA I/O function, I need to apply rules/format to specific Excel 2003 .Cells.

XML
<Column FieldName="ProductReference" FieldNumber="1">
<DataType>character</DataType>
<Format>x(20)</Format>
<Restrictions/>
<Mandatory>true</Mandatory>
<PermittedValues/>
</Column>

If the above XML-lines (strings) are read, I need to apply the following rules/formats to the identified .Cells(x,1):

1.
.Columns("A").NumberFormat = "General" or "Text" ??
2.
That when inputting a value, the user is restricted to 20 Characters
3.
That this is a Mandatory entry field

These values obviously change as per XML Output. Can anyone help, please?Is this related to Crystal Reports?

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECT NON EMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ON COLUMNS, NON EMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ON COLUMNS FROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ON COLUMNS FROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ON COLUMNS FROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ON COLUMNS FROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

Cdate conversion vs data from a cube

Hello,

I have a problem with date conversion when the date is coming from a cube.

I mean the function "=cdate(Fields!Signature_Date.Value)" works fine when the date is always fill in, but when my record is equal to nothing, I have got the value "#error" in my report...

Which is the best way to avoid to display this value in my report?

I have used the code "=iif(Fields!Signature_Date.Value = nothing, nothing, cdate(Fields!Signature_Date.Value))" but it doesn't work neither...

Please, if my only way to resolve that is to convert the field in the MDX statement, could you give me a code example...?

Thanks,

Guillaume

This works for me on cubes with a parameter.

i. ="[Time].[Batch Date].&[" + Format(CDate(Parameters!TimeBatchDate.Value), "yyyy-MM-ddT00:00:00") + "]"

|||

Thanks for your reply!

Unfortunately I should be too new in MDX because I don't know where to enter the format conversion without having an error...

My MDX statement in my report is :

SELECTNONEMPTY { [Measures].[Forecast Turnover], [Measures].[Forecast Sub Contracting] } ONCOLUMNS, NONEMPTY { ([Dim BV Organisation].[Country].[Country].ALLMEMBERS * [Dim BV Organisation].[Business Unit].[Business Unit].ALLMEMBERS * [Dim BV Organisation].[Reporting Line].[Reporting Line].ALLMEMBERS * [Dim BV Organisation].[Product Line].[Product Line].ALLMEMBERS * [Fact Contract].[Contract Type].[Contract Type].ALLMEMBERS * [Fact Contract].[Contract Description 1].[Contract Description 1].ALLMEMBERS * [Fact Contract].[Contract Description 2].[Contract Description 2].ALLMEMBERS * [Dim Client].[Client Number].[Client Number].ALLMEMBERS * [Dim Client].[Client Name].[Client Name].ALLMEMBERS * [Fact Contract].[Signature Date].[Signature Date].ALLMEMBERS * [Fact Contract].[End Of Plan Date].[End Of Plan Date].ALLMEMBERS * [Fact Contract].[Contract Status].[Contract Status].ALLMEMBERS * [Fact Contract].[Order Taken By].[Order Taken By].ALLMEMBERS * [Fact Contract].[Contract Number].[Contract Number].ALLMEMBERS * [Fact Contract].[Modified Date 1st-3rd Screen].[Modified Date 1st-3rd Screen].ALLMEMBERS * [Fact Contract].[Modified Date 2nd Screen].[Modified Date 2nd Screen].ALLMEMBERS * [Dim Time].[Year].[Year].ALLMEMBERS * [Dim Time].[Month].[Month].ALLMEMBERS * [Dim Time].[Date].[Date].ALLMEMBERS ) } DIMENSIONPROPERTIESMEMBER_CAPTION, MEMBER_UNIQUE_NAMEONROWSFROM ( SELECT ( -{ [Fact Contract].[Contract Type].&[B2], [Fact Contract].[Contract Type].&[B5], [Fact Contract].[Contract Type].&[BV], [Fact Contract].[Contract Type].&[X1], [Fact Contract].[Contract Type].&[X2], [Fact Contract].[Contract Type].&[C5] } ) ONCOLUMNSFROM ( SELECT ( { [Fact Contract].[Contract Status].&[], [Fact Contract].[Contract Status].&[2], [Fact Contract].[Contract Status].&[3], [Fact Contract].[Contract Status].&[4] } ) ONCOLUMNSFROM ( SELECT ( { [Dim BV Organisation].[Reporting Line].&[I&F], [Dim BV Organisation].[Reporting Line].&[INF] } ) ONCOLUMNSFROM ( SELECT ( STRTOMEMBER(@.FromDimTimeMonth, CONSTRAINED) : STRTOMEMBER(@.ToDimTimeMonth, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED) ) ONCOLUMNSFROM ( SELECT ( STRTOSET(@.DimBVOrganisationCountry, CONSTRAINED) ) ONCOLUMNSFROM [BVOMDW])))))) WHERE ( IIF( STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED).Count = 1, STRTOSET(@.DimBVOrganisationByProduct, CONSTRAINED), [Dim BV Organisation].[By Product].currentmember ) ) CELLPROPERTIESVALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

I don't know if I have to format my date at this level, or how to do that...

Please could you specify me where I need to enter the format conversion?

Thanks,

Guillaume

|||

Now I understand how to convert a field to date type when the field is a parameter (when there is STRTOSET in the MDX statement):

"="[Fact Contract].[Signature Date].&[" & cdate(Parameters!FactContractSignatureDate.Value) & "]""

However the field I want to convert is not a parameter. I just want to convert a field which belong to a table.

So I think it is not necessary to convert the date in MDX?

There is another way to proceed?

Guillaume

|||

Hi,

I will try to explain more accurately my problem because I really need it to be resolved...

I have a field "End of Plan Date" which displays date like this: "1999-07-28 00:00:00". I need to convert this date and apply the format "d MM, yyyy" to this field.

So far when I used in Reporting Services the formula "format(cdate(Fields!End_Of_Plan_Date.Value),"d MM, yyyy")", the result is good if the field "Fields!End_Of_Plan_Date.Value" is not NULL. If it is NULL I get the value "#error" in my report.

This is what I really want to avoid and I need to find a way to convert my field without having "#error" displays when the date is null.

I'm sure it's something easy to do but so far I have no clue...

Thanks,

Guillaume

|||

Guillaume,

if i understand your problem correctly, you are having problems displaying dates in the report when retrieved with your MDX statement, due to some dates being null.

You have two solutions for this:

- use a COALESCE EMPTY in your MDX to use some default date or value when the date field is empty

- surround your CDate function with a IsNothing check, like this (pardon any syntax errors i wrote this from memory):

Code Snippet

IIf ( IsNothing(myDateField),

"some default value",

CDate( IIf( IsNothing(myDateField), "1/1/1900", myDateField))

)

Just substitute myDateField with your dataset field. The "1/1/1900" is only there to keep the statement valid, as IIf evaluates all the arguments no matter which one is finally chosen, it can be anything as long as it is a valid date string (that particular date will never get returned though, so it can literally be anything you want).

Hope that helps!

Causes of allocation errors in tempdb

Hello,
Our company produces some software using SQL 7.0 as a back end, one function
of which is the running of several reports overnight. These reports make
use of temporary tables which can cause tempdb to grow to a few hundred
megabytes.
On a handful of our customers' servers, we have seen massive growth in
tempdb (usually consuming all available disk space - several Gb) when
running these reports. On running DBCC CHECKDB against tempdb we have
noticed thousands of allocation errors not associated with any particular
object. I have included details of some of these errors later on with some
commentary.
The first thought has been physical disk errors. All our customers' servers
run on either RAID1 or RAID5 arrays and we have run chkdsk with no errors
found. There have been no errors in the Windows event log suggesting
impending disk failure either.
The other databases (including system databases) have no errors when checked
with DBCC CHECKDB.
If there are any other factors that could lead to this situation I'd
appreciate it if you could let me know.
Many thanks,
John McLusky.
Could not allocate space for object '(SYSTEM table id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full.
> 1. When run on this system, the temporary database had grown so large that
> there was no room for further expansion.
Server: Msg 8999, Level 16, State 1, Line 1
Database tempdb allocation errors prevent further CHECKDB processing.
> 2. I suspect that this is related to the errors that follow shortly!
Server: Msg 1101, Level 17, State 1, Line 1
Could not allocate new page for database 'tempdb'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth.
> 3. as number 1.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:116) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:197) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:198) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
Page (1:208) in database ID 2 is allocated in the SGAM (1:3) and PFS (1:1),
but was not allocated in any IAM. PFS flags 'IAM_PG MIXED_EXT ALLOCATED
0_PCT_FULL'.
Server: Msg 8906, Level 16, State 1, Line 1
> 4. Several more otherwise identical errors with different page numbers
> follow. The SGAM and PFS IDs are identical.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:120) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:128) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:136) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:144) in database ID 2 is marked allocated in the GAM, but no SGAM
or IAM has allocated it.
> 5. Again, many more of these with only the extent number varying.
Server: Msg 8986, Level 16, State 1, Line 1
Too many errors found (201) for object ID 0. To see all error messages rerun
the statement using "WITH ALL_ERRORMSGS".
CHECKDB found 28208 allocation errors and 0 consistency errors not
associated with any single object.
CHECKDB found 28208 allocation errors and 0 consistency errors in database
'tempdb'.
repair_allow_data_loss is the minimum repair level for the errors found by
DBCC CHECKDB (tempdb ).
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.Hi John ,
From your descriptions, I understood that your tempdb is growing unexpected
and allocation errors with DBCC CHECKDB. Have I understood you? Correct me
if I was wrong.
First of all, have you upgraded your SQL Server to latest update?
Then, you could perform stop SQL Server services, delete existing
TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is recreated
every time service is restarted.
Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it is
clean now.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You're correct.
We have tried recreating tempdb by stopping SQL, deleting the files and then
restarting. Unfortunately once the nightly reports run again the same
problem recurs.
I am fairly sure that SQL 7 is on the latest service pack, but this will be
checked on Monday.
Before the reports run, DBCC CHECKDB comes back clean, but we haven't tried
DBCC NEWALLOC. Although I am out of the office next week my colleagues are
monitoring this thread and will try your advice.
Thanks,
John.
Michael Cheng [MSFT] wrote:
> Hi John ,
> From your descriptions, I understood that your tempdb is growing
> unexpected and allocation errors with DBCC CHECKDB. Have I understood
> you? Correct me if I was wrong.
> First of all, have you upgraded your SQL Server to latest update?
> Then, you could perform stop SQL Server services, delete existing
> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
> recreated every time service is restarted.
> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
> is clean now.
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader
> so that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.|||Please increase the tempdb size so that you do not run out of space when
running the big reports. You can let tempdb auto grow or preallocate a large
size (the preferred method for high performance).
The error "Could not allocate space for object '(SYSTEM table
id: -996385922)' in
database 'TEMPDB' because the 'DEFAULT' filegroup is full." indicates that
you need to increase space in tempdb.
When you have enough space, the problem probably will go away. If not,
please contact microsoft tech support.
DBCC checkdb (tempdb) itself needs more space in tempdb. So you need to have
enough space as well.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"John McLusky" <jmclusky@.community.nospam> wrote in message
news:e7buAAWIFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi Michael,
> You're correct.
> We have tried recreating tempdb by stopping SQL, deleting the files and
> then restarting. Unfortunately once the nightly reports run again the
> same problem recurs.
> I am fairly sure that SQL 7 is on the latest service pack, but this will
> be checked on Monday.
> Before the reports run, DBCC CHECKDB comes back clean, but we haven't
> tried DBCC NEWALLOC. Although I am out of the office next week my
> colleagues are monitoring this thread and will try your advice.
> Thanks,
> John.
>
> Michael Cheng [MSFT] wrote:
>> Hi John ,
>> From your descriptions, I understood that your tempdb is growing
>> unexpected and allocation errors with DBCC CHECKDB. Have I understood
>> you? Correct me if I was wrong.
>> First of all, have you upgraded your SQL Server to latest update?
>> Then, you could perform stop SQL Server services, delete existing
>> TEMPDB.MDF and TEMPDBLOG.LDF and restart SQL Server. Tempdb is
>> recreated every time service is restarted.
>> Run dbcc checkdb and dbcc newalloc on tempdb, let me know whether it
>> is clean now.
>>
>> Sincerely yours,
>> Michael Cheng
>> Microsoft Online Partner Support
>> When responding to posts, please "Reply to Group" via your newsreader
>> so that others may learn and benefit from your issue.
>> =====================================================>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>

Cast Question - Converting Datetime into Date

Hi guys,
I knew that the Function Cast can do this but I tried a lot and I dont want to use the MONTH, YEAR, DAY function.

I have a smalldatetime field with a value of this 12/18/2004 4:02:00 PM
I just like to see it like this 12/18/2004

Hope you can help me up. Thanks.
-vinceSELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?|||declare @.val datetime
set @.val='12/18/2005 11:00:00'
select @.val, convert(char(10),@.val,101)|||SELECT CONVERT(varchar(10),GetDate(),101)

Got Books Online (BOL)?

I have it here.. Thanks a lot...Sometimes, when you have a lot of things in your mind, you cant see exactly the answer which is sometimes in your face already. ;)

hehehe

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....

CAST function problem

Hi,

I am facing problem while using CAST function. Here i am using SUM function inside CAST function and assigning the returned value to a variable which is of varchar datatype of length 100. The variable which i am using inside SUM function is big int.

I am doing like this:

@.hour = (select CAST(SUM(totaltime) as varchar(100) from 'Table name'

It was getting error like:

Unable to convert varchar datatype to bigint.

How can i resolve this problem..
Regards,
SaratStart by posting code that will actually compile... Also, give us the data types of each column you are looking at.|||Actually the following code compiles and works as I would expect:

Create Table #X(x bigint)

Insert Into #X Values(1)

Insert Into #X Values(2)

Declare @.hour varchar(100)

Set @.hour = (select CAST(SUM(x) as varchar(100)) from #X)

Select @.hour

Drop Table #X

So I don't really see the problem... I pretty-much copied and pasted your line

|||Just wondering - why not use:

select @.hour = CAST(SUM(x) as varchar(100)) from #X

rather than: SET @.hour = (SELECT ....

CAST error

I am keep getting error when I use CAST function in Expression.

If I run (DT_I4)("1") I got following error.

TITLE: Expression Builder

Cannot convert expression value to property type.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.42&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=CannotAssignExpressionToProperty&LinkId=20476

ADDITIONAL INFORMATION:

Cannot convert 'System.Int32' to 'System.Int64'.

BUTTONS:

OK

This says to me that the target of the expression, such as a property, is of type Int64, or DT_I8, but your value is only Int32 Try the following instead-

(DT_I8)("1")