| Author |
Message |
brockloch
Groupie
Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 76
|
   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 Logged |
|
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
brockloch
Groupie
Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 76
|
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 Logged |
|
brockloch
Groupie
Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 76
|
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 Logged |
|
kevlray
Admin Group
Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
|
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 Logged |
|
lockwelle
Moderator
Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
|
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 Logged |
|
brockloch
Groupie
Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 76
|
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 Logged |
|
brockloch
Groupie
Joined: 03 Oct 2010
Location: United Kingdom
Online Status: Offline
Posts: 76
|
Posted: 08 Sep 2020 at 6:36am |
|
Thank you for your help, sorry for the delay in coming back to you.
Ed
|
|
brockloch
|
|
IP Logged |
|
|
|
