SQL Error 207

On this piece of sql code I'm getting an error. Here is the code:

(OID.[Pieces]-OID.[delivered]) AS NotShipped,  CONVERT(INT,NotShipped/CIL.[Unit]) AS Cases, NotShipped % CIL.[Unit] AS Piece,

and here is the Error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'NotShipped'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'NotShipped'.

The error is with this piece of code:
CONVERT(INT,NotShipped/CIL.[Unit]) AS Cases, NotShipped % CIL.[Unit] AS Piece,

What am I doing wrong? am I not able to refer to a previously created column?

Tammak1Asked:
Who is Participating?
 
imitchieCommented:
you can't use a named column in the select statement. put it in a a subquery. i.e.

select *, CONVERT(INT,NotShipped/[Unit]) AS Cases, NotShipped % [Unit] AS Piece
from
(
select
field1, field2, field3, (OID.[Pieces]-OID.[delivered]) AS NotShipped, field5
from table
) innertable

either that or

(OID.[Pieces]-OID.[delivered]) AS NotShipped,  CONVERT(INT,(OID.[Pieces]-OID.[delivered])/CIL.[Unit]) AS Cases, (OID.[Pieces]-OID.[delivered]) % CIL.[Unit] AS Piece,
0
 
James MurrellProduct SpecialistCommented:
what columns are in the table
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.