Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 conditional query

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 Examples

dbo.department
Description - ID
Books - 1
Toys - 2

dbo.item
Description - DepartmentID - ID*
A Book - 1 - 100
A Toy - 2 - 250

dbo.alias
itemid* - alias
100 - 978345157965584523
100 - 9783451579655
100 - DC34987
250 - 7265882347866
250 - DC85364

dbo.webitems
ID - Itemid* - Barcode
1 - 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 #AliasIs

This 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 WebItems

ID DeptID Alias ItemID
100 1 978345157965584523 100
100 1 9783451579655 100
100 1 DC34987 100
250 2 7265882347866 250
250 2 DC85364 250

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

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 Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

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
Go to Top of Page

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 here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

micagordon
Starting Member

6 Posts

Posted - 2015-07-08 : 21:57:28
For adding barcode to webitems, you can achieve this work by using
UPC barcode generation library for sql server. But UPC code contains UPC-A and UPC-B, please have a look at:
.net UPC-A barcode generation guide
.net UPC-E barcode generation guide
Go to Top of Page
   

- Advertisement -