Is there a way to create a link using part of a field?

If you use a COMMAND you may be able to link as

OtherTable.Field  LIKE ThisTable.Field

Another way would be

Mid({YourField},11,9) = {OtherTable.Field}

mlmcc

OK, to complicate this just a tad, I need to link that field to more than one table ie the text has a marker at the front of it which could be either 1 to 3 tables that need to be linked. The position of the data I want to link will be the same, however there could be more than one table to link to. How do I do this in the 'add command' and what type of link would I use?

ie as follows:

GL Table
warehouse/800000661/422/10 etc
warehous2/900000221/303/10

The first part of the text represents the table I need to link to. I hope that makes sense.

PS I went into Add Command and tried to use your formula:

Mid({YourField},11,9) = {OtherTable.Field}

however I got the following error:

"Failed to retrieve data from database.Details 42000 [Microsoft] [ODBC SQL Server Driver] Syntax error or Access Violation."

I think you are just going to have to try things using substring commands.

It may be that it isn't possible because of the database you are using and limitations of Crystal.

You may have to look into the substring commands available through MS SQL.  Also some database drivers may not be able to pass clauses like that to the database.

As far as whch table you link to that will almost certainly have to be handled in Crystal using a subreport.

If a given record might require linking to one of several tables then the best you can do is a subreport.  I doubt you can write SQL that is sophisticated enough to decipher which table to use as well as which part of the field.
I would say with that wrinkle a set of subreports would be the best bet and sections suppressed based on which subreport has the correct table.

mlmcc

Could it be just a syntax error that is causing the problem?

Would I need something like...

Select t_refr
From GLTABLE

Where

mid({GLTABLE.t_refr},10,9) = {WAREHOUSE.t_orno}

It could be but if warehouse has to change to warehous2 based on the data then you could have a problem.

I don't remember the MS SQL substring functions but if you try using that and it doesn't work then either it won't work or the ODBC connection won't allow it.

mlmcc

If you use the Add Command route for your data source then you have to write a complete sql statement in the SQL syntax of your database.

Optional joins cannot be implemented in CR so you would have to do that in your Command SQL, assuming your database SQL supports such a feature.

For MS SQL, replace Mid with SUBSTRING.

 As for your query, off the top of my head, maybe something like this:

SELECT <columns from GLTABLE>,
<columns from warehouse, which will be null when that table is not linked>,
<columns from warehous2, which will be null when that table is not linked>,
<columns from third table, which will be null when that table is not linked>
FROM GLTABLE

LEFT OUTER JOIN
WAREHOUSE W ON
SUBSTRING (GLTABLE.t_refr,1,9) = 'warehouse' AND
SUBSTRING (GLTABLE.t_refr,11,9) = W.t_orno

LEFT OUTER JOIN
WAREHOUS2 W2 ON
SUBSTRING (GLTABLE.t_refr,1,9) = 'warehous2' AND
SUBSTRING (GLTABLE.t_refr,11,9) = W2.link_field

LEFT OUTER JOIN
THIRD_TABLE T3 ON
SUBSTRING (GLTABLE.t_refr,1,9) = 'thirdtabl' AND
SUBSTRING (GLTABLE.t_refr,11,9) = T3.link_field


 I don't know how efficient it would be, but I think it would work, assuming that the t_refr strings are consistent (always start with the same characters to determine which table to use, and always have the "key" characters for each table in the same positions).

 James

James, I'm a bit confused about this part of the code:

SELECT <columns from GLTABLE>,
<columns from warehouse, which will be null when that table is not linked>,
<columns from warehous2, which will be null when that table is not linked>,
<columns from third table, which will be null when that table is not linked>
FROM GLTABLE

What exactly do I put in here? Do I need to select all of the fields I want to appear in the report?

Many thanks!

Yeah, that's the idea.  With a CR Command, you're creating the query for the report manually, rather than using CR's interface to add tables to the report and link them together.  It takes more work, but it gives you complete control over the query, so you can do more "advanced" things, like JOIN the tables based on a substring in a field.

 If you've got a report that's reading GLTABLE and has the fields that you need on the report, you could start by going to Database > "Show SQL Query" in that report and copying what's there.  That should give you a basic query with the fields that you're using on that report.  In theory, it's basically just a matter of taking that query and adding the other tables to it, as shown above.

 It should be fairly simple, but, of course, it depends on how comfortable you are writing your own queries.

 James

Hmmn. I'm a bit of a novice at this James!! I've copied the SQL Query into 'Add Command' and tried to add your code into it, but I'm getting a Syntax error near the word left. Can you have a look at the SQL query and add the code for the join as you would write it?

The GLTABLE is actually called ttfgld106300 and the warehouse table is actually called twhina122300. (I renamed them in my postings for simplicity sake!)


 SELECT "ttfgld106300"."t_otyp", "ttfgld106300"."t_odoc", "ttfgld106300"."t_leac", "ttfgld106300"."t_dcdt", "ttfgld106300"."t_dim1", "ttfgld106300"."t_dim4", "ttfgld106300"."t_refr", "ttfgld106300"."t_amnt", "ttfgld106300"."t_dbcr", "ttfgld106300"."t_fprd", "ttfgld106300"."t_fyer"
 FROM   "erplndb"."dbo"."ttfgld106300" "ttfgld106300"
 WHERE  "ttfgld106300"."t_fprd"=6 AND "ttfgld106300"."t_fyer"=2011 AND ("ttfgld106300"."t_leac"='1103' OR "ttfgld106300"."t_leac"='1104' OR "ttfgld106300"."t_leac"='1112' OR "ttfgld106300"."t_leac"='1116' OR "ttfgld106300"."t_leac"='1120' OR "ttfgld106300"."t_leac"='1127' OR "ttfgld106300"."t_leac"='1131' OR "ttfgld106300"."t_leac"='1170' OR "ttfgld106300"."t_leac"='1170I')


Many thanks!

I don't see the LEFT

You could also try the long OR as

"ttfgld106300"."t_leac" IN ['1103', '1104', '1112', ....]

mlmcc

 mlmcc,

 There's no LEFT because that's the base query, before she tried to add the JOIN's.


 spectrum17,

 While it has nothing to do with trying to JOIN with the other tables, mlmcc has a point about the OR's.  You could replace them all with IN, which would simplify the WHERE quite a bit.  But you'd use () around the values instead of [].  I went ahead and included that change in the code below.

 Here's your code with a JOIN added for twhina122300.  You'd replace T1.field1 and T1.field2 with the fields that you need from twhina122300.

SELECT "ttfgld106300"."t_otyp", "ttfgld106300"."t_odoc",
"ttfgld106300"."t_leac", "ttfgld106300"."t_dcdt",
"ttfgld106300"."t_dim1", "ttfgld106300"."t_dim4",
"ttfgld106300"."t_refr", "ttfgld106300"."t_amnt",
"ttfgld106300"."t_dbcr", "ttfgld106300"."t_fprd",
"ttfgld106300"."t_fyer",
T1.field1, T1.field2
FROM   "erplndb"."dbo"."ttfgld106300" "ttfgld106300"

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,9) = 'warehouse' AND
SUBSTRING (ttfgld106300.t_refr,11,9) = T1.t_orno

WHERE
"ttfgld106300"."t_fprd"=6 AND "ttfgld106300"."t_fyer"=2011 AND
"ttfgld106300"."t_leac" IN ('1103','1104','1112','1116','1120','1127','1131','1170','1170I')

Thanks James!

I just wanted to clarify something though. The string that we are converting here (ttfgld106300.t_refr) looks like this:

whina122/800000659/114/10/100650

ie the start of the table name is contained in the string that is held in ttfgld106300.t_refr.

I'm a bit lost re the T1 that you have here:

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,9) = 'warehouse' AND
SUBSTRING (ttfgld106300.t_refr,11,9) = T1.t_orno

and also what the 'warehouse' does in the above formula.

