SQL Stored Procedure - Invalid Column Name

Question

I'm building a stored procedure within SSMS and I've run into a brick wall.

USE [VINTCMPA]

go

/****** Object:  StoredProcedure [dbo].[OpenOrderReport]    Script Date: 12/05/2021 12:20:35 ******/
SET ansi_nulls ON

go

SET quoted_identifier ON

go

-- =============================================
-- Author:    Bancroft Wines
-- Create date: 12/05/2021
-- Description:  
-- =============================================
ALTER PROCEDURE [dbo].[Openorderreport]
  -- Add the parameters for the stored procedure here
  @startdate DATETIME = NULL,
  @enddate   DATETIME = NULL
AS
  BEGIN
      SET @startdate = CONVERT(DATETIME, '2021-04-30 23:59:59', 102)
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET nocount ON;

      -- Insert statements for procedure here
      SELECT Customers.cust_acc                       AS CUSTCODE,
             [Sales Order Details].vop_ordn           AS ORDERNO,
             'Invoiced SOP'                           AS TYPE,
             [Sales Order Header].ordidate            AS DATE,
             Round([Sales Order Details].vol_qtty /
                   [Sales Order Details].volcsize
                   *
                         [Sales Order Details].volcprcn /
                   [Sales Order Header].ordexch1, 2)
                                                      AS VALUE,
             Round(Round([Sales Order Details].vol_qtty /
                         [Sales Order Details].volcsize *
                                     [Sales Order Details].volcprcn /
                   [Sales Order Header].ordexch1, 2) -
                   [Sales Order Details].casecost *
                   [Sales Order Details].vol_qtty
                   /
                   [Sales Order Details].volcsize, 2) AS MARGIN
      FROM   vopoline AS [Sales Order Details]
             INNER JOIN voporder AS [Sales Order Header]
                     ON [Sales Order Details].vop_ordn =
                        [Sales Order Header].vop_ordn
             INNER JOIN vslname1 AS Customers
                     ON [Sales Order Header].cust_acc = Customers.cust_acc
                        AND [Sales Order Header].cust_acc = Customers.custaccs
             INNER JOIN vstmain1 AS Products
                     ON [Sales Order Details].prd_code = Products.prd_code
      WHERE  ( [Sales Order Header].ordidate >=
               CONVERT(DATETIME, '2021-04-30 23:59:59', 102)
             )
             AND ( [Sales Order Header].ordidate <=
                   CONVERT(DATETIME, '2021-05-12 23:59:59',
                   102)
                 )
             AND ( [Sales Order Header].ordacode <> 'S' )
             AND ( [Sales Order Header].ord_stat = 5 )
             AND ( Products.grp_code >= '001' )
             AND ( Customers.custanl1 = 'NO' )
              OR ( [Sales Order Header].ordidate >=
                   @startdate
                 )
                 AND ( [Sales Order Header].ordidate <=
                       @enddate
                     )
                 AND ( [Sales Order Header].ordacode = 'S' )
                 AND ( [Sales Order Header].ord_stat = 5 )
                 AND ( Products.grp_code >= '001' )
                 AND ( Customers.custanl1 = 'NO' )
                 AND ( [Sales Order Header].credstyl = 1 )
      ORDER  BY custcode,
                orderno
  END

go

Which throws the error:

Invalid column name 'VALUE'.

I've defined VALUE, so I'm not sure why it's not detecting it.

EDIT:

I've reformatted the code, and it now does not show the invalid column error, however it doesn't seem to be inputting anything to the table.

You cannot refer to computed columns in the same `SELECT` that's computing them. Use a CTE or repeat the expression. Also, the `ORDER BY` makes no sense semantically -- inserted rows do not have an order. If you're adding this so "you don't have to `ORDER BY` later when `SELECT`ing", that's not going to work -- without an `ORDER BY` selected rows have no order, regardless of how they're stored on disk. — Jeroen Mostert, May 12 '21 at 11:05
Also, *please* learn to make **good** use of whitespace and linebreaks. That SQL was *very* difficult to consume with the lack of either. — Thom A, May 12 '21 at 11:06
The parenthesis (`()`) around your objects in the `FROM` don't make a lot of sense either. Nor does putting the `ON` for `VOPORDER` *after* the `ON` for `VOPORDER` when both are `INNER JOIN`s. — Thom A, May 12 '21 at 11:09
I also strongly suggest you use shorter aliases, such as `sh` for `Sales Order Header`. And I hope you fully understand `AND/OR` precedence — Charlieface, May 12 '21 at 13:45
Does this answer your question? [Possible to store value of one select column and use it for the next one?](https://stackoverflow.com/questions/65818438/possible-to-store-value-of-one-select-column-and-use-it-for-the-next-one) — Charlieface, May 12 '21 at 13:47
Do you realize the parameters passed are 1) overwritten in the case of @startdate and 2) never used in the case of @enddate? — Zorkolot, May 12 '21 at 14:14

score 0 · Answer 1 · answered May 12 '21 at 11:12

0

The select cannot refer to other calculated columns, this happens at a different stage in the query process and is unavailable. you need to either repeat the criteria or use the [value] in an outer select that wraps the first one.

You would find it easier to read if you separated your selected columns onto separate lines or at least in logical groups, and removed the unecessary parantheses.

Try the following in your select

Round(Round((([Vol_Qtty]/[VolCsize])*[VolCprcN])/[OrdExch1],2)-([CaseCost]*[Vol_Qtty]/[VolCsize]),2) AS MARGIN

answered May 12 '21 at 11:12

Stu

30,392
6
14
33

I've done this, and there's no longer an error, however it's not actually pasting into the database table – James Timms May 12 '21 at 11:24
1

@JamesTimms - have you run the `select` on its own and confirmed the results are as expected and the columns in the results correlate with the columns of the `insert` statement – Stu May 12 '21 at 11:25
I ran a select query and it didn't do anything, however when i put the select query in it's own query instead of a stored procedure it works. It just won't pull any data within the stored procedure – James Timms May 12 '21 at 12:06
It would appear this addresses the question you asked, you now have a separate issue, which I suspect will require a proper [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) to help – Stu May 12 '21 at 13:23

SQL Stored Procedure - Invalid Column Name

1 Answers1