Whoever wrote that SQL statement like that needs to be shot.
Here's the same thing but re-formatted.
It's still too much effort trying to figure out what it's doing though.
If you want totalcost, run the sub-select for just totalcost on its own and see if it works.
SQL code:
SELECT ruddescrip,
supplier = Isnull
(
(
SELECT Max(smname)
FROM tagmaster
LEFT JOIN receivingheader ON rehid = tagsourceid AND tagsourcetype = 'Receiving'
LEFT JOIN productionheader ON ruhid = tagsourceid AND tagsourcetype = 'Production'
INNER JOIN suppliermaster ON smid = rehsuppid
WHERE
(
tagdestid = '$Kiln'
OR
tagdestid IN
(
SELECT a.ruhid
FROM productionheader a
WHERE a.ruhfileno = ''
)
)
AND tagproductid = rudproductid
AND taglocationid = rudlocationid
)
, ''
), /* /supplier */
rudpieces,
rudvolume,
CASE
WHEN pvmvalue IS NOT NULL THEN pvmvalue
ELSE 0
END AS market,
rudcost,
rudtally,
rudpcsperpkg,
pamdescrip = Isnull(pamdescrip, ''),
totalcost =
(
SELECT SUM(rd1.rudtotal)
FROM productiondetail rd1
WHERE rd1.rudcategory = 'Consumed'
AND
(
rd1.rudid = '$Kiln'
OR
rd1.rudid IN
(
SELECT a.ruhid
FROM productionheader a
WHERE a.ruhfileno = ''
)
)
), /* /totalcost */
totalvol = Isnull
(
(
SELECT SUM(a.rudvolume)
FROM productiondetail a
WHERE
(
a.rudid = '$Kiln'
OR
a.rudid IN
(
SELECT b.ruhid
FROM productionheader b
WHERE
b.ruhfileno = ''
)
)
AND a.rudcategory = 'Consumed'
)
, 0
) /* /totalvol */
FROM productiondetail
LEFT JOIN productvaluemaster ON rudproductid = pvmproductid AND pvmdoctype = 'GENERAL'
LEFT JOIN productmaster ON prdid = rudproductid
LEFT JOIN productattributemaster ON pamdocid = prdgradeid AND pamdoctype = 'Grade'
WHERE rudcategory = 'Consumed'
AND
(
rudid = '$Kiln'
OR
rudid IN
(
SELECT a.ruhid
FROM productionheader a
WHERE a.ruhfileno = ''
)
)
ORDER BY ruddescrip