Im trying to use this statement:

Select BillDate, SUM(Cast(Duration as Float)/60) as Duration,
SUM(Cast(Amount as Float)) as Amount from BVTF

Im getting a conversion error on the first item on the first field.
What is the correct way to write this ?
Thanks

Ben

Ben,

> Im trying to use this statement:
>
> Select BillDate, SUM(Cast(Duration as Float)/60) as Duration,
> SUM(Cast(Amount as Float)) as Amount from BVTF
>
> Im getting a conversion error on the first item on the first field.

Is that the complete statement?

SUM(Cast(Duration as Float)/60) as Duration should work without
conversion error, but BillDate should raise this error:

ElevateDB Error #700 An error was found in the statement at line 1 and
column 8 (Invalid expression "BillDate" found, GROUP BY clause required
for any column references)

Uli

This is the full statement

Select BillDate, SUM(Cast(Duration as Float)/60) as Duration,
SUM(Cast(Amount as Float)) as Amount from BVTF
where TranslationNumber = '8555357848'
Group by BillDate

and this is the error

ElevateDB Error #1011 An error occurred with the value 30
(A conversion error occurred with the value 30                       )

The value of 30 is the value of "Duration"  in the first record

Ben Sprei

"Uli Becker"  wrote in message
news:8B0A8320-4767-4C6E-864C-DA6C3FC492F4@news.elevatesoft.com...

Ben,

> Im trying to use this statement:
>
> Select BillDate, SUM(Cast(Duration as Float)/60) as Duration,
> SUM(Cast(Amount as Float)) as Amount from BVTF
>
> Im getting a conversion error on the first item on the first field.

Is that the complete statement?

SUM(Cast(Duration as Float)/60) as Duration should work without
conversion error, but BillDate should raise this error:

ElevateDB Error #700 An error was found in the statement at line 1 and
column 8 (Invalid expression "BillDate" found, GROUP BY clause required
for any column references)

Uli

Ben,

ElevateDB Error #1011 An error occurred with the value 30
(A conversion error occurred with the value 30                       )

The number of blanks after the "30" in the error message attracts my attention. Can it be, that duration is a VarChar field and contains blanks? Then you would have to trim the value first.

Uli

Ben


Can you post the table definition?


Roy Lambert

Thanks for your reply.  The field size is 25.
How would I add a TRIM command to the above SQL

Ben

"Uli Becker" wrote in message
news:6E0ECF4C-C7AF-443F-BCA2-3A916676726F@news.elevatesoft.com...

Ben,

ElevateDB Error #1011 An error occurred with the value 30
(A conversion error occurred with the value 30                       )

The number of blanks after the "30" in the error message attracts my
attention. Can it be, that duration is a VarChar field and contains blanks?
Then you would have to trim the value first.

Uli

Ben



Select BillDate, SUM(Cast(TRIM(TRAILING ' ' FROM Duration) as Float)/60) as Duration,
SUM(Cast(Amount as Float)) as Amount from BVTF
where TranslationNumber = '8555357848'
Group by BillDate

Roy Lambert

Thanks. This works.  I used RTrim instead to avoid such a long description
Ben

"Roy Lambert"  wrote in message
news:F99986FE-9B9E-4BF2-8DE6-DB7B3FBB60B8@news.elevatesoft.com...

Ben



Select BillDate, SUM(Cast(TRIM(TRAILING ' ' FROM Duration) as Float)/60) as
Duration,
SUM(Cast(Amount as Float)) as Amount from BVTF
where TranslationNumber = '8555357848'
Group by BillDate

Roy Lambert

Ben


I'm glad it worked, but if you have Duration defined as a CHAR or VARCHAR column I can't help but think you're going to get into more difficulties at some point when someone enters non-numeric characters in there.

Roy Lambert

At this point I realized duaration is entered as an integer so it does not
cause any problems on conversion.  I do have situations where its
entered in time format (00:00) noting hours and minutes.  The only way I was
thinking was using the subString function.  That would be fine if
all parts (before and after the colon) were the same length.  But since
simplicity is an impossibility I might have to run thru it programatically
converting every number to an integer or decimal - that is unless you have a
better idea.

Ben Sprei

"Roy Lambert"  wrote in message
news:56EB244A-F69A-44CD-A78A-EF9C3BCB16A8@news.elevatesoft.com...

Ben


I'm glad it worked, but if you have Duration defined as a CHAR or VARCHAR
column I can't help but think you're going to get into more difficulties at
some point when someone enters non-numeric characters in there.

Roy Lambert