SQL Query - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

SQL Query

Author

Topic

SergioM
Posting Yak Master

170 Posts

Posted - 2010-08-23 : 01:00:40

This is a basic question but I'm very new to SQL. I have a database hosted on SQL Server 2005. I have written some very simple queries. I want to know if it's possible to find/replace information but so that it only appears that way on the Output and not within the database itself.

For example. My database has my suppliers in numerical format. 1, 2 3, 4, 5, etc... However when I query the database I want to know which supplier is which. 1 is Bob, 2 is Bill, 3 is Roger, etc... Can I write a query that will find/replace those values but will leave them in numerical format within the DB? It should be easy, but I don't really know how.

I thought that it would work by trying the following. However I just get an error message.

SELECT    Inventory.SupplierID

FROM      Inventory
WHERE     Inventory.QtyOnHand > 0

UPDATE Inventory.SupplierID
SET Inventory.SupplierID=replace(Inventory.SupplierID, '1', 'Bob')
WHERE Inventory.SupplierID='1'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-23 : 01:32:15

do you have a Supplier table that defines what is the supplier ID and the corresponding name ?

something like


create table Supplier
(
    SupplierID   int,
    SupplierName varchar(100)
    primary key (SupplierID)
)

insert into Supplier (SupplierID, SupplierName) values (1, 'Bob')

then you can use INNER JOIN to your Inventory table when you retrieve data from it

select Inventory.SupplierID, Supplier.SupplierName, Inventory.QtyOnHand
from   Inventory 
       inner join Supplier on Inventory.SupplierID = Supplier.SupplierID
where  Inventory.QtyOnHand > 0

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 04:46:41

as Tan suggests you need to have a supplier mapping table for this. Its not a good method to use REPLACE or CASE based on numeric values to get names.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SergioM
Posting Yak Master

170 Posts

Posted - 2010-08-23 : 16:58:47

Interesting. I suppose that is easy to set up and would probably be quicker for querying the database. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:42:26

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources