Problem wish SQL CASE WHEN. Help? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Problem wish SQL CASE WHEN. Help?

Author

Topic

wleonard
Starting Member

30 Posts

Posted - 2011-04-13 : 13:24:27

(CASE
WHEN (cos_quantity_on_hand < 0) THEN 0
END
)

Why am I getting a syntax error near CASE?

Will Leonard

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-13 : 13:26:15

Can you show the rest of the query? The CASE expression looks fine.

wleonard
Starting Member

30 Posts

Posted - 2011-04-13 : 13:34:42

SELECT tblinventoryonhand.cos_quantity_on_hand, rtrim(tblinventoryonhand.inp_short_item_number)+rtrim(Isnull(tblinventoryonhand.ins_color, ''))+rtrim(Isnull(tblinventoryonhand.ins_size, ''))
(CASE
WHEN (cos_quantity_on_hand < 0) THEN 0
END
)
FROM tblinventoryonhand
INNER JOIN cat_prod
ON rtrim(tblinventoryonhand.inp_short_item_number)+rtrim(Isnull(tblinventoryonhand.ins_color, ''))+rtrim(Isnull(tblinventoryonhand.ins_size, ''))=cat_prod.ProdCode

Will Leonard

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-04-13 : 13:39:27

You are missing a comma before the Case expression.

wleonard
Starting Member

30 Posts

Posted - 2011-04-13 : 13:40:37

what do you mean comma? Like this:

, (CASE WHEN....

???????

Will Leonard

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-04-13 : 18:34:37

The short answer is that you missed giving a name to the computed column and left out a comma. But this short posting is full of other basic design errors.

Please stop putting “tbl” on table names. It violates ISO-11179, common sense and looks soooo silly. But worse, you have attribute splitting. A separate table for inventory on hand is like having “Male_Personnel” and “Female_Personnel” instead a
“personnel;” table. The on hand quantity is a measurement on an absolute scale in your Inventory. It is not an entity.

We use COALESCE these days, not ISNULL(). How can a quantity physically fall below zero? What yuoun are doing in a query should be handled with shortage_qty, on_order_qty, etc. in the DDL.

We do not need to use TRIM() very often because CHECK() constraints have prevented dirty data from getting into the table in the first place.

Why did you build a product code with concatenation of unrelated atomic attributes? Where is the SKU? Here is a guess at a usable schema

SELECT I.sku, C.product_name,
I.on_hand_qty,
-- I.inp_short_item_number, –- is this the SKU or UPC?
I.ins_color, I.ins_size,
CASE WHEN I.on_hand_qty < 0
THEN 0 ELSE NULL END AS fix_dirty_data_in_DML
FROM Inventory AS I, Product_Categories AS C
WHERE I.sku = C.sku;

Your DML is not your real problem.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources