| Author |
Message |
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
  
Topic: syntax error in sql query
Posted: 10 Jun 2015 at 4:19am |
|
Hi,
I get a syntax error in a command, I can't see what it is :
select
cast(substr(Code,7,9) as char(9)) as Ordercode from table
The actual problem : this field does appear in the field explorer, but not in the links tab, which means that Crystal doesn't recognize the data type. I tried to_char and to_text, that doesn't work. Now I'm trying with CAST
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jun 2015 at 4:35am |
|
what data type is CODE?
|
| IP Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 10 Jun 2015 at 5:35am |
|
It's supposed to be text. other substrings from Code do appear in the link tab, except OrderCode
to_number(subrstr(Code,10,2)) as Orderline doesn't give any syntax errors and shows in the link tab.
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jun 2015 at 5:40am |
and you tried without a cast or convert?
substr(Code,7,9) as Ordercode from table
Edited by DBlank - 10 Jun 2015 at 5:51am
|
| IP Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 10 Jun 2015 at 7:04am |
|
Yes, that doesn't give any syntax errors, but OrderCode doesn't appear in the links tab, I need that field to link to a table
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jun 2015 at 7:12am |
|
what is your source data type?
|
| IP Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 10 Jun 2015 at 7:50am |
|
The report (not written by me) connects with something ending with .uld. I don't know what that is. I was told it was one long string (field Code)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Jun 2015 at 8:11am |
I am not sure as my testing has not giving me any issues in a command object and a substring and using the resulting value as linkable field.
Maybe try a varchar?
CONVERT(Varchar(9),substring(Code,7,9)) as Ordercode from table
|
| IP Logged |
|
Macavity
Groupie
Joined: 24 Sep 2012
Online Status: Offline
Posts: 93
|
Posted: 11 Jun 2015 at 1:38am |
|
Convert doesn't work either. I found a solution. I created a subreport with the table that has field Code. Unfortunately in the subreport link Ordercode didn't appear (frustrating). I created a formula in the subreport :
command.ordercode + " ". That field appeared in the subreport link. Problem solved, not very neat, but oh well.....
|
| IP Logged |
|
|
|
