| Author |
Topic |
|
SergioM
Posting Yak Master
170 Posts |
 Posted - 2011-09-07 : 17:04:05
|
I would like to create a SQL query that shows a value different from the one that it reads in the database. Normally, I would just SELECT & UPDATE the table. But the DBMS inputs a numeric field and it expects to find it. I would like to create a query that says something likequote:
If Inventory.Location is equal to '1' - Display as '1 Day Lead time'If Inventory.Location is equal to '2' - Display as '3 Day Lead time'If Inventory.Location is equal to '3' - Display as '5 Day Lead time'
The following SQL Query works very well for the part where I select what I need to find. I just don't know how to make it show something based on the data it finds. How can I do this in SQL?SELECT Items.Manufacturer, Inventory.PartNum, Inventory.QtyOnHand, Items.TitleFROM InventoryINNER JOIN ItemsON Inventory.InventoryID = Items.InventoryIDWHERE Inventory.Location = '1' |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2011-09-07 : 17:22:06
|
| It's a fixed value. There isn't any other data in the DB that would tell you. I just know it. There are also too few warehouses (5) to want to develop anything slick. I'm ok with retyping the code 5 times. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2011-09-07 : 19:06:04
|
| Thanks! I just tried it out & it does exactly what I need. I don't want to take advantage, but I have a very similar question and it doesn't justify a second post. What if I want to keep it within the same column as the other data? IE If I have a price list and some values are missing. So rather than having them appear as 'NULL', I can say, if it is NULL, display '0'. Except, I wouldn't want a second column. I would want to display it all in the same one, just altered. Again, thanks for the help. I'm halfway through an SQL, book but (so far) it encompasses basic understanding without delving into these subtleties. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2011-09-07 : 22:24:06
|
I plan to use this SQL technique on a couple of things. The following table is easier to express. These three vendors sell the similar items. When they all carry the items, the comparison is easy. Sometimes there is little or no overlap. So that vendor does not carry the item. Since the field exists, it is just stated as 'NULL'. However, that clutters any exports I have and takes up a lot of space with needless 'NULL' strings. I'd prefer for it to be blank or say '0'. Vendor_A Vendor_B Vendor_CItem_1 $12.00 $11.00 $10.00Item_2 $15.00 NULL $5.00Item_3 $11.00 $11.00 NULLItem_4 $10.00 $15.00 $15.00Item_5 NULL NULL $5.00 In your previous example, all of the output END AS 'LeadTime', a new column created in real time. In this example, I want it all to appear in the column it came from. So using the example above, I would want to alter Row 3, Column 3 (Vendor_B:Item_2). But I would want the output to be right there where it came from. Not in a new column.I'm sure that the SQL command is a variation of what you showed earlier. But I'm getting error after error as I tinker with it on my own. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2011-09-08 : 00:08:08
|
| Again, it's exactly what I needed & works like a charm. Thank you!!! :) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
If Value = 1, Display as '3 Day Lead time'