Results 1 to 6 of 6

Thread: Query problem

  1. #1
    Join Date
    Jun 2006
    Posts
    72

    Unanswered: Query problem

    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.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Try placing the keyword TABLE in front of your nested expressions. Also, for readability sake, you should name each of your nested expressions.
    Dave

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The forum's CODE tags are useful, but only if the code you put inside them has actually been formatted! Try some indentation maybe?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  6. #6
    Join Date
    Jun 2006
    Posts
    72
    Firstly, thank you for your responses and sorry for my delayed reply.

    Also, sorry for the unformatted code.

    Quote Originally Posted by andrewst View Post
    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
    Last edited by Access Junkie; 07-29-10 at 04:31.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •