Converting numeric to text in command feature 2

[SarahS1]

Hi!

System info: Crystal XI, Microsoft SQL server, JDE database

I am trying to create an consignment inventory report pulling in the customer account name based on where the inventory currently resides.

I am trying to create a command and within the command convert a numeric field to text. The F4101.ABAN8 address field is stored as a number. The F41021.LILOCN field is stored as a string [20]. The LILOCN field does have alpha and numeric data in it, but the data that I am specifically looking at is all numeric, the LILOCN numeric data is the location of inventory and the 8 digit address number is found on the ABAN8 field. Once I have those two properly linked I will be able to pull in the name of the customer.

Below is the code for what I have tried... but it gives me a result of "6.2e+007" when what I am looking for would be 62XXXXXX.


SELECT "F0101"."ABAN8",
convert(char, "F0101"."ABAN8") As "AN8",
"F0101"."ABALPH", "F0101"."ABMCU"
FROM "JDE_EPD_REP"."CPRODDTA"."F0101" "F0101"
WHERE "F0101"."ABMCU"=N' 132' AND "F0101"."ABAN8">61999999

Thanks!

 

[Madawc]

I don't follow what you're trying to do in SQL. I think you could use a Crystal formula to get the same result, e.g.

Left({F0101.ABAN8}, 2) & "000000"

This could then be compared against whatever-it-is. Crystal would generate its own SQL from it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[IanWaterman]

Try changing your convert to cast

CAST("F0101"."ABAN8" AS char(8)) as "AN8"

Ian

 

[SarahS1]

Hi Madawc - I tried that within the Command box and it didn't work. Crystal didn't allow the & "000000" in the command box. The reason I am trying to do it within the command is so that I can link it to a table and then have data from the command and the table pull into the report based on a left join. Without the command, the data types in my 2 tables are incompatible. (String and number).

Ian - I tried what you suggested and it accepted it within the command box. When I refreshed my report I am receiving the error "Database Connector Error: '22003:[Microsoft][ODBC SQL Server Driver][SQL Server] Arithmetic overflow error for type varchar, value = 62001000.000000. [Database Vendor Code: 232]'

I also tried RTRIM() and that gave me a result of 6.2001e+007 and LTRIM() and that gave me a result of 6.2001e+007 as well.

Any other ideas?

 

[IanWaterman]

Not sure what is going wrong as I am not a SQL server expert

Try

CAST(CAST("F0101"."ABAN8" AS INT) AS char(8)) as "AN8"

Failing that post your query on the

Microsoft SQL Server Programming forum on tek-tips

Ian

 

[SarahS1]

Hi Ian,

It works! Thank-you! However, now the report runs quite slowly and speed will be a factor. So I will have to see what I can do about that. Thanks again!

 

[IanWaterman]

I assume you are now joining atable to this command.

Might run faster if you get your whole data set in the command.

ie join the other table into the command too.

Ian