Author |
Topic |
RichBNYC
Starting Member
3 Posts |
Posted - 2010-08-18 : 16:08:46
|
I have four tables: department, items, alias, webitems. The alias table contains a mixture of barcodes, upc codes and manufacturers codes for each item. I need to update the webitems.barcode with alias.alias based on two criteria. The first is the department.id and the other is the type of alias.Table Examplesdbo.departmentDescription - IDBooks - 1Toys - 2dbo.itemDescription - DepartmentID - ID*A Book - 1 - 100A Toy - 2 - 250dbo.aliasitemid* - alias100 - 978345157965584523100 - 9783451579655100 - DC34987250 - 7265882347866250 - DC85364dbo.webitemsID - Itemid* - Barcode1 - 100 -2 - 250 -I need to update the webitems.barcode with an alias based on the departmentID and the length of the alias. For example: if the department is 1, I want to select the 18 digit alias and if there isn't one, select the 13 digit one.I've started off with the following: (it's crude - but I'm a beginner)select Itm.ID as ID , Itm.DepartmentID as DeptID , Als.alias as Alias , Als.ItemID into #AliasIs from Item Itm right outer join Alias Als on Itm.ID = Als.Itemid drop table #AliasIsThis gives me a temporary table with all the aliases and the relevant department and item information. What I am stuck on is how to update the webitem.barcode from the temporary table based on the criteria I've mentioned.Any help would be very much appreciated.Rich |
|
MSquared
Yak Posting Veteran
52 Posts |
Posted - 2010-08-18 : 16:48:00
|
Rich, can you show what you are expecting in the webitems table? When I run your query i get the following results. What if there is no alias? Just not sure what you expect in WebItemsID DeptID Alias ItemID100 1 978345157965584523 100100 1 9783451579655 100100 1 DC34987 100250 2 7265882347866 250250 2 DC85364 250For Faster results please follow the posting guidelines herehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-18 : 16:58:08
|
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. That means no spaces in the data element names and no silly data type and "tbl-" prefixes. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html This "multiple identifier" problem occurs in securities a lot. The table with the alternative identifiers has SEPARATE columns for each type of id used. One of them is picked to be the key and it is the REFERENCE in the rest of the schema. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2010-08-18 : 16:58:08
|
Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. That means no spaces in the data element names and no silly data type and "tbl-" prefixes. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http://www.catb.org/~esr/faqs/smart-questions.html This "multiple identifier" problem occurs in securities a lot. The table with the alternative identifiers has SEPARATE columns for each type of id used. One of them is picked to be the key and it is the REFERENCE in the rest of the schema. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
RichBNYC
Starting Member
3 Posts |
Posted - 2010-08-18 : 18:23:22
|
Thanks Msquared,at present there is no barcode in the webitems table. I'm trying to populate each webitem with the correct alias from the alias table. In effect, the webitems table is pretty much the same as the item table but with one barcode instead of the many and various types that are attached to the items vial the alias table.The result set is correct... the next step (which I couldn't figure out how to do) is to put one of the item's matching alias into the barcode based on its length and department. So if the criteria is department = 1 and len(alias) = 18, webitems would look like this:id = 1, itemid = 100, barcode = 978345157965584523.Sorry if I've not been clear about this, I'm new to sql and have been puzzling over this for a couple of days.Rich |
|
|
MSquared
Yak Posting Veteran
52 Posts |
Posted - 2010-08-19 : 08:14:20
|
So, you're looking for an alias that is either 18 digits or 13 digits in the alias table? Doesn't make much sense, but you have your requirements. In your example, you also have an alias that is 7 digits, what't going on with that one?For Faster results please follow the posting guidelines herehttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
RichBNYC
Starting Member
3 Posts |
Posted - 2010-08-19 : 14:27:29
|
A large US supplier populates our inventory database via purchase orders. If they do not have a proper barcode (ISBN,UPC etc.) they may insert part of one or their own codes into the alias table. There are also a surprising number of variations of barcodes that can be used for one item. This is not a problem in the store... the barcode scanners search the alias table to find a match. However, when we put the items on the web we only want the most current barcode associated with that item. ISBN numbers have changed from 13 to 18 digits, UPC numbers have also recently changed. So what I am trying to do is insert the most current barcode from the alias table into webitems. The system we are using is Microsoft RMS.Rich |
|
|
judy56
Starting Member
2 Posts |
Posted - 2013-07-08 : 00:19:46
|
You can create UPC barcodes with this UPC barcode generator and apply them into your application. The generated barcode images are standard and professional for good compatibility. |
|
|
micagordon
Starting Member
6 Posts |
|
|