Just to summarise, and to make sure we are on the same page; the fields that I need to link are ttfgld106300.t_refr and twhina122300.t_orno (need to link on the (11,9) position.

After this is linked I then need to bring in the field twhina122300.orno, twhina122300.item and twhina122300.rvrs.

Once I get this one table link working I'd then like to add one more table link ie ttfgld106300.t_refr to twhina110.t_itid. and bring in a couple of fields from the twhina110 table as well.

I know this is a huge ask (?!) but do you think all of that is possible?

Thanks again James (and mlmcc!), you're a huge help!

The T1 is just an alias (short for "Table 1" in this case).  That just allows you to refer to the table using T1, instead of twhina122300.  So, when I used T1.field1 and T1.t_orno, it's the same as using twhina122300.field1 and twhina122300.t_orno .

 If you look at the FROM line:

FROM   "erplndb"."dbo"."ttfgld106300" "ttfgld106300"

 That "ttfgld106300" at the end is actually creating an alias for that table.  It's just using exactly the same thing as the full table name for the alias.  I assume that you copied that from CR.  CR doesn't try to get fancy with the queries that it generates, so it just uses the full table name as the alias.  If you changed the FROM line to:

FROM   "erplndb"."dbo"."ttfgld106300" "A"

 you could replace all of the references to ttfgld106300 in the other lines with A.

 I don't think it makes the slightest bit of difference to the db whether you create a shorter alias or just use the full table name.  It's about making the query easier for you to type and read.


 > and also what the 'warehouse' does in the above formula.

 You need to replace that with whatever characters signify that you should use that table.  If you want to look in the twhina122300 table when the field looks like this:

whina122/800000659/114/10/100650

 You need to change the JOIN to:

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,8) = 'whina122' AND
SUBSTRING (ttfgld106300.t_refr,10,9) = T1.t_orno


 Note that I changed the length of the first SUBSTRING from 9 to 8, because "warehouse" is 9 characters, but "whina122" is only 8.  That also changes the starting position of the second SUBSTRING from 11 to 10.

 A basic assumption here is that your strings will be consistent - That the string for table twhina122300 will _always_ be whina122, and that the second part of the string will always be 9 characters; and that the string for the second table will always be the same for that table, and so on.


 Also, a word about the quotes, in case you were wondering.

 Double quotes can be used around the parts in a name (the table name, column name, etc.).  In my experience, they aren't required unless you have some "special" character in the name, like a space, so I tend not to include them.  But CR does, so you get things like

FROM   "erplndb"."dbo"."ttfgld106300" "ttfgld106300"

 None of those double quotes are really necessary, but there's no harm in having them there either.

 So, in the LEFT OUTER JOIN, I have this line:

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON

 The quotes are around erplndb and dbo simply because I copied that from the FROM line.  For consistency, I could have removed them:

erplndb.dbo.twhina122300 T1 ON

 Or added some around the table name:

"erplndb"."dbo"."twhina122300" "T1" ON

 It's all the same to the db.

 Double quotes can be used around the parts of a name.  Single quotes are used for string literals, like 'whina122'.  You can not use double quotes there.  If you put "whina122", the db would think that whina122 was a column name, not the literal string 'whina122'.

 Hope all of that makes some things clearer.  :-)

 James

Awesome explanation James!! I understand it heaps better now.

Re: "A basic assumption here is that your strings will be consistent - That the string for table twhina122300 will _always_ be whina122, and that the second part of the string will always be 9 characters; and that the string for the second table will always be the same for that table, and so on." That is correct. The strings will be consistent.

I'll change that LEFT OUTER JOIN formula now and give it a go.

Stay tuned....

OK, so I've just kept the formula with the one table at this stage and yippee - no errors!

Can I just leave the Command in Database Expert and delete the ttfgld106 and twhina122 tables now? Or do I need to keep them in there?

I haven't 'refreshed' to try and retrieve the data yet - that will be the next test.

Ultimately you will replace the tables with the Command, so, yes, you would remove the tables.  But before you do that, you may want to basically duplicate the report using the fields in the Command.  When you remove the tables, CR will presumably remove all of the fields from those tables from the report.  I'm not 100% sure, because I don't think I've ever done this before.  Assuming that CR is going to remove those fields, it might make things a bit easier to duplicate the current setup using the new fields first.  For example, create a new detail section and insert the fields from the Command there, and arrange and format them like the old fields.  Then when you remove the tables, delete the old detail section and you'll be left with the new section with the new fields.

 James

