Query problem

Access Junkie · #1 (**permalink**) 07-26-10, 00:41

This may be somewhat hard to understand, I know it's certainly going to be hard to explain so please try to bear with me and feel free to ask any questions.

The query below works.

Code:

SELECT DISTINCT
SMP.TREATMENTID
,SMPPRD.SAMPLEPRODUCTCODE
,
(
SELECT
SPCNUM
FROM
(
SELECT DISTINCT
SMP2.TREATMENTID
,SMPPRD2.SAMPLEPRODUCTCODE
,ROWNUM AS SPCNUM
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 = 301
ORDER BY SMPPRD2.SAMPLEPRODUCTCODE
)
WHERE
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;

What I want to do is instead of saying;

AND SMP2.TREATMENTID = 301

say

AND SMP2.TREATMENTID = SMP.TREATMENTID

This doesn't work because it does not recognise SMP as an alias for a table.

You might notice that this query is nested twice. On the first nest level I say;

WHERE
SAMPLEPRODUCTCODE = SMPPRD.SAMPLEPRODUCTCODE

SMPPRD.SAMPLEPRODUCTCODE is actually coming from the query above in the same way that I want to get the SMP.TREATMENTID.

The difference between this and what I want to do is the nest level. What I want to do is on the second nest level.

It may be that what I'm trying to do simply is not possible or perhaps there is another completely different approach that might do what I am trying to do. I don't know but I'm willing to accept either answer.

dav1mo · #2 (**permalink**) 07-26-10, 08:32

Try placing the keyword TABLE in front of your nested expressions. Also, for readability sake, you should name each of your nested expressions.
Dave

andrewst · #3 (**permalink**) 07-26-10, 10:37

The forum's CODE tags are useful, but only if the code you put inside them has actually been formatted! Try some indentation maybe?

anacedent · #4 (**permalink**) 07-26-10, 10:42

Code:

SELECT DISTINCT smp.treatmentid,
                smpprd.sampleproductcode,
                (SELECT spcnum
                 FROM   (SELECT DISTINCT smp2.treatmentid,
                                         smpprd2.sampleproductcode,
                                         ROWNUM AS spcnum
                         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 = 301
                         ORDER  BY smpprd2.sampleproductcode)
                 WHERE  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;

andrewst · #5 (**permalink**) 07-26-10, 11:27

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.

Access Junkie · #6 (**permalink**) 07-29-10, 03:27

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