Help, difference between two (2) data sets

Hi all, I need to calculate with a data item the difference between two columns of two different data sets.Here is the example:1st Query:SELECT ID_MONEDA, SUM(I_COBRO)
FROM FAC_COBROS
WHERE O_EMPRESA = NVL(:p_o_empresa, O_EMPRESA)
AND F_LIQUIDACION = NVL(:p_f_liquidacion, F_LIQUIDACION)
AND N_LIQUIDACION = NVL(:p_n_liquidacion, N_LIQUIDACION)
GROUP BY ID_MONEDA 2nd Query:SELECT ID_MONEDA, SUM(I_PAGO)
FROM FAP_PAGOS
WHERE O_EMPRESA = NVL(:p_o_empresa, O_EMPRESA)
AND F_LIQUIDACION = NVL(:p_f_liquidacion, F_LIQUIDACION)
AND N_LIQUIDACION = NVL(:p_n_liquidacion, N_LIQUIDACION)
GROUP BY ID_MONEDA WhaT I need is to put in a table the result form (SUM(I_PAGO) - SUM(I_COBRO))Each query can return 1, 2 or 3 columns.Thank you very much!!!

Find more posts tagged with

Comments

micajblock

Have you tried joined data set?

kidtorres

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="121675" data-time="1383229829"><div>Have you tried joined data set?</div></blockquote>I create a Joint Data set with those two querys wiht inner join, and then in the joint query I create a computed column thay was row["SUM(I_PAGO"] - row["SUM(I_COBRO)"] but nothing happened, did I do something wrong??

bgbaird

There are lots of methods to do this, depending on where you want the work done. If you do the join in data without computing does it work? If so, you could just use the dataset to get all the results, and do the math in the report. Brian

Hans_vd

Are [color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]row["SUM(I_PAGO"] and row["SUM(I_COBRO)"] really the names of the data set fields?[/color][color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]And if so, isn't there a ) missing in the first one?[/color]

kidtorres

<blockquote class="ipsBlockquote" data-author="bgbaird" data-cid="121680" data-time="1383234861"><div>There are lots of methods to do this, depending on where you want the work done. If you do the join in data without computing does it work? If so, you could just use the dataset to get all the results, and do the math in the report. Brian</div></blockquote>Yes if do it wuthout computing works but it onle shows 1 column.. The only way that it works is if I make a data set with the two querys with an Union All, but I dont know how to create a data item that can calculate the difference between the two rows..

kidtorres

<blockquote class="ipsBlockquote" data-author="Hans_vd" data-cid="121683" data-time="1383236782"><div>Are [color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]row["SUM(I_PAGO"] and row["SUM(I_COBRO)"] really the names of the data set fields?[/color][color=rgb(0,0,0);font-family:helvetica, arial, sans-serif;]And if so, isn't there a ) missing in the first one?[/color]</div></blockquote>Yes, those are the real names, and the missing ) was a mistake that I made typing here jejeje sorry and thanks for the help..

micajblock

What do you mean 'nothing happened' when you do a Joined data set? What happens when you preview the data in the data set editor?

Hans_vd

And what happens if you give the sum fields an alias?

kidtorres

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="121687" data-time="1383239556"><div>What do you mean 'nothing happened' when you do a Joined data set? What happens when you preview the data in the data set editor?</div></blockquote>I meant that it didnt show the table, as if it doesnÂ´t exist the joined data set that I created. The result would be:1st query could return:ID_MONEDA SUM(I_COBRO):$ 17.000U$S 20.000Â£ 5.000 2nd query could return:ID_MONEDA SUM(I_PAGO):$ 13.000U$S 18.000Â£ 1.000 And what I need to display is the diference between the columns, I_COBRO and I_PAGO (I_COBRO - I_PAGO):ID_MONEDA Difference:$ 4.000U$S 2.000Â£ 4.000 Thank you ALL!!!!!

micajblock

The fact that it did not show in the table might be a binding issue. What do you see when you click on 'Preview Results' when you edit the joined data set?

kidtorres

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="121696" data-time="1383245239"><div>The fact that it did not show in the table might be a binding issue. What do you see when you click on 'Preview Results' when you edit the joined data set?</div></blockquote>I canÂ´t see anything because both querys depends on parameters, so in the preview results I canÂ´t set them.ItÂ´s something very easy to do in Oracle reports but here is very dificult, I dont know how to do it jaja

micajblock

<blockquote class="ipsBlockquote" data-author="kidtorres" data-cid="121702" data-time="1383249398"><div>I canÂ´t see anything because both querys depends on parameters, so in the preview results I canÂ´t set them. </div></blockquote> You can set default values for the parameters and then you can preview the results. <blockquote class="ipsBlockquote" data-author="kidtorres" data-cid="121702" data-time="1383249398"><div>ItÂ´s something very easy to do in Oracle reports but here is very difficult.</div></blockquote> Not difficult, just different that what you are used to in Oracle reports. In the attached design I show 2 ways to so this. One using a joined data set (if done correctly you WILL get results). The other uses sub queries to perform the join on the database. Both work. <blockquote class="ipsBlockquote" data-author="kidtorres" data-cid="121702" data-time="1383249398"><div>I dont know how to do it java</div></blockquote> You do not need to know Java (as demonstrated in the examples). You can use JavaScript to store variables from the first query and use them in the second, but my examples are much easier (I did not write any code in my examples).

kidtorres

<blockquote class="ipsBlockquote" data-author="mblock" data-cid="121706" data-time="1383253379"><div>You can set default values for the parameters and then you can preview the results. Not difficult, just different that what you are used to in Oracle reports. In the attached design I show 2 ways to so this. One using a joined data set (if done correctly you WILL get results). The other uses sub queries to perform the join on the database. Both work. You do not need to know Java (as demonstrated in the examples). You can use JavaScript to store variables from the first query and use them in the second, but my examples are much easier (I did not write any code in my examples).</div></blockquote> Thank you very very much, I could use the example of the jonied data. I end up using a full outer join, because sometimes, one query could return 1 row, and the other query 2 rows, so your example helped me a lot.THANK YOU again!!!Best regards.