Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    38

    Unanswered: combine two table

    hi
    friend ,
    i have 2 different row that has no relation and i want to make a row from that two row...
    how i get...?

    for exa...
    (a) GRANT IN AID NON -PLAN (GC) - FRI

    (b) GRANT IN AID NON -PLAN (KVS) - FRI


    these r two row and i want to gat these row i a row means

    (a) GRANT IN AID NON -PLAN (GC) - FRI (b) GRANT IN AID NON -PLAN (KVS) - FRI



    help me to solve this issue...

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I have no idea what is that result supposed to mean (concatenating two unrelated records? What for?), but here's one option:
    Code:
    SQL> create table a_dept as select * from dept where deptno = 10;
    
    Table created.
    
    SQL> create table b_dept as select * from dept where deptno = 20;
    
    Table created.
    
    SQL> select a.a_row ||' - '|| b.b_row result
      2  from (select dname a_row from a_dept) a,
      3       (select dname b_row from b_dept) b;
    
    RESULT
    -------------------------------
    ACCOUNTING - RESEARCH
    
    SQL>

  3. #3
    Join Date
    Jul 2011
    Posts
    38

    combine two row in one row...

    thanks for ur reply...
    my que is not this...
    for exa...

    with a as (
    select ac.ACCOUNTCODE , ac.ACCOUNTDESC,sum(bps.allocatedamount) AllocatedAmt
    from budgetperiodsummary bps ,account ac
    where ac.accountcode = bps.account
    and bps.ORGSTRUCTCODE in (02) and ACCOUNTDESC like '%PLAN%'
    group by ac.ACCOUNTCODE,ac.ACCOUNTDESC),
    b as (select vd.accountcode,
    SUM(DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0))-sum(DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0)) as UtilisedExpenditure
    from costcenteralloc cca,voucher v, voucherdetail vd
    where v.DOCID= vd.docid and vd.docdetailid = cca.VOUCHERdetailID and v.APPROVSTATUSTYPENUM = 2
    and v.DOCDATE between to_date('4/1/2011','mm/dd/YYYY') and to_date('4/2/2011','mm/dd/YYYY') and v.ORGSTRUCTCODE in (01)

    group by vd.accountcode),
    c as (select vd.accountcode,
    SUM(DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0))-sum(DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0)) as ProgressExpenditure
    from costcenteralloc cca,voucher v, voucherdetail vd
    where v.DOCID= vd.docid and vd.docdetailid = cca.VOUCHERdetailID and v.APPROVSTATUSTYPENUM = 2
    and v.ORGSTRUCTCODE in (02)
    group by vd.accountcode)

    select a.accountdesc,a.AllocatedAmt,
    DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpend iture) as UtilisedExpenditure,
    DECODE(c.ProgressExpenditure,'',0,c.ProgressExpend iture) as ProgressExpenditure
    from (a left outer join b on a.accountcode=b.accountcode)
    left outer join c on a.accountcode=c.accountcode

    this is a query which return some record and another query

    with a as (
    select ac.ACCOUNTCODE , ac.ACCOUNTDESC,sum(bps.allocatedamount) AllocatedAmt
    from budgetperiodsummary bps ,account ac
    where ac.accountcode = bps.account
    and bps.ORGSTRUCTCODE in (02) and ACCOUNTDESC like '%NON-PLAN%'
    group by ac.ACCOUNTCODE,ac.ACCOUNTDESC),
    b as (select vd.accountcode,
    SUM(DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0))-sum(DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0)) as UtilisedExpenditure
    from costcenteralloc cca,voucher v, voucherdetail vd
    where v.DOCID= vd.docid and vd.docdetailid = cca.VOUCHERdetailID and v.APPROVSTATUSTYPENUM = 2
    and v.DOCDATE between to_date('4/1/2011','mm/dd/YYYY') and to_date('4/2/2011','mm/dd/YYYY') and v.ORGSTRUCTCODE in (01)

    group by vd.accountcode),
    c as (select vd.accountcode,
    SUM(DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0))-sum(DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0)) as ProgressExpenditure
    from costcenteralloc cca,voucher v, voucherdetail vd
    where v.DOCID= vd.docid and vd.docdetailid = cca.VOUCHERdetailID and v.APPROVSTATUSTYPENUM = 2
    and v.ORGSTRUCTCODE in (02)
    group by vd.accountcode)

    select a.accountdesc,a.AllocatedAmt,
    DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpend iture) as UtilisedExpenditure,
    DECODE(c.ProgressExpenditure,'',0,c.ProgressExpend iture) as ProgressExpenditure
    from (a left outer join b on a.accountcode=b.accountcode)
    left outer join c on a.accountcode=c.accountcode


    which also return some record and i want to combine these record into one row...

    such as query 1 return a (4 colum)
    and query 2 return b (4 colum)
    and i want to combine a, b in one row..(8 colum)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about using a FULL OUTER JOIN, something like...
    Code:
    WITH
     a AS (
    ...
    ...
    )
    , b AS (
    ...
    ...
    )
    SELECT a.col1 , a.col2 , ...
         , b.col1 , b.col2 , ...
     FROM  (SELECT a.*
                 , ROW_NUMBER() OVER(...) AS rn
             FROM  a
           ) a
     FULL  OUTER JOIN
           (SELECT b.*
                 , ROW_NUMBER() OVER(...) AS rn
             FROM  b
           ) b
       ON  b.rn = a.rn
     ORDER BY
           NVL(a.rn , b.rn)
    ;

  5. #5
    Join Date
    Jul 2011
    Posts
    38

    Red face


    thanks friend for ur reply...

    r u using full outer join in one with condition?
    and i want to combine two with condition.actually one with condion give me calculated 4 colum according to my condition. and another with condition give me another correct value.
    with condition which i describe u my second last mail.

    i want to combine both with condition so that i get correct result 8 colum .
    actually i want to get data in tabullar form from both with query.and do some other opration in these data

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry, I didn't saw detail of your queries.

    Although I must say that I didn't fully understood your last post,
    how about to add a.accountcode to the final results of both query, then full outer join them?

    Note: I assumed that there is a possibility that different number of rows retuned from both queries.
    Code:
    WITH
     q1_a AS (
    ...
    ) ,
     q1_b AS (
    ...
    ) ,
     q1_c AS (
    ...
    ) ,
     q1_final AS (
    select a.accountcode
         , a.accountdesc
         , a.AllocatedAmt
         , DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpend iture) as UtilisedExpenditure
         , DECODE(c.ProgressExpenditure,'',0,c.ProgressExpend iture) as ProgressExpenditure
    ...
    ) ,
     q2_a AS (
    ...
    ) ,
     q2_b AS (
    ...
    ) ,
     q2_c AS (
    ...
    ) ,
     q2_final AS (
    select a.accountcode
         , a.accountdesc
         , a.AllocatedAmt
         , DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpend iture) as UtilisedExpenditure
         , DECODE(c.ProgressExpenditure,'',0,c.ProgressExpend iture) as ProgressExpenditure
    ...
    )
    SELECT q1.accountdesc , q1.AllocatedAmt , q1.UtilisedExpenditure , q1.ProgressExpenditure
         , q2.accountdesc , q2.AllocatedAmt , q2.UtilisedExpenditure , q2.ProgressExpenditure
     FROM  q1_final q1
     FULL  OUTER JOIN
           q2_final q2
       ON  q2.accountcode = q1.accountcode
       AND q2.accountdesc = q1.accountdesc
     ORDER BY
           NVL(q2.accountcode , q1.accountcode)
         , NVL(q2.accountdesc , q1.accountdesc)
    ;
    Last edited by tonkuma; 01-19-12 at 09:18. Reason: Replace whole of final subquery.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    After looking your queries, I thought that combination of first query and second query might be written like Example 1.

    Sorry my inconsistent considerations.
    I replaced subquery a and final subquery.


    Example 1:
    Code:
    with
     a as (
    select ac.ACCOUNTCODE
         , ac.ACCOUNTDESC
         , sum(bps.allocatedamount) AllocatedAmt
     from  budgetperiodsummary bps 
         , account             ac 
     where ac.accountcode = bps.account 
       and bps.ORGSTRUCTCODE in (02)
       and
       (   ACCOUNTDESC like '%PLAN%'
        or ACCOUNTDESC like '%NON-PLAN%'
       )
     group by
           ac.ACCOUNTCODE
         , ac.ACCOUNTDESC
    ) ,
     b as (
    select vd.accountcode
         ,   SUM( DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0) )
           - sum( DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0) ) as UtilisedExpenditure 
     from  costcenteralloc cca
         , voucher         v
         , voucherdetail   vd 
     where v .DOCID       = vd .docid
       and vd.docdetailid = cca.VOUCHERdetailID
       and v .APPROVSTATUSTYPENUM = 2 
       and v .DOCDATE
                 between to_date('4/1/2011','mm/dd/YYYY')
                     and to_date('4/2/2011','mm/dd/YYYY')
       and v .ORGSTRUCTCODE in (01) 
     group by
           vd.accountcode
    ) ,
     c as (
    select vd.accountcode
         ,   SUM( DECODE(cca.isdebit ,'T',cca.DEBITVALUE, 0) )
           - sum( DECODE(cca.isdebit ,'F',cca.DEBITVALUE, 0) ) as ProgressExpenditure 
     from  costcenteralloc cca
         , voucher         v
         , voucherdetail   vd 
     where v .DOCID       = vd .docid
       and vd.docdetailid = cca.VOUCHERdetailID
       and v .APPROVSTATUSTYPENUM = 2 
       and v .ORGSTRUCTCODE in (02) 
     group by
           vd.accountcode
    )
    select a1.accountdesc
         , a1.AllocatedAmt
         , CASE
           WHEN a1.accountdesc IS NOT NULL THEN
                DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpenditure)
           END  as UtilisedExpenditure
         , CASE
           WHEN a1.accountdesc IS NOT NULL THEN
                DECODE(c.ProgressExpenditure,'',0,c.ProgressExpenditure)
           END  as ProgressExpenditure
         , a2.accountdesc
         , a2.AllocatedAmt
         , CASE
           WHEN a2.accountdesc IS NOT NULL THEN
                DECODE(b.UtilisedExpenditure,'',0,b.UtilisedExpenditure)
           END  as UtilisedExpenditure
         , CASE
           WHEN a2.accountdesc IS NOT NULL THEN
                DECODE(c.ProgressExpenditure,'',0,c.ProgressExpenditure)
           END  as ProgressExpenditure
     from  a a1
     FULL  OUTER JOIN
           a a2
       ON  a2.accountcode = a1.accountcode
       AND a1.ACCOUNTDESC like '%PLAN%'
       AND a2.ACCOUNTDESC like '%NON-PLAN%'
     left  outer join
           b
       on  b.accountcode = NVL(a2.accountcode , a1.accountcode)
     left  outer join
           c
       on  c.accountcode = NVL(a2.accountcode , a1.accountcode)
     WHERE (a1.ACCOUNTcode IS NULL OR a1.ACCOUNTDESC like '%PLAN%'    )
       AND (a2.ACCOUNTcode IS NULL OR a2.ACCOUNTDESC like '%NON-PLAN%')
    ;
    Last edited by tonkuma; 01-19-12 at 10:01. Reason: Replace sample code.

  8. #8
    Join Date
    Jul 2011
    Posts
    38
    thanks brother for ur copretion to slove this problem

    regards
    vishwas

Posting Permissions

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