Skip to main content

Hi,

    I have the below two tables with the mentioned fields and sample rows. Table GL_Ledger X has got a lot of actual details down to GL rows while table Budget Y has got budget amounts by Activity_Seq. I need to come up with one report where I have all fields of table X and one additional field for “AMT” coming from budget using the common filed Activity_seq.

 

Two issues in my result:

  • Duplication of budget amounts (in A1 rows).
  • Missing budget for A3 and A5

In other words, want to keep all details and rows of table X and pick up all records from table Y with no duplicates.

Any clues on how to solve this issue? Also if I will use union all, is there an easy way to copy all table X columns properties to table Y and how to do it if that is the only solution.

 

Hi

You can achieve this with a union but you’ll need to define each of the columns that you want to show.  Something along these lines:

 

select activity_seq, amount, unit, sys_code, code from GL_Ledger_X

union

select activity_seq, AMT as amount, ‘’ as unit, ‘’ as sys_code, ‘’ as code from Budget_Y

   where activity_seq not in (select activity_seq from GL_Ledger_X where activity_seq is not null)

 

Note this does mean you won’t get the AMT from Budget_Y for any activities which exist in GL_Ledger_X but it wasn’t clear whether you needed that or not.  If you do, then you’ll have to do a view combining both tables and then query that with a group by to sum the amounts.

Hope this helps.

Thanks MMcK, actually i am looking for the below result where i will have all data from table X and all Data from table Y as per below.  I also expect the below blanks as null. your help is much appreciated

 

regards

Housseiny

 

 

Hi

In that case the query is slightly easier, you should be able to use

select activity_seq, amount, unit, sys_code, code, NULL as AMT from GL_Ledger_X

union

select activity_seq, NULL as amount, NULL as unit, NULL as sys_code, NULL as code AMT from Budget_Y

 

You just need to align the columns for the union.

Hope this helps.

Many thanks MMcK, it works as expected. Much appreciated!!!

 

Regards

Housseiny

Reply


Terms & ConditionsCookie settings