Oracle Query problem...

I have got some problem with my sql query in oracle 9i.
i m trying to stop several occurances of same Do_No.
In the following example (Example1)shows that each Do_No have different MR_No and different MR_Amount.The problem is, we can see that same DO_No occourars several times.But i want it once only when show report on screen(see Example2).

Exmple:1
Query:
select
v_do_sales6.do_no,
v_mreceipt1.mr_no,
v_mreceipt1.amount
from
v_do_sales6,v_mreceipt1 where v_do_sales6.do_date between '01-jun-09' and '30-jun-09' and
v_mreceipt1.DISTRIBUTOR_CODE=v_do_sales6.distribut or_code and
v_do_sales6.distributor_code='S051'

Output:
Do_No MR_No MR_Amount
-----------------------------------------------------------
01 101 5000
01 102 4500
01 103 5600
01 104 6800
01 105 5005
02 106 7250
02 107 3060
02 108 8000

Exmple:2
Output

Do_No MR_No MR_Amount
-----------------------------------------------------------
01 101 5000
102 4500
103 5600
104 6800
105 5005
02 106 7250
107 3060
108 8000

Can anybody Please help me..... i need it early

Thanks in advance

re: Oracle Query problem...

Hi,

Please check and run the below SQL


While writing this SQL I have made DO_NO column as a Primary Key in v_do_sales6 and Foreign Key in v_mreceipt1 tables. I wonder why didn't you do this. Anyways let me know if any issues wt this SQL.

re: Oracle Query problem...

just add a break statement.

--
-- without a break statement
--
select owner
,table_name
from all_tables
where owner in ('SYS','SYSTEM')
order by owner
,table_name

OWNER TABLE_NAME
------------------------ -----------------------------
SYS AUDIT_ACTIONS
SYS AW$AWCREATE
SYS OLAP_OLEDB_MDPROPVALS
SYS PLAN_TABLE$
SYS PSTUBTBL
SYS WRI$_ADV_ASA_RECO_DATA
SYSTEM DEF$_TEMP$LOB
SYSTEM HELP
SYSTEM MVIEW$_ADV_PARTITION
SYSTEM OL$
SYSTEM OL$NODES

--
-- With a break statement
--
SQL> break on owner

SQL> l
1 select owner
2 ,table_name
3 from all_tables
4 where owner in ('SYS','SYSTEM')
5 order by owner
6* ,table_name


OWNER TABLE_NAME
------------------------ -----------------------------
SYS AUDIT_ACTIONS
AW$AWCREATE
OLAP_OLEDB_MDPROPVALS
PLAN_TABLE$
PSTUBTBL
WRI$_ADV_ASA_RECO_DATA
SYSTEM DEF$_TEMP$LOB
HELP
MVIEW$_ADV_PARTITION
OL$
OL$NODES

Hope this helps.

re: Oracle Query problem...

Sorry... trying to get the output to look proper on this site. Hopefully, this will show you what the ouput should look like when you use the break statement.

OWNER TABLE_NAME
------------------------ -----------------------------
SYS AUDIT_ACTIONS
AW$AWCREATE
OLAP_OLEDB_MDPROPVALS
PLAN_TABLE$
PSTUBTBL
WRI$_ADV_ASA_RECO_DATA
SYSTEM DEF$_TEMP$LOB
HELP
MVIEW$_ADV_PARTITION
OL$
OL$NODES

re: Oracle Query problem...

Hi

Using break it's not giving desired output when I did try on Oracle 9i.

re: Oracle Query problem...

I've used "break on" since version 7. Are you using sqlplus or do you need to run this in a different query tool?

re: Oracle Query problem...

Yes I did. It's working.

re: Oracle Query problem...

But I guess it's SQL * Plus command and we can't use it in procedure or function etc.