This discussion is archived
7 Replies Latest reply: Dec 16, 2012 12:05 PM by Srini VEERAVALLI RSS

Issue with Combine similar request

user8744765 Newbie
Currently Being Moderated
Hi Guru's,

I am trying to create a union request , with 2 criteria's
my first criteria is returning Qty Shipped, and second one returning Qty Ordered, both the queries have common dim Order Type and one dummy column cast null as double .
but my results are supposed to be in one row Like Order type : Shipped : Ordered

but the results are in 2 rows like below

order type null Qty Shipped
Order type QTY Ordered Null

how to get this results in one row, and can you any one of you provide me any guide on how to work with union queries , combine with similar request.

Thanks in advance

Edited by: user8744765 on Dec 10, 2012 8:38 PM
  • 1. Re: Issue with Combine similar request
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Two things:
    1) As per given info I dont think columns are in same order

    order type null Qty Shipped
    Order type QTY Ordered Null

    These suppose to be
    order type null Qty Shipped
    Order type Null QTY Ordered

    2) Not sure what View you are using! If you use Pivot table view there might be a chance to get in 1 row by summing numeric values

    If helps pls mark else update
  • 2. Re: Issue with Combine similar request
    Maqsood Hussain Journeyer
    Currently Being Moderated
    Hi,

    Are you using OBIEE 11g. If yes then you don't need to create a Union query for Order Type , Quantity Shipped and Quantity Ordered. You can click on Add Remove Subject Areas button to add the measure from another subject area.
    Any ways if you still want to do union you can try the below approach.

    It seems the Measures are not getting grouped by Order Type. (Not sure why). So you can apply group by directly in advanced tab.

    Go to advanced tab > New Analysis > . Type the following logical sql

    SELECT saw_0 saw_0, SUM(saw_1) saw_1, SUM(saw_2) saw_2 FROM
    ((SELECT "Order"."Order Type" saw_0, "Order Facts"."Quantity Shipped" saw_1, Null saw_2 FROM "SH")
    UNION
    (SELECT "Order"."Order Type" saw_0, Null saw_1, "Order Facts"."Quantity Ordered" saw_2 FROM "SH")) t1
    GROUP BY Saw_0 ORDER BY saw_0

    You will have to replace "Order"."Order Type", Order Facts"."Quantity Shipped" and "Order Facts"."Quantity Ordered" with your column names in the above logical SQL.

    Now click on results.

    You will see results with column heading saw_0, saw_1 ,saw_2 . You can change the column heading in column properties.

    Thanks
  • 3. Re: Issue with Combine similar request
    user8744765 Newbie
    Currently Being Moderated
    Hi Sreeni,

    Thanks for you reply, but if i create the columns that you have mentioned they i will get only Qty shipped and QTY ordered in the same column rt?

    but we need to show some thing like, Order Type | Qty shipped |  Qty Ordered
    can you tell me how can i achieve this.

    Thanks.

    Edited by: user8744765 on Dec 13, 2012 9:34 AM
  • 4. Re: Issue with Combine similar request
    Srini VEERAVALLI Guru
    Currently Being Moderated
    In your initial query instead of null use 0
    in Pivot table values are summed up and you get your required output.

    This should work, if helps pls mark

    Edited by: Srini VEERAVALLI on Dec 13, 2012 2:47 PM
  • 5. Re: Issue with Combine similar request
    user8744765 Newbie
    Currently Being Moderated
    Hi Magsood Hussain,

    Thanks for your response,

    Yes we are using OBIEE 11g, but when i click on add or remove subject area it is taking me to union report screen only.

    we have a very big criteria with 30 columns in the report, so i dont think i can chage the SQL in Advanced Tab as i am not sure :(
  • 6. Re: Issue with Combine similar request
    user8744765 Newbie
    Currently Being Moderated
    Hi Sreeni,


    Yes that worked, is there a way we can achieve this in Table format in OBIEE 11g,
    because we have lot of measures in the report and Pivot is not looking good with the new 11g look.


    Thanks..
  • 7. Re: Issue with Combine similar request
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Assuming this is your complete query

    SELECT saw_0 saw_0, SUM(saw_1) saw_1, SUM(saw_2) saw_2 FROM
    ((SELECT "Order"."Order Type" saw_0, "Order Facts"."Quantity Shipped" saw_1, Null saw_2 FROM "SH")
    UNION
    (SELECT "Order"."Order Type" saw_0, Null saw_1, "Order Facts"."Quantity Ordered" saw_2 FROM "SH")) t1
    GROUP BY Saw_0 ORDER BY saw_0

    at outer query use expression like

    SUM(saw_1 by saw_0)
    to get that you need to open the column fx and then add
    this may work

Legend

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