Need help on this CASE WHEN statement

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

Need help on this CASE WHEN statement

Author

Topic

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-09 : 10:58:21

Hey guys,

I'm working on this CASE WHEN statement but it seems not work out for me at all.
SELECT
(p21_view_inventory_value_report.[qty_on_hand]+p21_view_inventory_value_report.special_layer_qty) as 'total_qty'
,((p21_view_inventory_value_report.[qty_on_hand]+p21_view_inventory_value_report.special_layer_qty)-p21_view_inv_loc.qty_allocated) AS 'total_available'
,(((p21_view_inventory_value_report.qty_on_hand - p21_view_inventory_value_report.special_layer_qty) * [p21_view_inventory_value_report].[cost]) + p21_view_inventory_value_report.special_layer_value) AS extended_value
,CASE
WHEN (SUM (CASE WHEN [p21_inventory_usage_all_view].year_and_period BETWEEN LEFT(CONVERT(CHAR(8), DATEADD(MM, -9, getdate()),112),6) AND LEFT(CONVERT(CHAR(8), DATEADD(MM, 0, getdate()),112),6)THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) = 0) THEN 'No Usage 9 Months'
WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N') THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'Non-Stockable'
WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T') THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'Temporary'
WHEN (SUM (CASE WHEN [p21_view_inv_loc].date_created <= DATEADD(mm,DATEDIFF(mm,0,GETDATE())-3,0) THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)= 0) THEN 'NNU'
ELSE 'Good' END As 'Status'

These two statements that the query does not like it.

WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N') THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'Non-Stockable'
WHEN (SUM (CASE WHEN p21_view_inv_mast.product_type = 'T') THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END) THEN 'Temporary'

I think the problem is because of the text string or something. I could not figure out why. I try to elaborate this two case when into a simple way as

Select
[p21_view_inv_loc].stockable
From
[p21_view_inv_loc]
Where
[p21_view_inv_loc]= 'N'

It works this way and this way it does not work at all

Select
[p21_view_inv_loc].stockable = 'N'

From

[p21_view_inv_loc]

It executed with errors. If you guys know why it is not working this way, let me know. Because I really need to work these statements out with the case when function to get the data that I need.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 11:32:02

your braces are not properly placed

for example

WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N') THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END)

should be this

WHEN (SUM (CASE WHEN [p21_view_inv_loc].stockable = 'N' THEN [p21_inventory_usage_all_view].actual_usage ELSE 0 END))

...

try repositioning them correctly and running

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

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-09 : 11:34:32

I did try to reposition them and this error occurs: An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.

I show you how I join them as :

FROM
[p21_view_inventory_value_report]
LEFT OUTER JOIN p21_view_inv_loc ON (p21_view_inv_loc.inv_mast_uid = p21_view_inventory_value_report.inv_mast_uid) AND
(p21_view_inv_loc.location_id = p21_view_inventory_value_report.location_id)
LEFT OUTER JOIN [p21_inventory_usage_all_view] ON ([p21_view_inventory_value_report].inv_mast_uid = [p21_inventory_usage_all_view].inv_mast_uid AND
[p21_view_inventory_value_report].location_id = [p21_inventory_usage_all_view].location_id)
LEFT OUTER JOIN p21_view_inv_mast ON p21_view_inventory_value_report.inv_mast_uid = p21_view_inv_mast.inv_mast_uid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-09 : 11:50:19

the issue is not on the join but on braces you use within your CASE..WHEN

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

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-09 : 11:53:19

Every other CASE WHEN works out when I took them off the query, I don't know how it's not working when everything seems very logical.

tantcu
Yak Posting Veteran

58 Posts

Posted - 2012-07-09 : 12:19:53

Is there anyone know how to convert this string text, it may help to solve the problem?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources