May 14, 2009 at 1:08 pm
Be aware I'm not an SQL guru. I am using a third party application called Bartender Label Software to connect to 2 SQL ODBC connections. I'm hoping someone could provide some insight on the SQL Database side that will point me in the right direction and is willing to take on this challenge.
Situation:
I have a label where I have 2 SQL databases added. In particular, Database A has a table with an item_no and Database B is a view that has a field named stock number that is similar to the item_no in Database A, but not exact. The item_no/stock number have the first 8 characters in common. Database A has stock numbers where the last 2 characters in the number determine the weight and unit of measure that stock number is. In Database A there is around 90 different ending characters on the stock number that determine the weight and unit of measure that are located in a separate field in the database. Database B has a stock number where the last 2 characters are always the same, in this case the last 2 characters are "IM" meaning that the stock number is a raw material. The SQL view in Database B calculates the top 5 ingredients and VOC % of a specified stock number that is entered with the last 2 characters ending in "IM".
What I'm trying to accomplish is in my Bartender label, the user will be prompted to enter the stock number of the item with the 8 digits ending in so for example "12345678AE", then query both databases to populate the appropriate fields. The fields would be as follows: from Database A the label would populate the Barcode 128 font from the stock number entered, the stock number keyed into the prompt, the stock number description, the item weight determined by the last 2 characters, and the item unit of measure determined from the last 2 characters. From Database B the label would populate the top 5 ingredients and the VOC % in grams per liter which I have confirmed is working if you enter in the stock number with the last 2 characters ending in "IM".
The Problem:
The problem I have is that the item_no from Database A and the stock number from Database B is not exactly the same because of the last 2 characters in the stock number. I need to enter the specified last 2 characters in the stock number in order in order to populate the fields from Database A and I need to enter the stock number with the last to character ending in "IM" in order to populate the top 5 ingredients and VOC information on that stock number from Database B. I have found that this is not http://www.sqlservercentral.com/Forums/Skins/Classic/Images/RichTextBoxTable/tbl_bottomleft.gifpossible because when you are joining two tables in Bartender you would need to join 2 tables that have the same exact stock number/item numbers in both Databases.
I'm thinking if I could somehow create the correct SQL view that has both the item_no from Database A and the stock number from Database B and I would be golden.
May 14, 2009 at 4:29 pm
Please review the article in my signature. To better answer your question we are going to need table definitions (create table statements), sample data (insert statements) and expected results.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply