July 25, 2012 at 7:30 am
Hi all,
I have a table, InventoryDetail that has a composite key:
warehouse_no
Record_type
TagLot_No
None of these individually are unique, but with all 3 together they are. This is actually a COBOL application and we are moving the data into SQL Server in order to create reports.
I am trying to connect this table to others and the only thing they have in common is the warehouse_no and since it's not unique this isn't working of course.
Here is the query I tried, and failed with!
Select *DATA*
FROM InventoryDetail id
LEFT JOIN SalesReporting sr
ON id.Warehouse_No = sr.warehouse_no
LEFT JOIN Category cat
ON cat.Category_ID = sr.Category_ID
LEFT JOIN Item i
on i.Item_ID = sr.Item_ID
There are 3 tables I need to join for this query and the Inventory Detail is the main issue, nothing really in common with the other tables they are requesting. And I can't change anything, this is an old system like I said in COBOL. I can modify the data or structure when I ETL it into SQL though if that would make a difference?
If anyone has any suggestions I would really appreciate it, been working on this for awhile and my boss is waiting!
thanks!
July 25, 2012 at 8:52 am
Your question is not very clear. Kindly post DDL along with sample data.
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
July 25, 2012 at 9:10 am
Record_Type & Tag_Lot_No must appear somewhere else in the database - hopefully in the tables you want to join to. If they do, you will need to add the tables with them into your FROM clause, then join the InventoryDetail table using joins from more than 1 table. If they don't, you can't join to them. Get some COBOL programs & try & find linking code, or ask someone who knows the old system if possible...
July 25, 2012 at 9:13 am
+1
Get in touch with some veteran from cobol team
~ Lokesh Vij
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
July 25, 2012 at 9:28 am
I agree as well, get with the team that supports the COBOL app and ask them to help you understand the tables used and how they relate to each other.
I was in your position at one time, the difference was I was also part of the team the supported the COBOL application so I had intimate knowledge of the data and how each of the tables related to each other. made it much easier to extract and import into SQL Server for reporting purposes.
July 25, 2012 at 9:41 am
thanks!
talked to a programmer and found the other 2 fields in a table that I don't need, but will try to join in anyway and see if it works.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply