Have Geek Brain

Oracle Query Tuning (Duplicate Table Access)

I am trying to continue customizing this request. The request returns the status for three different tests for each sample. However, if I want to further filter the returned samples, I have to put conditions in both " SELECT ... FROM sample ...

" queries .

Is it possible to rewrite this query by referencing the table sample

only once?

SELECT   sample_id,
         created_on,
         s_acid,
         s_ph,
         s_titr
  FROM 
      (SELECT sample_id, -- Rows w/ same sample_id to columns
              MAX (CASE WHEN tst_tmpl_id = 36 THEN status END) AS s_acid,
              MAX (CASE WHEN tst_tmpl_id = 43 THEN status END) AS s_ph,
              MAX (CASE WHEN tst_tmpl_id = 66 THEN status END) AS s_titr
         FROM (SELECT test_id, test_tmpl_id, sample_id FROM test)
         JOIN (SELECT sample_id      FROM sample WHERE sam_tmpl_id = 18)
              USING (sample_id)
         GROUP BY   sample_id
       )
        -- get other sample fields 
  JOIN (SELECT sample_id, created_on FROM sample WHERE sam_tmpl_id = 18)
       USING (sample_id)
+1


a source to share


1 answer 


WITH q AS
        (
        SELECT  sample_id, created_on
        FROM    sample
        WHERE   sam_tmpl_id = 18
        )
SELECT  sample_id,
        created_on,
        s_acid,
        s_ph,
        s_titr
FROM    (
        SELECT  sample_id, -- Rows w/ same sample_id to columns
                MAX (CASE WHEN tst_tmpl_id = 36 THEN status END) AS s_acid,
                MAX (CASE WHEN tst_tmpl_id = 43 THEN status END) AS s_ph,
                MAX (CASE WHEN tst_tmpl_id = 66 THEN status END) AS s_titr
        FROM    (
                SELECT  test_id, test_tmpl_id, sample_id
                FROM    test
                )
        JOIN    q
        USING   (sample_id)
        GROUP BY
                sample_id
        )
JOIN    q
USING   (sample_id)


+3


a source






More articles:


All Articles