SQL Query Results

[bfountaine]

I am running an sql query that brings back a currency value eg 34.45 if I have a value that comes back as a round number eg 155.00, when I then try and enter the value as keystrokes, it doesn't put the trailing 0's, so will only type 155. Has anyone come across this and is there a way I can make it type the trailing 0's too.

[forumsupport]

Hello,

Can you please tell us what database are you running the query against?

One thing you can try is check if the dataset column variable includes any dot (.) as follows and write the trailing zeros accordingly

If $Dataset Column(5)$ Does Not Include . Then
Keystrokes $Dataset Column(5)$.00
End If
If $Dataset Column(5)$ Includes . Then
Keystrokes $Dataset Column(5)$
End If

Hope that helps.

[bfountaine]

The database I'm running the query against is Firebird 1.5, the field in question is defined as a float. I've got a third party ODBC driver from IBPhoenix http://www.ibphoenix.com/main.nfs?page=ibp_60_odbc

Will try the suggestion you've made and see if it does the trick,

Thanks

[bfountaine]

I've found it truncates any trailing 0's so a value of 2.50 is coming through as 2.5. The

If $Dataset Column(5)$ Includes . Then...

suggestion made did help for values such as 10.00, any similar suggestions of how I check if I need the trailing 0?

[bfountaine]

Further information to this issue. I have tried using cast in my sql and casting it to a decimal with no luck. I've written a script that says select 2.50 from [tablename] and then logged to a file the value in dataset column 1 and I still get 2.5 can I test if there is only 1 character after the decimal place? I can check if the value includes the decimal, but can I check how long a variable is?

[bfountaine]

finally figured out a way around this so for those of you who may also have this problem. In my sql I cast to numeric(8,2) and then cast that to varchar(20) in the following way:

select cast(cast(field as numeric(8,2)) as varchar(20)) from table

I'm fairly certain its a problem with the odbc driver rather than Automation Anywhere as running the same original query through an odbc connection under another piece of software gave me the same truncation.

[Vote Sizing Steve]

I was also having a series of problems with my mySQL odbc connector (MyODBC ver 3.5.11) getting the loop to get past the first record, and by working it out with tech support I found the work-around of making a table in msAccess and then connecting to it through the Jet connection instead.