Home » SQL & PL/SQL » SQL & PL/SQL » produce 1 row
produce 1 row [message #202123] Wed, 08 November 2006 04:54 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
i have a table matgen, i've attached the insert statements.



create table matgen
(  	
	bran_code	varchar2(5),
	cust_code	varchar2(16),
	comm_code	varchar2(5),
	origin		varchar2(5),
	destination	varchar2(5),
	cocl_code	varchar2(5),
	cosi_code	varchar2(5),
	coty_code	varchar2(5),
	factor		number(10,4),
	basis		varchar2(5),
	sear_code	varchar2(5),
	sash_seq_no	number(7),
	chin_code	varchar2(5),
	new_sear_o	varchar2(5),
	new_sear_d	varchar2(5),
	f_std_frt	number(10),
	f_frt_amt	number(10),
	f_frt_percent	number(10),
	f_frt_net_frt	number(10),
	f_gross_bsc	number(10),
	f_gross_amt	number(10),
	f_gross_percent	number(10),
	f_gross_net	number(10),
	f_frt_bsc	number(10),
	f_doc_stamps	number(10),
	f_wharfage_orig	number(10),
	f_wharfage_dest	number(10),
	f_handling_orig	number(10),
	f_handling_dest	number(10),
	f_trucking_orig	number(10),
	f_trucking_dest	number(10),
	f_dd_vat	number(10),
	f_pd_vat	number(10),
	f_pp_vat	number(10),
	f_dp_vat	number(10),
	f_dd_act	number(10),
	f_pd_act	number(10),
	f_pp_act	number(10),
	f_dp_act	number(10),
	puser		varchar2(10) default user
);




as you see in the data, for every the same origin and destination, if there is only FRT and BSC chin_code, it should be merge into 1 row, so that when i create a view only 1 record will be produced per origin and destination with only FRT and BSC, for example i create a view with the columns bran_code, f_std_frt and f_gross_bsc. i hope i've explained it well, thanks again sir/mam.


rhani

  • Attachment: insert.sql
    (Size: 7.99KB, Downloaded 126 times)
Re: produce 1 row [message #202243 is a reply to message #202123] Wed, 08 November 2006 19:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I don't know if I follow your requirements, but from what I can gather, are you looking for something like this ?

SQL> select bran_code, f_std_frt, f_gross_bsc from (
  2  select bran_code, f_std_frt, f_gross_bsc,
  3  row_number() over (partition by origin, destination order by origin) rn
  4  from matgen)
  5  where rn = 1;

BRAN_  F_STD_FRT F_GROSS_BSC
----- ---------- -----------
CEB        18551        1370
CEB        18551        1370
CEB        18189        1370
CEB        35347        2040
CEB        18189        1370
CEB        35347        2040

6 rows selected.
Re: produce 1 row [message #202258 is a reply to message #202243] Wed, 08 November 2006 21:01 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi sir,

thank you so much, that worked fine =) that's one case i need to handle, but another one is that like in the new attached data, there's a chin_code TRKND or sometimes TRKNO, now if there are TRKND or TRKNO, i need not to select those rows with FRT or BSC per origin and destination. i hope i've explained it well sir,thanks again =) ill also try to start with your query, thanks again.
  • Attachment: insert2.sql
    (Size: 15.22KB, Downloaded 111 times)
Re: produce 1 row [message #202280 is a reply to message #202258] Wed, 08 November 2006 22:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
I don't know if I follow your requirements, but from what I can determine, you may be looking for something like this ?

After reading over you initial request, I overlooked the fact that you mentioned chin_code being BSC and FRT, so I modified the script above:

select bran_code, f_std_frt, f_gross_bsc from (
    select bran_code, f_std_frt, f_gross_bsc,
    row_number() over (partition by origin, destination order by origin) rn
    from matgen
    where chin_code in ('BSC', 'FRT'))
where rn = 1;


I believe this is what you are looking for in your second request:

select bran_code, f_std_frt, f_gross_bsc from (
select bran_code, f_std_frt, f_gross_bsc, 
	row_number() over (partition by origin, destination order by origin) rn
from matgen
where (origin, destination) not in (select origin, destination from matgen
					where chin_code in ('TRKND','TRKNO'))
and chin_code in ('BSC','FRT'))
where rn = 1;


By the way, thanks for the create and insert scripts !!

HTH
Re: produce 1 row [message #202304 is a reply to message #202280] Thu, 09 November 2006 01:07 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!,
i think my explanation for the second is incomplete, for a particular origin and destination, if it has FRT and/or BSC alone, it should be merge into one row, likewise for the case if it has (FRT, BSC and one TRKNO) OR (FRT, BSC and one TRKND) these cases should be ONE row per origin destination, now if in case there's FRT or BSC and there are more than one TRKND or TRKNO, then the rows of FRT and BSC should not be selected and only those of TRKND or TRKNO FOR THAT particular origin and destination. i hope i've explained it well.

thanks again sir.
Previous Topic: Trigger for audit purpose
Next Topic: Select Vs Order by Clause
Goto Forum:
  


Current Time: Thu Dec 08 16:44:26 CST 2016

Total time taken to generate the page: 0.11660 seconds