Print Page | Close Window

Joining Tables

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=22889
Printed Date: 29 Apr 2024 at 10:10am


Topic: Joining Tables
Posted By: brockloch
Subject: Joining Tables
Date 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


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

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

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

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

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

Posted By: brockloch
Date Posted: 08 Sep 2020 at 6:35am
Sorry for the delay in coming back to you, I'll try using CASE. Thanks again

-------------
brockloch

Posted By: brockloch
Date Posted: 08 Sep 2020 at 6:36am
Thank you for your help, sorry for the delay in coming back to you.
Ed

-------------
brockloch


Print Page | Close Window