Oracle Query problem...

[boss1]

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

[OraMaster]

Hi,

Please check and run the below SQL

SELECT DECODE (seq, 1, do_no, NULL) do_no, mr_no, mr_amt
  FROM (SELECT ROW_NUMBER () OVER (PARTITION BY mst.do_no ORDER BY mst.do_no)
                                                                          seq,
               mst.do_no, dtl.mr_no, dtl.mr_amt
          FROM v_do_sales6 mst, v_mreceipt1 dtl
         WHERE mst.do_no = dtl.do_no)

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.

[jsmithstl]

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.

[jsmithstl]

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

[OraMaster]

Hi

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

[jsmithstl]

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

[OraMaster]

@jsmithstl
Yes I did. It's working.

[OraMaster]

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