Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design  
Message Icon Topic: Create Command using Left Post Reply Post New Topic
Author Message
chudok
Groupie
Groupie


Joined: 18 May 2010
Online Status: Offline
Posts: 62
Quote chudok Replybullet Topic: Create Command using Left
    Posted: 05 Aug 2019 at 4:05am
I have tried several different ways of getting the Left to give me only the data I need. Basically i need to create a table that has 2 fields. One original field and the other one only taking the left so many characters. This way I can link to tables together by using this "new" command table.

Example table one has "ABC" table 2 has "ABCD", i need to created something that will look only at the left 3 characters and link them. So the command i was creating was taking the table that has the "ABCD" and trying to create 2 fields, on that has the full "ABCD" and another field that only takes the left 3 of "ABCD" then i could link my original tables together with the command in the middle.

I hope i am making sense.

Select
left(Sort, 3) AS 'SortQ',
from AP_Vendor;

Here is one example of trying to get just the left 3 from the field.

the error that returns is "Failed to retrieve data from the database. Details: 37000[ProvideX][ODBC Driver] Expected lexical element not found: <identifer> [database vendor code: 1015]
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1484
Quote kevlray Replybullet Posted: 05 Aug 2019 at 4:59am
Not sure what database you are using, but something I have done in MS-SQL
looks like this

select something
from
table1
inner join table2 on left(table2.field,4) = table1.field
IP IP Logged
chudok
Groupie
Groupie


Joined: 18 May 2010
Online Status: Offline
Posts: 62
Quote chudok Replybullet Posted: 05 Aug 2019 at 6:12am
I am needing to do this in Crystal Reports. The data source is a proprietary version from Sage 100. So is there a way to do this in Crystal?   This is why i was trying to create a table using the Command.

What about using DECLARE "DATA" AS CHAR(8)
???

How do i pull out only 8 characters of data from a field using the Command in Data Expert?

Edited by chudok - 05 Aug 2019 at 9:58am
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1484
Quote kevlray Replybullet Posted: 06 Aug 2019 at 10:02am
I do not know the functions for Sage 100.  It would require some research to determine what functions are available.
IP IP Logged
chudok
Groupie
Groupie


Joined: 18 May 2010
Online Status: Offline
Posts: 62
Quote chudok Replybullet Posted: 08 Aug 2019 at 1:56am
At this point i am even trying to figure out the code for Crystal.

I wish i could drop a screenshot. But this is where i am going. Database tab - database expert - select the connection - the first thing in the connection is Add Command. This is where i am trying to add this left(data) to.

does anyone know if you can create a statement that will only pull the left 8 characters from a field?
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1484
Quote kevlray Replybullet Posted: 12 Aug 2019 at 4:58am
In the command you have to write a valid SQL statement (database specific).  Since I have never worked on a SAGE database.  I do not know what would be valid code.
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 12 Aug 2019 at 10:25pm
Hi,

Just check this SAP note, which may help you to join both tables:

https://apps.support.sap.com/sap/support/knowledge/public/en/1212698

Thanks,
Sastry
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.