Hi everybody,

I beleive this is impossible to have something like this

select ..., CASE When SomeCondition THEN some complex programming logic ... etc.

Here is the query that someone tries and it looks to me like a completely wrong, but is it possible to somehow solve this?

SELECT DISTINCT	SM.sku_id, SM.sku_description, SM.unit_price,
							SM.product_type, ST.description,
							SM.product_group, SM.warehouse_id,

							CASE
								WHEN (SM.warehouse_id = 'DC1' OR SM.warehouse_id = 'DC2') then
									--BEGIN
										SELECT @ClientID = wh_owner_id 
										FROM project_warehouse_setup PWS
										INNER JOIN project_setup PS 
										ON PWS.project_id = PS.projectID
										WHERE PS.project_code = @ProjectCode
										AND PWS.warehouse_id = SM.warehouse_id
									--END

									-- NOW LOOK UP INVENTORY DATA IN ILS
										
										--DECLARE @QTY_ALLOCATED int

										SELECT @QTY_ALLOCATED = SUM(QTY) FROM ILS_DATA.dbo.ORDER_ALLOCATION
										WHERE CLIENT_ID = @ClientID
										AND SKU_ID = SA.sku_id

										IF @QTY_ALLOCATED IS NULL
											SET @QTY_ALLOCATED = 0

										--DECLARE @RET_QTY int

										--SELECT @RET_QTY = CAST(QTY_AVAILABLE - @QTY_ALLOCATED - QTY_HELD AS Int)
										SELECT @RET_QTY = CAST(QTY_AVAILABLE - @QTY_ALLOCATED AS Int)
										FROM ILS_DATA.dbo.INVENTORY_SUMMARY
										WHERE CLIENT_ID = @ClientID
										AND SKU_ID = SA.sku_id

										IF @RET_QTY < 0
											SET @RET_QTY = 0

										SELECT @RET_QTY AS QTY
							END

			FROM			sku_master SM
			INNER JOIN		sku_alias SA on SM.sku_id = SA.sku_id 
			INNER JOIN		sku_types ST on SM.product_type = ST.Item_Type

		WHERE	SA.sku_group = @SkuGroup
				AND SM.destroy_status = 'ACTIVE' 
				AND SM.sku_status = 'ACTIVE'

		ORDER BY SM.sku_id ASC
		END