Text to Number Conversion

[Smithsc]

I am trying to create a 'command' table in crystal where 1 of the fields is selecting part of another field:
SUBSTRING(GPSTNAR,13,3)) AS SCHEDULE.

This creates a text field which I need to convert to a number. I've tried putting various things before the SUBSTRING such as TO_NUMBER, TO NUMBER and NUMBER but none of these seem to work.

Can someone please help.

Many Thanks

 

[dgillz]

Are you trying to create SQL expression? Is a simple formula not acceptable? tonumber() as a crystal formula will work.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Smithsc]

Sorry, I should have been more specific.
I am trying to create a SQL expression to create a custom table which I can link to exsisting tables.

Select
GACCNUM as Account_Number,
GINPDAT as Input_Date,
TRANSACTION_CODE as Transaction_Code,
Trim(Left(GPSTNAR,11)) as Agreement,
*** SubString(GPSTNAR,13,3) as Schedule,
*** Right(GPSTNAR,2) as Termination,
GPSTAMT as Posting_Amount,
GANCDE1 as Analysis_Code_1,
GANCDE2 as Analysis_Code_2,
GJRNNUM as Journal_Number,
GJRNSEQ as Journal_Seq_No
From
GPSTING


The 2 lines that I've marked with *** are character fields and I need to convert them to numeric.

 

[dgillz]

How would this be linked to existing tables? By Account_Number? I would just add GPSTING to the report and write formulas to convert to a number. No SQL expressions required.


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[lbass]

What version of CR are you using and what is your datasource?

-LB

 

[Smithsc]

I need to link by Agreement, Schedule and Termination so I have to convert the tables.

I am running CR XI and my data source is an AS400 database

 

[dgillz]

Have you tried writing a formula and failed? I am still unsure why you seem to have tunnel vision on using a SQL expression to accomplish this.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[Smithsc]

How would I link GPSTING to the other tables The only fields that I can use to link on the other tables are Agreement, Schedule and Termination. The fields GPSTNAR in GPSTING contains this information and needs to be seperated before I can link them.

Thanks for your continued help and I'm sorry if I'm not explaining things clearly.

 

[Smithsc]

I've found the answer: The function to use is VALUE not forgetting (as I did) to put the number of decimal points at then end.

Many thanks for all your help.

 

[lbass]

Smithsc,

Can you please show the command that you ended up using so that others can benefit?

-LB

 

[Smithsc]

Of course:

Select
GACCNUM as Account_Number,
GINPDAT as Input_Date,
TRANSACTION_CODE as Transaction_Code,
Trim(Left(GPSTNAR,11)) as Agreement,
Value(SubString(GPSTNAR,13,3),0) as Schedule,
Value(Right(GPSTNAR,2),0) as Termination,
GPSTAMT as Posting_Amount,
GANCDE1 as Analysis_Code_1,
GANCDE2 as Analysis_Code_2,
GJRNNUM as Journal_Number,
GJRNSEQ as Journal_Seq_No
From
GPSTING

This will only work for tables from an AS400 datasource as other databases use different types of SQL.