7 Replies Latest reply: Sep 18, 2014 2:35 AM by user13117585 RSS

    Complex SELECT generating records

    user13117585

      Hello guys,

       

      I'm here to ask your help with a very complex SELECT. I wonder if it's doable with SELECT or do I need something else to generate my output. Any advise is welcome. I'm using Oracle 11g.

       

      Imagine the following table structure. I simplified it and kept only the minimum attributes.

       

      -- The technical id is used for foreign keys. [document_id is a business key and the combination of document_id version is unique]
      CREATE TABLE documents 
      (  
         technical_id number(10), 
         document_id number(10), 
         version number(10), 
         title varchar2(100)
      );
      
      
      CREATE TABLE attachments 
      ( 
        technical_id number(10), -- primay key
        attachment_id number(10),  -- concat of attachment_id and version is unique!
        version number(10), 
        language varchar2(100),
        title varchar2(100)
      );
      
      
      CREATE TABLE doc_attachments 
      (
        document_id number(10), 
        attachment_id number(10)
      );
      

       

      A document can have different version and to each version can be linked attachments of different languages and differnt version. Let's take the normal scenario.

      Imagine my "First document" has 3 versions. We will have in the table documents 3 records:

       

      INSERT INTO documents(technical_id, document_id, version, title) values(9912, 1, 1, 'First document');
      -- title can be updated for example to have a different version
      INSERT INTO documents(technical_id, document_id, version, title) values(45,   1, 2, 'FIRST doc.'); 
      INSERT INTO documents(technical_id, document_id, version, title) values(789,  1, 3, 'First document!');
      

      Now, imagine I have the following attachments.

       

      INSERT INTO attachments(technical_id, attachment_id, version, language, title) values(11,  1, 1, 'en', 'attachment en');
      

       

      Imagine the attachment with technical 11 is linked to document in version 1.

       

      INSERT INTO doc_attachments(document_id, attachment_id) VALUES(9912, 11);
      

       

      Now, I want a SELECT that returns this:

       

      doc_technical_id  att_technical_id doc_version att_version att_language      
                  9912                11           1           1           en 
                    45                11           1           1           en 
                   789                11           1           1           en 
      

        

      The logic is since I received an attachment for a lower version of the document, whenever I don't have it in a "more recent version" (version is bigger), I should SELECT the previous attachment.

      This becomes more complex when you have many revisions of attachments. If I change this scenarion and add this:

       

      INSERT INTO attachments(technical_id, attachment_id, version, language, title) values(56,  1, 2, 'en', 'attachment en');
      

       

      And have the following join:

       

      INSERT INTO doc_attachments(document_id, attachment_id) VALUES(789, 56);
      

       

      I should have the following result:

       

      doc_technical_id  att_technical_id doc_version att_version att_language      
                  9912                11           1           1           en 
                    45                11           1           1           en 
                   789                56           1           2           en 
      

       

       

       

      This illustrates better my SELECT. I need to "copy" the previous attachment for more recent document versions. Any help on how I can do that??

       

      Thank you guys for any advise you can provide.  

        • 1. Re: Complex SELECT generating records
          Frank Kulash

          Hi,

           

          So, when there is no attachment information about a given version, you want to repeat the last attachement information that was given; is that right?

          That sounds like a job for the analytic LAST_VALUE function (with the IGNORE NULLS option), like this:

           

          SELECT    d.technical_id   AS doc_technical_id

          ,         LAST_VALUE (a.technical_id IGNORE NULLS)

                        OVER ( PARTITION BY  d.document_id

                               ORDER BY      d.version

                             )       AS att_technical_id

          ,         d.document_id    AS doc_version

          ,         LAST_VALUE (a.version IGNORE NULLS)

                        OVER ( PARTITION BY  d.document_id

                               ORDER BY      d.version

                             )       AS att_version

          ,         LAST_VALUE (a.language IGNORE NULLS)

                        OVER ( PARTITION BY  d.document_id

                               ORDER BY      d.version

                             )       AS att_language

          FROM              documents        d

          LEFT OUTER JOIN   doc_attachments  da  ON  d.technical_id    = da.document_id

          LEFT OUTER JOIN   attachments      a   ON  da.attachment_id  = a.technical_id

          ORDER BY  d.document_id

          ,         d.version

          ;

          Thanks for posting the CREATE TABLE and INSERT statements; that's very helpful!

          • 2. Re: Re: Complex SELECT generating records
            user13117585

            Hello Frank and thank your for your answer. It's exactly that for one language. But, when we add additional languages, it does not produce the right result.

             

            Imagine, I have another attachment:

             

            INSERT INTO attachments(technical_id, attachment_id, version, language, title) values(65,  3, 1, 'ar', 'attachment ar');
            
            

             

            And that attachment is linked to document version 2

             

            INSERT INTO doc_attachments(document_id, attachment_id) VALUES(45, 65); 
            
            

             

            I should have the following result

             

            DOC_TECHNICAL_ID       ATT_TECHNICAL_ID       DOC_VERSION            ATT_VERSION            ATT_LANGUAGE      
            ---------------------- ---------------------- ---------------------- ---------------------- -------------------
            9912                   11                     1                      1                      en                
            
            
            45                     11                     2                      1                      en
            45                     65                     2                      1                      ar 
            
            
            789                    65                     3                      1                      ar                
            789                    56                     3                      2                      en                
            
            

             

             

            We shouls keep the last attachment details for each language for each document version. Is it more clear?

             

            Thank you Frank

            • 3. Re: Complex SELECT generating records
              Frank Kulash

              Hi,

               

              This is in reply to your original question.  I was typing this when you posted reply #2, above.  I had not read reply #2 when I wrote the following:

               

               

              In the attachments table, can version of language be NULL?

              For example, if instead of the second attachment that you posted:

              INSERT INTO attachments(technical_id, attachment_id, version, language, title) values(56,  1, 2, 'en', 'attachment en');

              we had this:

              INSERT INTO attachments(technical_id, attachment_id, version, language, title) values(56,  1, 2, NULL, 'attachment en');

              would you want the row with att_technical_id=56 to have att_language='en' (because that's the most recent language), or would you want att_language to be NULL (because the language on the most recent attachment is NULL)?

              The query I posted earlier would have att_language='en' on that row.

              If you want NULL on that row, then you can do something like this:

               

              WITH    got_att_technical_id    AS

              (

                  SELECT    d.technical_id   AS doc_technical_id

                  ,         d.version

                  ,         LAST_VALUE (a.technical_id IGNORE NULLS)

                                OVER ( PARTITION BY  d.document_id

                                       ORDER BY      d.version

                                     )       AS att_technical_id

                  ,         d.document_id    AS doc_version

                  FROM              documents        d

                  LEFT OUTER JOIN   doc_attachments  da  ON  d.technical_id    = da.document_id

                  LEFT OUTER JOIN   attachments      a   ON  da.attachment_id  = a.technical_id

              )

              SELECT    ga.doc_technical_id

              ,         ga.att_technical_id

              ,         ga.doc_version

              ,         ra.version       AS att_version

              ,         ra.language      AS att_language

              FROM             got_att_technical_id  ga

              LEFT OUTER JOIN  attachments           ra  ON  ga.att_technical_id  = ra.technical_id

              ORDER BY  ga.doc_version

              ,         ga.version

              ;

              • 4. Re: Complex SELECT generating records
                user13117585

                No. Language is never null.

                 

                However, your query does not produce the correct result. It produces only 4 rows, and I need 5. Each version of the document should get all the attachment languages that are received before. Did you see my second expected output? If it's not clear, I can explain more with other details.

                 

                THank you for your help. It's very kind.

                • 5. Re: Complex SELECT generating records
                  Frank Kulash

                  Hi,

                  user13117585 wrote:

                   

                  ... Did you see my second expected output? If it's not clear, I can explain more with other details. ...

                  Yes, I saw your reply #2 after I posted my reply #1.  My reply #1 is not an answer to your reply #2.

                  I may have questions, or a solution, later.

                  • 6. Re: Complex SELECT generating records
                    Frank Kulash

                    Hi,

                     

                    I'm not sure I completely understand what you're doing.

                    This gets the results you requested (except for the order of the output rows, which may not matter to you):

                     

                    WITH    doc_attachments_plus    AS

                    (

                        SELECT  d1.document_id

                        ,       d1.version        AS doc_version

                        ,       a1.attachment_id

                        ,       a1.version        AS att_version

                        FROM    doc_attachments  da

                        JOIN    documents        d1  ON  da.document_id    = d1.technical_id

                        JOIN    attachments      a1  ON  da.attachment_id  = a1.technical_id

                    )

                    ,    got_r_num    AS

                    (

                        SELECT  d2.technical_id  AS doc_technical_id

                        ,       a2.technical_id  AS att_technical_id

                        ,       d2.version       AS doc_version

                        ,       a2.version       AS att_version

                        ,       a2.language      AS att_language

                        ,       dap.document_id

                        ,       dap.attachment_id

                        ,       ROW_NUMBER () OVER ( PARTITION BY  dap.document_id

                                                     ,             dap.attachment_id

                                                     ,             d2.version

                                                     ORDER BY      a2.version   DESC

                                                   )    AS r_num

                        FROM    doc_attachments_plus  dap

                        JOIN    documents             d2  ON  dap.document_id     = d2.document_id

                                                          AND dap.doc_version    <= d2.version

                        JOIN    attachments           a2  ON  dap.attachment_id   = a2.attachment_id

                                                          AND dap.att_version    >= a2.version

                    )

                    SELECT    doc_technical_id

                    ,         att_technical_id

                    ,         doc_version

                    ,         att_version

                    ,         att_language

                    FROM      got_r_num

                    WHERE     r_num  = 1

                    ORDER BY  document_id

                    ,         doc_version

                    ,         attachment_id

                    ,         att_version

                    ;

                     

                    Output (from all the sample data you posted):

                     

                         DOC_      ATT_

                    TECHNICAL TECHNICAL    DOC_    ATT_ ATT_

                          _ID       _ID VERSION VERSION LANGUAGE

                    --------- --------- ------- ------- --------

                         9912        11       1       1 en

                           45        11       2       1 en

                           45        65       2       1 ar

                          789        56       3       2 en

                          789        65       3       1 ar

                    • 7. Re: Complex SELECT generating records
                      user13117585

                      Thank you Frank. It's exactly what I needed.