Print Page | Close Window

Create Command using Left

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22807
Printed Date: 16 Apr 2024 at 2:11am


Topic: Create Command using Left
Posted By: chudok
Subject: Create Command using Left
Date 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]


Replies:
Posted By: kevlray
Date 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

Posted By: chudok
Date 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?

Posted By: kevlray
Date 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.

Posted By: chudok
Date 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?

Posted By: kevlray
Date 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.

Posted By: Sastry
Date 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


Print Page | Close Window