Firstly, thank you for your responses and sorry for my delayed reply.
Also, sorry for the unformatted code.
Quote:
Originally Posted by andrewst
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.
|
While your example does work, it does not do what I need it to.
What I need is an ID that will increment from 1 to how many products there happen to be for a treatment. I need this in order to pivot the data such that if there are 6 unique products for 10 treatments but any 1 treatment has a maximum of 3 products, I can pivot it all into 3 columns.
Luckily, there is someone that I work with that came up with a solution that works perfectly. See below.
Code:
WITH QRY as
(
SELECT
SAMPLEID
,SAMPLEPRODUCTID
, ROWNUM AS IDX
FROM
(
SELECT DISTINCT
SMP.SAMPLEID
,SMPPRD.SAMPLEPRODUCTID
FROM
GPL.GPL_SAMPLES SMP
,GPL.GPL_SAMPLEPRODUCTLISTS SMPPRDLST
,GPL.GPL_SAMPLEPRODUCTS SMPPRD
WHERE
SMPPRDLST.SAMPLEID = SMP.SAMPLEID
AND SMPPRD.SAMPLEPRODUCTID = SMPPRDLST.SAMPLEPRODUCTID
AND SMP.SAMPLEBARCODE NOT LIKE '%P'
AND SMP.TRIALID = 2708
ORDER BY SAMPLEID,SAMPLEPRODUCTID
)
)
SELECT
Q1.SAMPLEID
, Q1.SAMPLEPRODUCTID
, Q1.IDX - Q2.MINIDX + 1 AS TRN
FROM QRY Q1,
(
SELECT
SAMPLEID
,MIN(IDX) MINIDX
FROM QRY
GROUP BY SAMPLEID
) Q2
WHERE Q1.SAMPLEID= Q2.SAMPLEID