SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 conditional query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

RichBNYC
Starting Member

USA
3 Posts

Posted - 08/18/2010 :  16:08:46  Show Profile  Reply with Quote
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 - 08/18/2010 :  16:48:00  Show Profile  Reply with Quote
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

Edited by - MSquared on 08/18/2010 16:50:41
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 08/18/2010 :  16:58:08  Show Profile  Visit jcelko's Homepage  Reply with Quote
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

USA
547 Posts

Posted - 08/18/2010 :  16:58:08  Show Profile  Visit jcelko's Homepage  Reply with Quote
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

USA
3 Posts

Posted - 08/18/2010 :  18:23:22  Show Profile  Reply with Quote
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 - 08/19/2010 :  08:14:20  Show Profile  Reply with Quote
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

USA
3 Posts

Posted - 08/19/2010 :  14:27:29  Show Profile  Reply with Quote
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 - 07/08/2013 :  00:19:46  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000