This part...
Code:
SELECT spcnum
FROM (SELECT DISTINCT smp2.treatmentid,
smpprd2.sampleproductcode,
ROWNUM AS spcnum
... appears to be getting a count of the distinct treatmentid and sampleproductcode combinations? In which case perhaps the query can be re-written as:
Code:
SELECT DISTINCT smp.treatmentid,
smpprd.sampleproductcode,
(SELECT COUNT(DISTINCT smp2.treatmentid||':'||smpprd2.sampleproductcode)
FROM gpl.gpl_samples smp2,
gpl.gpl_sampleproductlists smpprdlst2,
gpl.gpl_sampleproducts smpprd2
WHERE smpprdlst2.sampleid = smp2.sampleid
AND smpprdlst2.sampleproductid = smpprd2.sampleproductid
AND smp2.treatmentid = smp.treatmentid
AND sampleproductcode = smpprd.sampleproductcode) AS spcnum
FROM gpl.gpl_samples smp,
gpl.gpl_sampleproductlists smpprdlst,
gpl.gpl_sampleproducts smpprd
WHERE smpprdlst.sampleid = smp.sampleid
AND smpprdlst.sampleproductid = smpprd.sampleproductid
AND smp.trialid = 3163;
Having removed a level of nesting you should now be able to reference smp.treatmentid in the scalar subquery.
Note that I had to concatenate the 2 column values together to use COUNT(DISTINCT x); I used a colon, but you must ensure that whatever text you use as a delimiter will not appear in the actual values so there is no ambiguity.