Available Inventory Report

[former_member604104]

I am trying to write a query where I can find out what items are in stock, after deducting the committed quantities.

This works:

SELECT TOP 10 T1.[ItemCode], ( T1.[OnHand] - T1.[IsCommited] ) as [AVAILABLE] FROM [OITM] T1 ORDER BY [AVAILABLE]

But if I try to add [AVAILABLE] to the where clause:

SELECT TOP 10 T1.[ItemCode], ( T1.[OnHand] - T1.[IsCommited] ) as [AVAILABLE] FROM [OITM] T1 WHERE [AVAILABLE] > '0'

it gives the error "Invalid Column Name." I have tried this several different ways, all without success.

Any help would be appreciated.

Thanks,

Seth

[KonradZaleski]

You cannot use alias in the WHERE clause directly. Try this:

SELECT TOP 10 
  T1.[ItemCode], 
  ( T1.[OnHand] - T1.[IsCommited] ) as [AVAILABLE] 
FROM 
  [OITM] T1 
WHERE 
  ( T1.[OnHand] - T1.[IsCommited] )  > 0

[former_member604104]

Perfect! Yeah, I should have thought of that.

Thanks,

Seth

[KonradZaleski]

Please accept my answer if problem has been solved.