Fantastic. Yes i have done this, and the new link produces data in the report. I have found though that I must need another link on the first database, as it is producing multiple records for one record.

When I try to link in another field I am getting the following message:

"Failed to retrieve data from the database: 22018. Conversion failed when converting the varchar value '1/0' to datatype Int. [Database Vendor Code 245]."
Do you know how to fix this? I've attached a snipit of PART the code.

T1.t_orno, T1.t_item, T1.t_rvrs, T1.t_seqn,T2.t_itid, T2.t_orno, T2.t_item
FROM   "erplndb"."dbo"."ttfgld106300" "ttfgld106300"

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,8) = 'whina122' AND
SUBSTRING (ttfgld106300.t_refr,10,9) = T1.t_orno AND
SUBSTRING (ttfgld106300.t_refr,20,3) = T1.t_seqn


Also, given that I have put the Selection criteria in the Command, does this mean that the Select Expert won't work anymore? (I need to run this report with a parameter that I haven't yet written).

Thanks James, you're a GREAT help!!!

You can have parameters in a COMMAND.  You create them in the edit command screen.

mlmcc

Thanks mlmcc. How do you do this?

When editting the commnd there should be a parameter button.  Click it and create the parameter.  

mlmcc

As mlmcc said, there's an option to create a parameter in the Command editor.  When you do, you can include that parameter in the Command.  You can not use parameters that are created in the regular parameter editor.  You have to create the parameter in the Command editor window.  But once you've created the parameter for the Command, you can edit it in the regular parameter editor.

 Another option would be to create a stored procedure in the db, instead of a Command in CR.  Then you could create a parameter in the stored procedure.

 As for your error, it looks like there a problem with your last SUBSTRING.  I think the '1/0' in the error message is what the last SUBSTRING is pulling out (3 chararacters starting at position 20), and, of course, that isn't an integer.  Either the starting position of 20 is wrong, or the value in that string is different and the number that you're looking for is not in the right position.

 James

Thanks for that. I'm still trying to fix the join so that I can test the whole report. I've found that the characters starting at position 20 that I need to link to can be 1 to 3 characters long. Here is a snipit of the code. I am getting an error re the word THEN and IF.

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,8) = 'whina122' AND
SUBSTRING (ttfgld106300.t_refr,10,9) = T1.t_orno AND
(if SUBSTRING (ttfgld106300.t_refr,20,2) = "/" THEN
SUBSTRING (ttfgld106300.t_refr,20,1) = T1.t_seqn ELSE
if SUBSTRING (ttfgld106300.t_refr,20,3) = "/" THEN
SUBSTRING (ttfgld106300.t_refr,20,2) = T1.t_seqn ELSE
SUBSTRING (ttfgld106300.t_refr,20,3) = T1.t_seqn)

Thanks mlmcc, you're right - I was picking up the wrong part of the text.

I'm still getting the IF and THEN errors though.... This is the snipit of the formula as is:

LEFT OUTER JOIN
"erplndb"."dbo".twhina122300 T1 ON
SUBSTRING (ttfgld106300.t_refr,1,8) = 'whina122' AND
SUBSTRING (ttfgld106300.t_refr,10,9) = T1.t_orno AND
(if SUBSTRING (ttfgld106300.t_refr,21,1) = "/" THEN
SUBSTRING (ttfgld106300.t_refr,20,1) = T1.t_seqn ELSE
if SUBSTRING (ttfgld106300.t_refr,22,1) = "/" THEN
SUBSTRING (ttfgld106300.t_refr,20,2) = T1.t_seqn ELSE
SUBSTRING (ttfgld106300.t_refr,20,3) = T1.t_seqn)

Have I got my brackets in the right place? it doesn't like my syntax regarding IF and THEN for some reason....  Any ideas?

Thanks James.  Been too long since I wrote any serious SQL.

mlmcc

Thank you so much for your help!!

You're welcome.  Glad I could help.

 James