SQLTeam.com | Weblogs | Forums

Embedding SELECT statement into WHERE clause


#1

I'm trying to embed a select statement with a JOIN into a where statement.

Not sure what the best method is for this, open to suggestions.

Thank you.

WHERE
shitem.forderqty=(SELECT
Sum(shitem.fshipqty)
FROM sorels
INNER JOIN (shmast INNER JOIN shitem ON shmast.fshipno = shitem.fshipno) ON (sorels.fsono = shmast.fcsono) AND (sorels.fenumber = shitem.fenumber))


#2

Something like this, or you could put the derived table into a CTE:

SELECT *
FROM ...
JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty


#3

ugh, when i add

JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty

as a new join i get no returns, what am I missing?


#4

You'll have to post sample data and expected result for us to help. Plus show us the rest of the query.


#5

Thanks for your help Tara!

Not sure how to provide sample data but here's the whole query:

select
soitem.fac as [Fac],
somast.fcompany as [Customer],
shmast.fccompany as [End Customer],
soitem.fsono as [SO No],
soitem.fenumber as [Line Item],
soitem.fquantity as [SO Qty],
shitem.fshipqty as [Ship Qty],
sorels.fnetprice as [Sales Price],
soitem.fpartno as [Part No],
soitem.fpartrev as [Rev],
soitem.fprodcl as [Prod Cls],
soitem.fdesc as [Desc],
shmast.fshipdate as [Ship Dt],
SHITEM.FITEMNO AS [Ship Ln Item],
soitem_ext.field3 as [Prom Dt],
soitem_ext.field9 as [Reason],

CASE

	WHEN shmast.fshipdate >= '20150731'
		AND shmast.fshipdate < '20150829' THEN '1'
		
	WHEN shmast.fshipdate >= '20150830'
		AND shmast.fshipdate < '20150927' THEN '2'
		
	WHEN shmast.fshipdate >= '20150927'
		AND shmast.fshipdate < '20151031' THEN '3'
		
	WHEN shmast.fshipdate >= '20151101'
		AND shmast.fshipdate < '20151128' THEN '4'
		
		WHEN shmast.fshipdate >= '20151129'
		AND shmast.fshipdate < '20151226' THEN '5'
		
		WHEN shmast.fshipdate >= '20151227'
		AND shmast.fshipdate < '20160130' THEN '6'
		
		WHEN shmast.fshipdate >= '20160201'
		AND shmast.fshipdate < '20160227' THEN '7'
		
		WHEN shmast.fshipdate >= '20160228'
		AND shmast.fshipdate < '20160326' THEN '8'
		
		WHEN shmast.fshipdate >= '20160327'
		AND shmast.fshipdate < '20160430' THEN '9'
		
		WHEN shmast.fshipdate >= '20160501'
		AND shmast.fshipdate < '20160528' THEN '10'
		
		WHEN shmast.fshipdate >= '20160529'
		AND shmast.fshipdate < '20160625' THEN '11'
		
		WHEN shmast.fshipdate >= '20160626'
		AND shmast.fshipdate < '20160801' THEN '12'
	ELSE 'X'
END AS FM,
CASE 
	WHEN shmast.fshipdate >= '20140801'
		AND shmast.fshipdate < '20150801' THEN '2015'
	WHEN shmast.fshipdate >= '20150801'
		AND shmast.fshipdate < '20160731' THEN '2016'
	ELSE 'X'
END AS FY,

DATEDIFF(DD, shmast.fshipdate,SOITEM_EXT.FIELD3) AS [Days Late],
/CHANGES*****************/

CASE
when shmast.fshipdate > SOITEM_EXT.FIELD3 then '1'
else '0'
end as [Days_Early_Count]*/

/END CHANGES********/
from soitem

JOIN soitem_ext--l
on soitem_ext.fkey_id=soitem.identity_column
--and soitem_ext.field3<>cast('1900-01-01 00:00:00' as datetime)

LEFT JOIN somast--l
on somast.fsono=soitem.fsono

JOIN shmast
on shmast.fcsono=somast.fsono
and shmast.fcsorev=somast.fsorev
--and shmast.fshipdate>soitem_ext.field3

LEFT JOIN sorels--l
on sorels.fsono=soitem.fsono
and sorels.fpartno=soitem.fpartno
and sorels.finumber=soitem.finumber

JOIN shitem ON
shmast.fshipno = shitem.fshipno
and
sorels.fsono = shmast.fcsono
and
sorels.fenumber = shitem.fenumber
and
soitem.fenumber = shitem.fenumber

JOIN (
SELECT
Sum(shitem.fshipqty) AS fshipqty
FROM sorels
INNER JOIN shmast ON sorels.fsono = shmast.fcsono
INNER JOIN shitem ON shmast.fshipno = shitem.fshipno AND sorels.fenumber = shitem.fenumber
) t ON shitem.forderqty=t.fshipqty

where
soitem.fprodcl not in ('54','55','70','71','72','73','76','77','78','79','7A','FR','SH')
and
shmast.fshipdate BETWEEN '4/16/2016' AND '4/24/2016'

order by soitem.fsono ASC