SQL help

Hi Danny,

Go to SO Administrator, then go to File and Connect the DB, then go to qUERY AND CLICK ON NEW, now type the following query

SELECT * FROM ITEM WHERE BARCODEFORMAT 7 -- list you all items

Now suppose you wanted to update those which has no 7 so run the update query.

update item set barcodeformat=7 where barcodeformat 7

Rate this if you see it is working.

Akber

"Danny" wrote:

Hi Akber

I want to update barcodeformat for items with no barcodeformat (just using barcodeformat = 7) and items with no alias. Some background - we have our own numbering system for ILC - this year we purchased a scanner - so during invenotry time we entered all the barcode's as Aliases. Since one department had no items with barcodes, I was able to update those barcodeformat field very easily with Update barcodeformat = 7 from item with departmentid = 1 - now I would like to do all other departments but NOT the items with alias (which is the manufacturers companies barcode).

Thanks Danny

Hi Akber

If I do the following sql :

select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i LEFT OUTER JOIN alias a ON i.id = a.itemid

I get all my Items - what I want is ONLY Items with barcodeformat7 or a.alias = '' (blank)

Danny

Hi Danny - you can try using a subquery as well...

select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i left join alias a on i.id = a.itemid where i.barcodeformat 7 or i.id not in (select itemid from alias)

My understanding is that the barcode format setting dictates what barcode format will print on a barcode tag you print using the label wizard -- if your items come with a barcode and you've added the barcode as an alias, the "barcode format" doesn't come into play -- its primarily your scanner being programmed to recognize the barcode format of the "alias" tag...most scanners today will work fine - you can test the barcode by scanning it into a notepad and comparing what you see in the notepad with what you assigned as the alias

- if the numbers match, you're good to go, no real need to re-assign barcode formats > Hi Akber

hi do this: select i.itemlookupcode, i.barcodeformat, i.description, a.alias from item i LEFT OUTER JOIN alias a ON i.id = a.itemid Where i.barcodeformat7 or a.alias is null

"Danny" wrote:

Akber/Convoluted

Worked great - thanks

1) Now I have to turn on the barcodeformat type to 7 for all my items where I don't have an alias... 2) Need report to give me value of inventory at start as of Jan 1, 2007 and at end of Dec., 31, 2007.

Danny PS. Rated both replies - thanks guys

Hi Danny - search the newsgroup for the report - I don't have it available but I recall seeing this request more than a few times... you can use this query to set your barcodeformat to 7 for items not in the alias table - BACKUP FIRST...

update item set barcodeformat = 7 where id not in (select itemid from alias)

happy friday....

"Danny" wrote: