13 Replies Latest reply: Jan 3, 2013 6:45 PM by Frank Kulash RSS

    values in both result sets

    981609
      I have 2 queries, if the "name" in the first query is also in the second query I want to get the informaiton from second
      query. If you look at output below for both queries, you wil see that "DBBACKUP" is in both result sets. So I want to use the second result set to print out "DBBACKUP" line and the first query output to print out the rest of the informaiton since it does not appear in the second query.

      The reason I am doing this is because the % used for DBBACKUP is not accurate in the first result set. This is an ACFS FS opposed to the other lines, which are justr disk groups.

      Can somebody help me put these two queries together and keep the results from each query grouped togehter in the ouput.
      QUERY 1
      =======
      
      SELECT
          name                                     group_name
        , sector_size                              sector_size
        , block_size                               block_size
        , allocation_unit_size                     allocation_unit_size
        , state                                    state
        , type                                     type
        , total_mb                                 total_mb
        , (total_mb - free_mb)                     used_mb
        , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
         from v$asm_diskgroup order by name;
      
      
       QUERY 1 OUTPUT
      ===============
      
      Disk Group            Sector   Block   Allocation
      Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
      -------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
      DBBACKUP                 512   4,096    1,048,576 MOUNTED     EXTERN          92,160         91,238     99.00
      FRA                            512   4,096    1,048,576 MOUNTED     EXTERN         829,413          8,016        .97
      OCRVOTEDG               512   4,096    1,048,576 MOUNTED    HIGH               10,240          1,256     12.27
      
      
      
      QUERY 2
      ==========
      SELECT dg.name, v.mountpath, vs.total_mb/1024 as "TOTAL_GB", ROUND(100*(vs.total_mb-vs.free_mb)/vs.total_mb) as "USED%" 
      FROM v$asm_diskgroup dg, v$asm_volume v, v$asm_acfsvolumes vs
      WHERE dg.group_number = v.group_number
      AND vs.fs_name = v.mountpath;
      
      
      QUERY 2 OUTPUT
      ===============
      NAME       MOUNTPATH                                       TOTAL_GB      USED%
      ---------- --------------------------------------------- ---------- ----------
      DBBACKUP   /acfs/dbbackup                                        89          1
      
      
      
      OUTPUT I AM LOOKING FOR
      ==================
      
      
      Disk Group            Sector   Block   Allocation
      Name                    Size    Size    Unit Size State       Type   Total Size (MB) Used Size (MB) Pct. Used
      -------------------- ------- ------- ------------ ----------- ------ --------------- -------------- ---------
      FRA                            512   4,096    1,048,576 MOUNTED     EXTERN         829,413          8,016        .97
      OCRVOTEDG               512   4,096    1,048,576 MOUNTED    HIGH               10,240          1,256     12.27
      
      
      NAME       MOUNTPATH                                       TOTAL_GB      USED%
      ---------- ---------------------------------------------                  ----------          ----------
      DBBACKUP   /acfs/dbbackup                                        89          1
      
      Thanks to all who answer
        • 1. Re: values in both result sets
          Frank Kulash
          Hi,

          It sounds like you want a UNION ALL of those 2 result sets in a sub-query. That means you'll need to contrive the results so that both queries produce the same number of columns, and basically the same data types in corresponding columns. Include literal NULLs in the SELECT clauses when necessary.
          Add a literal (say 1 for query 1, and 2 for query 2) to each SELECT clause. Let's call this q_num. In another sub-query, use
          ROW_NUMBER () OVER ( PARTITION BY  group_name
                               ORDER BY          q_num     DESC
                       )         AS r_num
          and then, in the main query, select only the rows where r_num = 1.
          Depending on your data and your requirements, you want want to use DENSE_RANK instead of ROW_NUMBER.


           

          I hope this answers your question.
          If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}
          • 2. Re: values in both result sets
            981609
            Simple example
            SQL> select * from t1;
            
                     A B
            ---------- ----------
                     1 TAB1
                     2 TAB1
            
            SQL> select * from t2;
            
                     A B
            ---------- ----------
                     1 TAB2
            
            
            desitered output
            
                   A B
            ---------- ----------
                     1 TAB2  <<  Data from table t2
                     2 TAB1  <<  Data from table t1
            In my above query you mentioned that the number of cols have to match, will the query work if just print out
            name and % used only for both queries?

            Can u provide an example using t1/t2 and I will try to figure out how to deal with the original queries?
            • 3. Re: values in both result sets
              Frank Kulash
              Hi,
              978606 wrote:
              ... In my above query you mentioned that the number of cols have to match, will the query work if just print out
              name and % used only for both queries?
              Yes; as long as both queries are producing 2 columns, and the respective data types are similar (not necessarily exactly the same), then you can do a UNION.
              Can u provide an example using t1/t2 and I will try to figure out how to deal with the original queries?
              Sure. Post CREATE TABLE and INSERT statements. The simpler your data is, the less excuse you have for not doing that.
              It would be a better example if you included at least 1 column that occurs in one result set, but not the other.

              Edited by: Frank Kulash on Jan 2, 2013 3:56 PM

              You might want something like:
              WITH     got_q_num     AS
              (
                   SELECT     a, b, c
                   ,     1     AS q_num
                   FROM     t1
                  UNION ALL
                       SELECT  a, b, NULL
                   ,     2     AS q_num
                   FROM     t2
              )
              ,     got_r_num     as
              (
                   SELECT     a, b, c
                   ,     DENSE_RANK () OVER ( PARTITION BY  a
                                             ORDER BY          q_num  DESC
                                     )         AS r_num
                   FROM    got_q_num
              )
              SELECT       a, b, c
              FROM       got_r_num
              WHERE       r_num     = 1
              ORDER BY  a     -- if needed
              ;
              Here, t1.c is something you only need when the results come from t1; there is no corresponding column in t2.

              Again, whether you want DENSE_RANK or ROW_NUMBER depends on your data and your requirements. This problem hinges on there being (sometimes, but not always) matching rows in both tables. What exactly does it mean "to match"? Do 2 rows match if they have the same value of column a? What results do you want if there are matching rows in one result set or the other, before you even combine them? If that's an issue at all, I'm guessing you want DENSE_RANK.
              • 4. Re: values in both result sets
                Etbin
                Maybe
                select t1.a,nvl(t2.b,t1.b) b
                  from t1,t2
                 where t1.a = t2.a(+)
                Regards

                Etbin
                • 5. Re: values in both result sets
                  981609
                  I tried using one of the above suggestions. The "name" appears to be correct but the values does not. I want to use
                  the "pct used" from v$asm_acfsvolumes vs if the value is in that table else I want to use the "pct used" from v$asm_diskgroup dg

                  can somebody tell me how to make this query work?

                  COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
                  
                  select dg.name,nvl( ROUND((1- (vs.free_mb / vs.total_mb))*100, 2),
                                      ROUND((1- (dg.free_mb / dg.total_mb))*100, 2)) "pct used"
                  from v$asm_diskgroup dg, v$asm_volume v, v$asm_acfsvolumes vs
                  WHERE dg.group_number = v.group_number(+);
                  
                  
                  
                  NAME                             pct used
                  ------------------------------          ----------
                  DBBACKUP                              .77
                  FRA                                        .77
                  OCRVOTEDG                           .77
                  • 6. Re: values in both result sets
                    Frank Kulash
                    Hi,
                    978606 wrote:
                    I tried using one of the above suggestions. The "name" appears to be correct but the values does not. I want to use
                    the "pct used" from v$asm_acfsvolumes vs if the value is in that table else I want to use the "pct used" from v$asm_diskgroup dg

                    can somebody tell me how to make this query work?
                    Not with as little information as you've given.

                    Please post some sample data (CREATE TABLE and INSERT statements) and the results you want from that data.
                    If it's confusing to have sample tables with the same names as the data dictionary views you really need, then call the sample tables by different names, e.g. my_asm_acfsvolumes.
                    COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
                    
                    select dg.name,nvl( ROUND((1- (vs.free_mb / vs.total_mb))*100, 2),
                    ROUND((1- (dg.free_mb / dg.total_mb))*100, 2)) "pct used"
                    from v$asm_diskgroup dg, v$asm_volume v, v$asm_acfsvolumes vs
                    WHERE dg.group_number = v.group_number(+);
                    You're doing a cross-join with the vs table, that is, every row in vs will be joined to every row in the joined dg and v tables. Is that what you want?

                    Depending on your data and your requirements, you may need an outer join, perhaps a FULL OUTER JOIN.
                    • 7. values in both result sets
                      981609
                      First let me thank you all for you help, its greatly appreciated.

                      I broke down the views into simple tables and only used the columns I need.

                      Table t1/t2 are linked through the column "group_number"

                      Table t2/t3 are linked through the column "MOUNTPATH" and "FS_NAME" respecively

                      What I need is a query that does this:

                      If t1->t2->t3 than I want to take the values (total_mb, free_mb) this equation ROUND((1- (free_mb / total_mb))*100, 2) pct_used from table t3, else take the values (total_mb, free_mb) from t1.

                      [ciode]

                      My output should look something like this:


                      NAME pct_used
                      ---------- ----------
                      DBBACKUP 1 << pct_used should be from t3, name is alwasy t1.name
                      FRA . 97 << pct_used should be from t1 name is alwasy t1.name
                      OCRVOTE 12.28 << pct_used should be from t1 name is alwasy t1.name



                      create table t1
                      (group_number number,
                      name varchar2(30),
                      TOTAL_MB NUMBER,
                      FREE_MB NUMBER) tablespace users


                      insert ALL
                      into t1 (name,group_number, free_mb, total_mb) values ('DBBACKUP',1,922, 92160)
                      into t1 (name,group_number, free_mb, total_mb) values ('FRA',2,821397,829413)
                      into t1 (name,group_number, free_mb, total_mb) values ('OCRVOTEDG',3,8984,10240)
                      SELECT 1 FROM DUAL;


                      create table t2
                      (group_number number,
                      MOUNTPATH varchar2(1024)) tablespace users

                      insert into t2 (group_number, mountpath) values
                      (1,'/acfs/dbbackup');


                      create table t3
                      (FS_NAME varchar2(1024),
                      TOTAL_MB NUMBER,
                      FREE_MB NUMBER) tablespace users;

                      insert into t3 (fs_name, TOTAL_MB, FREE_MB) values
                      ('/acfs/dbbackup', 91136, 90435.9531);

                      • 8. Re: values in both result sets
                        Frank Kulash
                        Hi,
                        Here's one way:
                        WITH     got_numbers AS
                        (
                             SELECT       t1.name
                             ,        CASE
                                       WHEN  t3.fs_name  IS NOT NULL
                                       THEN  t3.total_mb
                                       ELSE  t1.total_mb
                                    END     AS total_mb
                             ,        CASE
                                       WHEN  t3.fs_name  IS NOT NULL
                                       THEN  t3.free_mb
                                       ELSE  t1.free_mb
                                    END     AS free_mb
                             FROM           t1
                             LEFT OUTER JOIN      t2   ON   t2.group_number  = t1.group_number
                             LEFT OUTER JOIN      t3   ON   t3.fs_name         = t2.mountpath
                        )
                        SELECT       name
                        ,       ROUND ( ( 1 - ( free_mb 
                                               / total_mb
                                       )
                                    ) * 100
                                  , 2
                                  )     AS pct_used
                        FROM       got_numbers
                        ORDER BY  name
                        ;
                        978606 wrote:My output should look something like this:

                        NAME pct_used
                        ---------- ----------
                        DBBACKUP 1 << pct_used should be from t3, name is alwasy t1.name
                        FRA . 97 << pct_used should be from t1 name is alwasy t1.name
                        OCRVOTE 12.28 << pct_used should be from t1 name is alwasy t1.name
                        The output I get dor dbabackup is different:
                        NAME         PCT_USED
                        ---------- ----------
                        DBBACKUP          .77
                        FRA               .97
                        OCRVOTEDG       12.27
                        If you really want 1.00, explain how you get it.
                        • 9. values in both result sets
                          981609
                          The results are correct.. I had a typo... thank you all for your help
                          • 10. Re: values in both result sets
                            981609
                            I am really noit sure wht the got_numbers thing does can this be re-wrriten not to used that syntax?
                            Ie remove
                            WITH got_numbers AS
                            (
                            )
                            FROM got_number

                            to use something like this

                            FROM t1, t2, t3
                            • 11. Re: values in both result sets
                              Frank Kulash
                              Hi,
                              978606 wrote:
                              I am really noit sure wht the got_numbers thing does can this be re-wrriten not to used that syntax?
                              Ie remove
                              WITH got_numbers AS
                              (
                              )
                              FROM got_number
                              Why? If I don't know what the problem is, how can I solve it?

                              Here's a way that uses an in-line view isntead of a WITH clause.
                              SELECT       name
                              ,       ROUND ( ( 1 - ( free_mb 
                                                     / total_mb
                                             )
                                          ) * 100
                                        , 2
                                        )     AS pct_used
                              FROM     (       -- got_numbers
                                   SELECT       t1.name
                                   ,        CASE
                                             WHEN  t3.fs_name  IS NOT NULL
                                             THEN  t3.total_mb
                                             ELSE  t1.total_mb
                                          END     AS total_mb
                                   ,        CASE
                                             WHEN  t3.fs_name  IS NOT NULL
                                             THEN  t3.free_mb
                                             ELSE  t1.free_mb
                                          END     AS free_mb
                                   FROM           t1
                                   LEFT OUTER JOIN      t2   ON   t2.group_number  = t1.group_number
                                   LEFT OUTER JOIN      t3   ON   t3.fs_name         = t2.mountpath
                                   )
                              ORDER BY  name
                              ;
                              • 12. Re: values in both result sets
                                981609
                                Sorry for leaving out the facts..

                                When I ran my the query in a regular DB it was accessing viiews and I saw that the numbers were not commig out correctly for total_mb and free_mb. So I than ran it in an ASM instance and the values came out correctly.

                                When you provided me the query with the "WITH got_numbers AS" clause this produce an error:


                                FROM got_numbers
                                *
                                ERROR at line 20:
                                ORA-01219: database not open: queries allowed on fixed tables/views only

                                The rewrite of the query works pefect now in the ASM instance. The onlly thing I need now is a way to add a clause to filiter some records.

                                ie where ROUND ( ( 1 - ( free_mb / total_mb)) * 100 , 2) > 10.. can you add that to the in line query and that should solve all my issues.

                                Once again.. thank you so much for your expetise..
                                • 13. Re: values in both result sets
                                  Frank Kulash
                                  Hi,
                                  978606 wrote:
                                  ... The onlly thing I need now is a way to add a clause to filiter some records.

                                  ie where ROUND ( ( 1 - ( free_mb / total_mb)) * 100 , 2) > 10.. can you add that to the in line query and that should solve all my issues.
                                  Do you want to add that condition to the in-line view, or do you want to add it to the main query? Post some sample data where it matters, and the results you want from that data.

                                  If you want to add that condition to the main query, you can just add a WHERE clause to the main query:
                                  WHERE       ROUND ( ( 1 - ( free_mb 
                                                         / total_mb
                                                 )
                                              ) * 100
                                            , 2
                                            )    > 10
                                  If you want to add that condition to the in-line view, does it apply to free_mb and total_mb of t1 or t3, or both? Whatever you want, there's nothing tricky about adding the condition.