Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42

    Unanswered: Combining 2 records into 1 in OBIEE 11.1

    What I am trying to do is a crosstab type query that allows me to take two separate lines/records and relate them by Journal Id(transaction #). If amount is negative then it is a "out" Amount. If Amount is positive then it is an "In" Amount. And the Dept on that line will either be "In" or "Out" based on negative or positive amount respectively. The issue I am having is that I cannot compress my results into one line. If I use the Case or Filter command I need to have default 0, right? Not sure if their is another command or way of doing but maybe if no function then subquery?

    Attached and Listed Below is Results desired and current Sql.

    Click image for larger version. 

Name:	OBIEE Combine 2 into 1.PNG 
Views:	5 
Size:	13.5 KB 
ID:	16323

    SELECT
    0 s_0,
    "A"."Cal for Journal Date"."Journal Dt" s_1,
    "A"."Dept - Financials"."Depart Id" s_2,
    "A"."Fact Control Journal"."Journl Trans Amt"

    s_3,
    "A"."Jrnal Det."Journal Id" s_4,
    "A"."Jrnal Det."Jrnal Line Ref Num" s_5
    FROM "A"
    WHERE
    (("Fiscal Period Year"."Fiscal Year" = '2015') AND ("Jrnal

    Det."Journal Id" = '0002155359'))
    ORDER BY 1, 5 ASC NULLS LAST, 2 ASC NULLS LAST, 6 ASC NULLS LAST, 4 ASC NULLS LAST, 3 ASC NULLS LAST
    FETCH FIRST 500001

    ROWS ONLY

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Try this:
    Code:
    WITH Tab_A ( Journal_Id, Journal_Dt, Jrnl_Line_Ref_Num, Jrnl_Trans_Amt, Depart_Id )
         AS (SELECT '0002155359', '3/26/2015', 1, -90, 'A1010000'FROM DUAL UNION ALL
             SELECT '0002155359', '3/26/2015', 1, 90, 'A1010000' FROM DUAL)
      -- ----------------
      SELECT Journal_Id
           , Journal_Dt
           , Jrnl_Line_Ref_Num
           , SUM ( CASE WHEN Jrnl_Trans_Amt >= 0 THEN Jrnl_Trans_Amt ELSE 0 END ) AS "in Jrnl Trns Amt"
           , MAX ( CASE WHEN Jrnl_Trans_Amt >= 0 THEN Depart_Id ELSE '' END ) AS "in Dept Id"
           , SUM ( CASE WHEN Jrnl_Trans_Amt < 0 THEN Jrnl_Trans_Amt ELSE 0 END ) AS "out Jrnl Trns Amt"
           , MAX ( CASE WHEN Jrnl_Trans_Amt < 0 THEN Depart_Id ELSE '' END ) AS "out Dept Id"
        FROM Tab_A
    GROUP BY Journal_Id, Journal_Dt, Jrnl_Line_Ref_Num
    ORDER BY Journal_Id, Journal_Dt, Jrnl_Line_Ref_Num
    /
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    I feel hopeful with your answer. So A union all query with one side the out amount by dept id and the other side the in amount by dept id and maxing the deptid on both? Now the select From Group BY Order By goes in the Advanced tab under Advanced SQL Clauses or is it under one of the queries in the union all?.....i notice you have dashes separating so i am assuming you want this placed elsewhere. I am just not sure where to place it.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by John2Chr View Post
    .....i notice you have dashes separating so i am assuming you want this placed elsewhere. I am just not sure where to place it.
    The 3 lines before the dashes provide the data
    The query is below the dashes.
    I suppose you are querying a table, then just remove the top 3 lines and replace "FROM Tab_A" with the correct table name.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Apr 2005
    Location
    Minnesota
    Posts
    42
    Hey, that was very helpful. I was able to do it without a union. I used your same methodology and realized by looking at your solution that I had to put the BY in the Formula. Link below helped too....thanks dude!



    http://obiee101.blogspot.co.uk/2008/...regate-by.html

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Please mark this answered
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •