Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: Joining Tables Post Reply Post New Topic
Author Message
brockloch
Groupie
Groupie


Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 67
Quote brockloch Replybullet Topic: Joining Tables
    Posted: 02 Sep 2020 at 1:04am
Hi

I wonder if someone can help please.
I have 2 stock tables which I would like to join, however in one table the stock code is called ABC in the other table it is called ABC(STOCKCODE). I can extract the stock code and create a sub report but I would rather join them some other way, if possible.
Any help much appreciated.
Ed
brockloch
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1528
Quote kevlray Replybullet Posted: 02 Sep 2020 at 3:56am
I do not know what the data source is or how good you are with SQL or if you know how to create a command. But here is an example if it helps.

from firstTable
join secondTable on firstTable.stockcode = left(secondTable.stockcode,3)
IP IP Logged
brockloch
Groupie
Groupie


Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 67
Quote brockloch Replybullet Posted: 02 Sep 2020 at 4:28am
Thank you, I know a bit of sql and a bit about command!
But what you have given me helps, it's not always 3 characters but i think i can work out the formula.
That's great thank you
Ed
brockloch
IP IP Logged
brockloch
Groupie
Groupie


Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 67
Quote brockloch Replybullet Posted: 02 Sep 2020 at 5:38am
Hi

Should I be able to use something like this as the join in the command please?

if (instr({BillofMaterialCostingComponent.StockCode},"(")-1) > 0 then mid({BillofMaterialCostingComponent.StockCode},1,(instr({BillofMaterialCostingComponent.StockCode},"(")-1))
    else {BillofMaterialCostingComponent.StockCode}


Thanks Ed
brockloch
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1528
Quote kevlray Replybullet Posted: 03 Sep 2020 at 4:03am
Except that a if statement is not normally available, and the field names would not have the braces, and not sure about instr and mid (seems like there is other standard functions), thus something like this.

case when (instr(BillofMaterialCostingComponent.StockCode,"(")-1) > 0 then mid(BillofMaterialCostingComponent.StockCode,1,(instr(BillofMaterialCostingComponent.StockCode,"(")-1))
    else BillofMaterialCostingComponent.StockCode end
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4297
Quote lockwelle Replybullet Posted: 03 Sep 2020 at 7:56am
your join command could be something like:
from firstTable
join secondTable on firstTable.stockcode = left(secondTable.stockcode,len(firstTable.stockcode))

this would allow the length to change.

It is not a good join, and sometimes that is the best that we can do.

HTH
IP IP Logged
brockloch
Groupie
Groupie


Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 67
Quote brockloch Replybullet Posted: 08 Sep 2020 at 6:35am
Sorry for the delay in coming back to you, I'll try using CASE. Thanks again
brockloch
IP IP Logged
brockloch
Groupie
Groupie


Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 67
Quote brockloch Replybullet Posted: 08 Sep 2020 at 6:36am
Thank you for your help, sorry for the delay in coming back to you.
Ed
brockloch
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.