none
How to union all this codes? RRS feed

  • Question

  • Hi all

    i used this codes....

    WITH LData
    AS ( SELECT VchNo ,
                VchDate ,
                DebitBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ) ,
         RData
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Debit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 )
    SELECT 1 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.DebitBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Debit
    FROM   LData L
           INNER JOIN RData R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL;

    and gets this result ...

    and used this code...

    WITH LData
    AS ( SELECT VchNo ,
                VchDate ,
                CreditBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ) ,
         RData
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Credit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 )
    SELECT 2 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.CreditBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Credit
    FROM   LData L
           INNER JOIN RData R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL;

    and gets this result

    now

    How to union all 2 results?


    Name of Allah, Most Gracious, Most Merciful and He created the human


    • Edited by sh 2020 Tuesday, June 30, 2020 4:57 PM
    Tuesday, June 30, 2020 4:56 PM

All replies

  • If by Union you mean combination of 2 results, then it will be simple:

    WITH LDataDebit
    AS ( SELECT VchNo ,
                VchDate ,
                DebitBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ) ,
         RDataDebit
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Debit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ),
    DebitInfo as (SELECT 1 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.DebitBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Debit
    FROM   LData L
           INNER JOIN RData R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL),
    LDataCredit AS ( SELECT VchNo ,
                VchDate ,
                CreditBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ) ,
         RDataCredit
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Credit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ),
    CreditInfo as (SELECT 2 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.CreditBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Credit
    FROM   LData L
           INNER JOIN RData R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL)
    
    select DebitInfo.*
    from DebitInfo
    UNION ALL
    select CreditInfo.*
    from CreditInfo;
    

    This is using your original code. Alternatively you can just use both types directly and join based on RN and Type (Also partition by Type in ROW_NUMBER function)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 5:09 PM
    Moderator
  • thanks dear 

    I used codes and get this error

    How to solve it?


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Tuesday, June 30, 2020 5:18 PM
  • Make sure to start your code with the semicolon right before the WITH, e.g.

    ;with LData ...


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 30, 2020 5:30 PM
    Moderator
  • thank but not work ...


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Tuesday, June 30, 2020 5:36 PM
  • Right, you need to make sure to use correct names of Common Table expressions in your select statements, so:

    ;WITH LDataDebit
    AS ( SELECT VchNo ,
                VchDate ,
                DebitBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ) ,
         RDataDebit
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Debit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ),
    DebitInfo as (SELECT 1 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.DebitBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Debit
    FROM   LDataDebit L
           INNER JOIN RDataDebit R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL),
    LDataCredit AS ( SELECT VchNo ,
                VchDate ,
                CreditBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ) ,
         RDataCredit
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Credit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ),
    CreditInfo as (SELECT 2 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.CreditBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Credit
    FROM   LDataCredit L
           INNER JOIN RDataCredit R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL)
    
    select DebitInfo.*
    from DebitInfo
    UNION ALL
    select CreditInfo.*
    from CreditInfo;


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by sh 2020 Tuesday, June 30, 2020 5:44 PM
    • Unmarked as answer by sh 2020 Tuesday, June 30, 2020 5:46 PM
    Tuesday, June 30, 2020 5:41 PM
    Moderator
  • please see pic 1 and 2

    and code not work properly


    Name of Allah, Most Gracious, Most Merciful and He created the human

    Tuesday, June 30, 2020 5:50 PM
  • Instead of showing the pictures, post your DDL, some insert statements and then desired result based on the inserted data.

    In other words, what is your goal - what do you want as a result? In your original question you talked about UNION, which is exactly what I used.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles



    Tuesday, June 30, 2020 6:09 PM
    Moderator
  • Hi sh 2020,
    Please try the following statement first.
    WITH LData
    AS ( SELECT VchNo ,
                VchDate ,
                DebitBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ) ,
         ,RData
          AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Debit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
             FROM   rpa.ReconcileRep
             WHERE  [type] = 1 )
    	 ,LData
          AS ( SELECT VchNo ,
                      VchDate ,
                      CreditBank ,
                      ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
               FROM   rpa.ReconcileRep
               WHERE  [type] = 2 ) 
         ,RData
          AS ( SELECT TempNum ,
                      VoucherHeaderDate ,
                      Credit ,
                      ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
               FROM   rpa.ReconcileRep
               WHERE  [type] = 2 )
          ,Result1 
    	  As (SELECT 1 AS [Type] ,
                          L.VchNo ,
                          L.VchDate ,
                          L.DebitBank ,
                          R.TempNum ,
                          R.VoucherHeaderDate ,
                          R.Debit
             FROM   LData L
             INNER JOIN RData R ON L.RN = R.RN
    	     WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL
             UNION ALL
             SELECT 2 AS [Type] ,
                         L.VchNo ,
                         L.VchDate ,
                         L.CreditBank ,
                         R.TempNum ,
                         R.VoucherHeaderDate ,
                         R.Credit
              FROM   LData L
              INNER JOIN RData R ON L.RN = R.RN
    	      WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL)
    		  ,Result2 
    		  As (select * ,rom_number()over(partition by R.TempNum order by R.TempNum) r
                  from Result1)
    select * from Result2
    where r=1


    Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …) 
    along with your expected result? So that we’ll get a right direction and make some test.


     Best Regards,
     Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, July 1, 2020 8:44 AM
  • hi

    Try the bellow code may solved

    WITH LData
    AS ( SELECT VchNo ,
                VchDate ,
                DebitBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ) ,
         RData
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Debit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 1 ),
    Joindata1
    AS(
    SELECT 1 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.DebitBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Debit
    FROM   LData L
           INNER JOIN RData R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL
    ),
    
    LData1
    AS ( SELECT VchNo ,
                VchDate ,
                CreditBank ,
                ROW_NUMBER() OVER ( ORDER BY VchDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ) ,
         RData1
    AS ( SELECT TempNum ,
                VoucherHeaderDate ,
                Credit ,
                ROW_NUMBER() OVER ( ORDER BY VoucherHeaderDate ) AS RN
         FROM   rpa.ReconcileRep
         WHERE  [type] = 2 ),
    joindata2 
    AS(
    SELECT 2 AS [Type] ,
           L.VchNo ,
           L.VchDate ,
           L.CreditBank ,
           R.TempNum ,
           R.VoucherHeaderDate ,
           R.Credit
    FROM   LData1 L
           INNER JOIN RData1 R ON L.RN = R.RN
    	   WHERE L.VchNo IS NOT NULL OR R.TempNum IS NOT NULL
    )
    
    
    Select * fron joindata1
    UNION ALL 
    select * from joindata2

    Wednesday, July 1, 2020 1:14 PM
  • Hi sh 2020,
    Has your problem been solved? If it is solved, please mark the point that you 
    think is correct as an answer. This can help others who encounter similar problems.

    Best Regards
    Echo

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    3 hours 18 minutes ago