SQL Error when using LIKE CASE WHEN with subquery

I’m attempting to write an SQL statement that use Excel parameters to insert data via a drop down menu. This normally works great, but I think because I’m using a subquery in this case I’m getting the error "The multi-part identifier could not be bound." The subquery is used to create a sum of the quantity of an item purchased within the past week. Here’s the code:

SELECT DISTINCT ITMMASTER.ITMREF_0 AS ITEMCODE, ITMMASTER.ITMDES1_0 AS ITEMDESC, Sum(ITMMVT.PHYSTO_0) AS AVAILSTOCK, Sum(ITMMVT.ORDSTO_0) AS ONORDER, Sum(ITMMVT.PHYALL_0) AS ALLOCATED, Sum(ITMFACILIT.MAXSTO_0) AS MAX,     INVQTY = (SELECT Sum(SINVOICED.QTY_0) AS INVQTY           FROM x3v11.PROD.SINVOICED SINVOICED          WHERE SINVOICED.INVDAT_0 BETWEEN DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0))              AND DATEADD(DAY,4,DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)))             AND SINVOICED.ITMREF_0 = ITMMASTER.ITMREF_0     ),  ITMMASTER.ITMWEI_0 AS WEIGHT, ITMCOST.CSTTOT_0 AS COST, ITMBPS.BPSNUM_0 AS VENDOR, BPSUPPLIER.BPSNAM_0 AS VENDNAME, ITMMASTER.TSICOD_2 AS CAT FROM x3v11.PROD.ITMMASTER ITMMASTER     LEFT OUTER JOIN x3v11.PROD.ITMMVT ITMMVT ON ITMMASTER.ITMREF_0 = ITMMVT.ITMREF_0     LEFT OUTER JOIN x3v11.PROD.ITMFACILIT ITMFACILIT ON ITMMVT.ITMREF_0 = ITMFACILIT.ITMREF_0 AND ITMMVT.STOFCY_0 = ITMFACILIT.STOFCY_0     LEFT OUTER JOIN x3v11.PROD.ITMCOST ITMCOST ON ITMMVT.ITMREF_0 = ITMCOST.ITMREF_0  AND ITMMVT.STOFCY_0 = ITMCOST.STOFCY_0     LEFT OUTER JOIN x3v11.PROD.ITMBPS ITMBPS ON ITMMASTER.ITMREF_0 = ITMBPS.ITMREF_0     LEFT OUTER JOIN x3v11.PROD.BPSUPPLIER BPSUPPLIER ON ITMBPS.BPSNUM_0 = BPSUPPLIER.BPSNUM_0 WHERE ITMCOST.STOFCY_0 <> '115'     AND ITMFACILIT.MAXSTO_0 <> 0     AND ITMBPS.PIO_0 <> 99     AND ITMBPS.BPSNUM_0 LIKE CASE WHEN ? IS NOT NULL THEN ? ELSE '%' END GROUP BY ITMMASTER.ITMREF_0, ITMMASTER.ITMDES1_0, ITMMASTER.ITMWEI_0, ITMCOST.CSTTOT_0, ITMBPS.BPSNUM_0, BPSUPPLIER.BPSNAM_0, ITMMASTER.TSICOD_2 ORDER BY ITMMASTER.ITMREF_0 
Add Comment
1 Answer(s)

I think you have specified the syntax for the subquery incorrectly:

INVQTY = (SELECT Sum(SINVOICED.QTY_0) AS INVQTY       FROM x3v11.PROD.SINVOICED SINVOICED      WHERE SINVOICED.INVDAT_0 BETWEEN  DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0))          AND DATEADD(DAY,4,DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)))         AND SINVOICED.ITMREF_0 = ITMMASTER.ITMREF_0 ), 

I think it should be:

(SELECT Sum(SINVOICED.QTY_0)      FROM x3v11.PROD.SINVOICED SINVOICED      WHERE SINVOICED.INVDAT_0 BETWEEN  DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0))          AND DATEADD(DAY,4,DATEADD(WEEK,-1,DATEADD(WEEK,DATEDIFF(WEEK,0,GETDATE()),0)))         AND SINVOICED.ITMREF_0 = ITMMASTER.ITMREF_0 ) AS INVQTY, 
Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.