CODEWITHSUNDEEP
oracle-databaseplsqloraclereports
Asif Raza
Asif Raza

Reputation: 3

Oracle Reports (6i): Get difference of two items from two different queries (Multi Query Report)

I have an Oracle 6i report with two complex queries (Q1 and Q2) connected by data link. The report was developed by some other developer who is not available now. Multiple columns are being displayed in report.

Q1 has a column Total_Issuance and Q2 has a column Total_Consumption. I have to display their difference on report. I am using a formula column to get the difference and then binding a display field to this formula column to show on report.

But since the formula column is outside both the queries, I am getting following errors.

REP-1517: column 'CF_1' references column 'Total_Issuance', which has incompatible frequency.

REP-1517: column 'CF_1' references column 'Total_Consumption', which has incompatible frequency.

Upvotes: 0

Views: 736

Answers (2)

Radagast81
Radagast81

Reputation: 3006

Create a new calculated field to the query that is the detail query (I asume it's Q2). In this calculated field you can simply use:

return :Total_Issuance - :Total_Consumption;

to calculate the difference.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142710

Just like the formula column is outside both queries, create two additional summary columns (outside of both queries):

  • cs_tot_iss, which will sum total_issuance
  • cs_tot_con, which will sum total_consumption

Now modify formula column so that its source are newly created summary columns, e.g. let it

return :cs_tot_iss + :cs_tot_con;

As of paper layout: you don't have to display cs_tot_iss nor cs_tot_con (as you already have those values via total_issuance and total_consumption), just add formula column.

Upvotes: 0

Related Questions