SQL expression involving convert?? 2

[CrysUser]

Hi,

I need to write a SQL expression to convert the string value to a numeric value,

When I write,

{fn convert({string_field}, integer)}

in the SQL expression editor I get an error. I tried different values like number, int etc in place of integer in the above formula. Is there any specific way of writing the contants. Please advice.
Any help would be immensely appreciated.
Thank you.
(P.S, I am using CR9).

 

[Reebo99]

Use this link to explain what CONVERT does :

http://page.inf.fu-berlin.de/~ag-db..._docu/server.901/a90125/functions22.htm#77039

Try using :

TO_NUMBER({string_field})



Reebo
Scotland (Sunny with a Smile)

 

[synapsevampire]

You might consider posting what the error is.

Convert works fine, your syntax is incorrect, depending upon the database you're using, also Cast is an option.

cast({string_field} as int)

Convert syntax would be:

CONVERT(int, {string_field})

But this is database dependent, not Crystal syntax, and you didn't mention the database type or version.

-k

 

[CrysUser]

Thanks for your responses. Sorry for not giving the specifics.

The database is SQL server 2000.

I am not trying to write a SQL query, but I am trying to create a new SQL Expression Field within the Crystal reports(CR9). I need to get this into the select expert of the query, I could use the crystals tonumber({field}) but it is not pushing down the query. So, I was trying to create a SQL expression field using the convert function from the SQL expression editor. when I click on the convert function within the SQL expression editor in CR this the syntax that comes,

{fn Convert()}.

Any idea of how this function would work?
Thank you.

 

[synapsevampire]

SQL Expressions do NOT use Crystal syntax, they use the underlying databases language, which is what I guessed to be SQL Server, so the solution provided will work.

Did you try it?

If you have problems, consider sharing what you tried, and any errors received.

-k

 

[rhinok]

Instead of using the Convert function from the Functions Tree in Crystal Reports ({fn Convert(, )}), type in the function as you would normally use it in SQL Server 2k (CONVERT(CHAR(N),FIELD[,STYLE])).

I've used this within my reports, also against a SQL Server 2K DB.

 

[rosemaryl]

Dear CrysUser,

The Convert function in straight sql works as indicated in the posts above.

However, if you want to know the syntax that is proper for the {Fn Convert()} function in a sql expression, using your string to integer as an example it is:

Syntax:
{FN CONVERT(value_exp, data_type)}

{FN CONVERT('StringField', SQL_NUMERIC)}

Example:

{FN CONVERT('7', SQL_NUMERIC)}
will return 7.00 You can change the format to be currency etc by using the Format Field option.

The reason that it is different from SQL's Convert function is because this function is not a database specific function but rather an ODBC function. The driver does the conversion to the proper database syntax.

For information on valid datatypes please see the link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odappepr_5.asp

This link has great information on the Convert function with examples.

http://www.canaimasoft.com/f90sql/OnlineManual/Appendix5/Explicit data type conversion.htm

Hope this helps.

ro


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[CrysUser]

Thanks to everyone who replied.

Since I was trying to paste the {fn (convert())} from the function tree list I was stuck, but the post from Rosemaryl helped me get through.

As a matter of fact the Convert function also works as posted by rhinok and sv.

Thank you!

 

[synapsevampire]

Nice explanation, Ro*.

I didn't realize Crysuser was using the fn Convert for some reason, I never use any of those functions, I always use the native SQL.

Crysuser: Cast also works.

-k