Home » SQL & PL/SQL » SQL & PL/SQL » QUERY PROBLEMS: PLEASE HELP
QUERY PROBLEMS: PLEASE HELP [message #249318] Wed, 04 July 2007 01:38 Go to next message
sjpesguerra
Messages: 3
Registered: July 2007
Location: Paranaque
Junior Member

Hi,

I have the following data....


contract_ref_no  tradeno ccy   principal        accrual
001LFTM060870003 033291  PHP   5,000,000.00    1,095.89
001LFTM060870003 033291  PHP   5,000,000.00   31,780.82
001LFTM060870004 033292  PHP   5,000,000.00    1,095.89
001LFTM060870004 033292  PHP   5,000,000.00   31,780.82



then i placed a break on the ff: contract_ref_no, tradeno, ccy and prncipal because the data is just redundant. This is how it looked like after the changes i've made....


contract_ref_no  tradeno ccy   principal        accrual
001LFTM060870003 033291  PHP   5,000,000.00    1,095.89
                                              31,780.82
001LFTM060870004 033292  PHP   5,000,000.00    1,095.89
                                              31,780.82



however, whenever i would perform the compute sum label for the report, the principal that was removed is still computed with other values. Based from my example, the principal is supposed to be 10,000,000.00 only because two of the accruals belong to only one tradeno. But it would still compute for 20,000,000.00.
How can i restrict the sql from not computing for the principal of the other transaction when it belongs to the same contractref no and tradeno...?
Please help me...
Thanks...

[Updated on: Wed, 04 July 2007 03:36]

Report message to a moderator

Re: QUERY PROBLEMS: PLEASE HELP [message #249330 is a reply to message #249318] Wed, 04 July 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Post what you actually did (copy and paste of your script/screen) and not just a description of what you think you did.

This generality said.
I think you used SQL*Plus.
I think you used BREAK to prevent from repeating the value.
I think you used COMPUTE to display the sum.
But BREAK is just a display statement, it does not erase the value so COMPUTE sees it and computes it.
If you don't want this, you have to improve your SQL to include the sum.

Regards
Michel
Re: QUERY PROBLEMS: PLEASE HELP [message #249346 is a reply to message #249330] Wed, 04 July 2007 02:44 Go to previous messageGo to next message
sjpesguerra
Messages: 3
Registered: July 2007
Location: Paranaque
Junior Member

We use Oracle9i. Thanks... You're right. I only used break to hide the value.
Below is the script i'm using:

--- Accrual report   
set lines 200
set pages 66
set trimout on
set trimspool on
set pau off 
set verify off
set feedback off
set colsep ' '
clear columns
clear breaks
clear computes
set serveroutput on

-- Prompt for Parameters
Accept accr_date prompt 'Enter accrual date    : '

-- Define bind variables to be used throughout the script
variable run_date varchar2(8);
variable disp_date varchar2(30);

-- Anon. PL/SQL Block to set bind variables
declare 
	dd date;
begin
	select today into dd 
	from sttms_dates
	where branch_code = '&2';
	:disp_date := to_char(dd,'MON DD, RRRR');
end;
/

Ttitle uu_line skip -
	   center -
	   'BRANCH ' '&2' ' -- ACCRUALS REPORT AS ON ' - 
       ' ' val_dt -
	   right  'Page : ' sql.pno -
	   skip -
	   left 'Run Date : ' RUN_DATE -
	   center nv_pdesc ' ( ' nv_prod ')' -
	   skip -
	   uu_line skip 2
btitle ' '

column 	category 		heading 	'CATEGORY'
column 	cust 			heading 	'CUSTOMER NO.'
column 	cust_name 		heading 	'NAME'
column 	contract_ref_no 	heading 	'CONTRACT|REFERENCE NO.' just cent
column 	user_ref_no 		heading 	'TRADING|ORDER NO.'
column	ccy			heading		'CCY'
column 	principal 		heading 	'PRINCIPAL|OUTSTANDING' format 999,999,999,999.99
column 	curr_run_int 		heading 	'INTEREST ACCRUED|CURRENT RUN' format 999,999,999,999.99
column 	curr_run_tax 		heading 	'TAX ACCRUED|CURRENT RUN' format 999,999,999,999.99
column 	int_outstanding 	heading 	'INTEREST ACCRUED|TO DATE' format 999,999,999,999.99
column 	tax_outstanding 	heading 	'TAX ACCRUED|TO DATE' format 999,999,999,999.99

column 	product_code 		noprint 	new_value 	nv_prod
column 	product_description 	noprint 	new_value 	nv_pdesc
column 	value_date 		noprint 	new_value 	val_dt
column 	disp_date 		noprint 	new_value 	RUN_DATE
column 	uu_l 			noprint 	new_value 	uu_line

break on  product_code skip page -
	  on category  skip 2 -
	  on cust -
	  on cust_name -
	  on contract_ref_no -
	  on user_ref_no -
	  on ccy -
	  on principal

compute sum Label 'Category :' -
			of principal curr_run_int curr_run_tax -
			   int_outstanding tax_outstanding -
			on category

compute sum Label 'Product Total :' -
			of principal curr_run_int curr_run_tax -
			   int_outstanding tax_outstanding -
			on product_code

spool d:\accr_rpt
exec dbms_output.put_line(chr(15));
select 
	decode(u.customer_type,'I','INDIVIDUALS',
			       'C','CORPORATES',
			       'B','CORPORATES') category,
	u.customer_no cust,
	u.short_name cust_name,
	c.contract_ref_no contract_ref_no,
	c.user_ref_no , 
	i.component_ccy ccy,
	b.principal_outstanding_bal principal,
	nvl(i.net_accrual,0) curr_run_int,
	nvl(t.net_accrual,0) curr_run_tax,
	nvl(i.outstanding_accrual,0) int_outstanding,
	nvl(t.outstanding_accrual,0) tax_outstanding,
	c.product_code,p.product_description,:disp_date disp_date,
	lpad('=',200,'=') uu_l,i.value_date
FROM	cstbs_contract c,
		sttms_customer u,
		cstms_product p,
		ldtbs_contract_balance b,
		cbc_accr_int i,
		cbc_accr_tax t
WHERE	c.contract_ref_no like upper('&2') || '%'
AND	    p.module = 'MM'
AND	    i.value_date =  to_date('&accr_date','DD-MON-RRRR')
AND     c.contract_ref_no = i.contract_ref_no
AND     i.contract_ref_no = t.contract_ref_no (+)
AND     i.value_date      = t.value_date (+)
AND     c.counterparty = u.customer_no 
AND     p.product_code = c.product_code 
AND     b.contract_ref_no = c.contract_ref_no
ORDER BY
	p.product_code,
	decode(u.customer_type,'I','INDIVIDUALS',
			           'C','CORPORATES',
			           'B','CORPORATES'),
	c.user_ref_no
/

[Updated on: Wed, 04 July 2007 02:47]

Report message to a moderator

Re: QUERY PROBLEMS: PLEASE HELP [message #250157 is a reply to message #249346] Sun, 08 July 2007 17:52 Go to previous messageGo to next message
SnippetyJoe
Messages: 63
Registered: March 2007
Location: Toronto, Canada
Member
This might help ...

>SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jul 8 18:49:16 2007

create table t ( c1 varchar2(10), c2 number, c3 varchar2(10) );

Table created.


insert into t values ( 'a', 50, 'w' );

1 row created.

insert into t values ( 'a', 50, 'x' );

1 row created.

insert into t values ( 'b', 50, 'y' );

1 row created.

insert into t values ( 'b', 50, 'z' );

1 row created.


commit;

Commit complete.


-- you essentially did something like this

break on c1 skip 1 on c2

compute sum of c2 on c1

select c1, c2, c3
from t
order by c1 ;

C1                 C2 C3
---------- ---------- ----------
a                  50 w
                      x
********** ----------
sum               100

b                  50 y
                      z
********** ----------
sum               100


4 rows selected.


-- try doing it this way instead

break on c1 skip 1

select
  c1,
  decode( row_num, 1, c2, null ) as c2 ,
  c3
from
  ( select
      row_number() over ( partition by c1 order by c1 ) row_num ,
      c1, c2, c3
    from t
  )
order by c1 ;

C1                 C2 C3
---------- ---------- ----------
a                  50 w
                      x
********** ----------
sum                50

b                  50 y
                      z
********** ----------
sum                50


4 rows selected.



--
Joe Fuda
SQL Snippets
Re: QUERY PROBLEMS: PLEASE HELP [message #250697 is a reply to message #250157] Tue, 10 July 2007 22:03 Go to previous message
sjpesguerra
Messages: 3
Registered: July 2007
Location: Paranaque
Junior Member

Thank you so much!!! You're such a lifesaver!!! Smile
Previous Topic: partition exchange
Next Topic: max dates
Goto Forum:
  


Current Time: Mon Dec 05 13:02:46 CST 2016

Total time taken to generate the page: 0.13673 seconds