This discussion is archived
13 Replies Latest reply: Jan 3, 2013 4:45 PM by Frank Kulash RSS

values in both result sets

981609 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    The results are correct.. I had a typo... thank you all for your help
  • 10. Re: values in both result sets
    981609 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points