Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Available Inventory Report

0 Kudos

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

  • SAP Managed Tags:
1 ACCEPTED SOLUTION

KonradZaleski
Active Contributor

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
  • SAP Managed Tags:
3 REPLIES 3

KonradZaleski
Active Contributor

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
  • SAP Managed Tags:

0 Kudos

Perfect! Yeah, I should have thought of that.

Thanks,

Seth

  • SAP Managed Tags:

0 Kudos

Please accept my answer if problem has been solved.

  • SAP Managed Tags: