# 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 `

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, `