How do I set a new field as a varchar in a command in Crystal

[AnnetteB1305]

I have a command I am using on a report I need to join a field on here to a field in a table from my database but it won't let me as they are not compatible data types. The field I am trying to link to is a varchar 32
My command is as follows:-
SELECT `opencall1`.`logdate`, `opencall1`.`closedate`, `opencall1`.`cust_id`, `opencall1`.`priority`, `equipmnt1`.`manufactur`, `pcdesc1`.`info`, `opencall1`.`callref`, `pcdesc1`.`code`,("EN"&mid(`pcdesc1`.`code`,3) ) as code2
FROM ((`swdata`.`opencall` `opencall1` LEFT OUTER JOIN `swdata`.`mdis_oc_asset` `mdis_oc_asset1` ON `opencall1`.`callref`=`mdis_oc_asset1`.`callref`) INNER JOIN `swdata`.`pcdesc` `pcdesc1` ON `opencall1`.`probcode`=`pcdesc1`.`code`) LEFT OUTER JOIN `swdata`.`equipmnt` `equipmnt1` ON `mdis_oc_asset1`.`asset_id`=`equipmnt1`.`equipid`

The field I want to link is the code 2 field but I'm unsure how to set it as a varchar as when I put this after the as code 2 it is throwing an error.

Any help would be appreciated

Thanks

Annette

 

[hilfy]

You don't say what type of database you're using nor do you tell us the error you're getting.

Having said that, I suspect that the issue is the "&". Try using '||' instead.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[AnnetteB1305]

Sorry it's a mysql version and that still hasn't done it I am getting an error saying the data types are not compatible. When I try and type the following

('EN'|| mid(`pcdesc1`.`code`,3) ) as code2 varchar (32)

It is saying incorrect syntax in the mysql

 

[CoSpringsGuy]

have you tried the cast function? Not sure the exact syntax for mysql or wether it will work with mid

CAST(mid(`pcdesc1`.`code`,3) AS CHAR) AS code2

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

Disregard .... I got more understanding after I read your related post

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Is {pcdesc1.code} a number or string datatype? Please just place it in your report, right click on it and browse to see what datatype is shown. If it is a number, then try this in your command:

"EN"& mid({fn convert(`pcdesc1`.`code`,sql_varchar)},3) as code2

-LB

 

[AnnetteB1305]

Hello,

It's a varchar, it's numeric and letters e.g. EN00-005-002-0000

Have tried your solution anyway but saying incorrect syntax, the issue is this is a very old version of mysql which they won't upgrade so a bit of a mare!

Thanks for your help

Annette


Thanks,

 

[lbass]

Did you try using either:

'EN'+substr(`pcdesc1`.`code`,3)

or

'EN'+{fn substring(`pcdesc1`.`code`,3)}

??

-LB

 

[AnnetteB1305]

Yes incorrect syntax for the mysql version error. I hadn't tried it with the || but just have and still the same

AB