Home » SQL & PL/SQL » SQL & PL/SQL » logic help
logic help [message #198371] Mon, 16 October 2006 21:05 Go to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
im having a problem of how can i produce this. i've attached the sample data as the result of my query, now i created a table into which ill put some of those columns and also the other columns in the table will be computed. please check the attached for the data, there are types of chin_code, mostly there would only be 1 FRT, and 1 BSC ( i didnt have it in the sample), then there's TRKNO and sometimes TRKND, in my table basically some of the columns are

ORIGIN
DESTINATION
FRTAMT
BSCAMT......
TRUCKORIGINAMT
TRUCKDESTINATIONAMT


for example on the data i have 5 TRKNO and 1 FRT, when i put this on the table it should contain 6 row still but the value for the FRTAMT should be also put on the rows of TRKNO, i dont know how to display it, but heres my sample..

so we have 5 trkno so we have value on it

origin dest frtamt bscamt trucko truckdest

MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 0 0 600 0
MNL DGT 500 0 0 0

it should be transformed into

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 600 0
MNL DGT 500 0 0 0

but if TRKNO is only 1 and 1 FRT so that would be two row in resultset, or if we also have TRKND as you can see we have value for truckdest, it should only be merged into one row

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 0 0
MNL DGT 0 0 600 700

it should be displayed inputed to the table like this

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 0 600 700

but if there is more than 1 TRKNO or TRKND, it should be compared to every TRKND or vice versa, for example

origin dest frtamt bscamt trucko truckdest

MNL DGT 0 0 600 0
MNL DGT 0 0 400 0
MNL DGT 0 0 0 300
MNL DGT 0 0 0 200
MNL DGT 0 100 0 0
MNL DGT 500 0 0 0

so we have 2 TRKNO, 2 TRKND and 1 FRT and 1 BSC, so it should be like this

origin dest frtamt bscamt trucko truckdest

MNL DGT 500 100 600 300
MNL DGT 500 100 600 200
MNL DGT 500 100 400 300
MNL DGT 500 100 400 200


thank you so much, i hope i've explained it well, on populating the values of the columns im thinking of using databse triggers on it, thanks again.

  • Attachment: data.csv
    (Size: 0.74KB, Downloaded 185 times)
Re: logic help [message #198411 is a reply to message #198371] Tue, 17 October 2006 02:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm sorry, I can't understand what you're trying to say.
All of the processing you're describing seems to be based on fields that you don't bother showing.

Try coming up with some examples that show ALL the data involved in the processing, and then format the data using CODE tags so that the columns line up vertically.
Re: logic help [message #198433 is a reply to message #198411] Tue, 17 October 2006 03:10 Go to previous message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi,
im really sorry for my english, i hope this clear up some things, so i have a table which will hold my discounts, this is matgen, i shrink the table for now for the most important columns:


SQL> create table matgen(
  2  	bran_code	varchar2(5),
  3  	cust_code	varchar2(16),
  4  	comm_code	varchar2(5),
  5  	origin		varchar2(5),
  6  	destination	varchar2(5),
  7  	cocl_code	varchar2(5),
  8  	cosi_code	varchar2(5),
  9  	coty_code	varchar2(5),
 10  	factor		number(10,4),
 11  	basis		varchar2(5),
 12  	sear_code	varchar2(5),
 13  	sash_seq_no	number(7),
 14  	chin_code	varchar2(5),
 15  	new_sear	varchar2(5),
 16  	f_std_frt	number(10),
 17  	f_gross_bsc	number(10),
 18  	f_trucking_orig	number(10),
 19  	f_trucking_dest	number(10)
 20  )
 21  /

Table created

SQL> 



here's the result of the query from two tables CS_SAS_HEADER and CS_SAS_CHIN_DETAIL, the query is long so here's just the result set:


BRAN_CODE CUST_CODE        COMM_CODE ORIGIN DESTINATION COCL_CODE COSI_CODE COTY_CODE       FACTOR BASIS SEAR_CODE SASH_SEQ_NO CHIN_CODE
--------- ---------------- --------- ------ ----------- --------- --------- --------- ------------ ----- --------- ----------- ---------
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     133             25661 TRKND
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     4090            25661 TRKNO
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     95              25661 TRKNO
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     96              25661 TRKNO
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     97              25661 TRKNO
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V     99              25661 TRKNO
MNL       TD001-00000-12   LI003     MNL    CGY         A         20FTR     DV              1.0000 V                     25661 FRT

7 rows selected

SQL> 




as you see there are chin_codes, namely FRT, BSC, TRKNO and TRKND, now i need to insert this rows into matgen, and also compute the values for f_std_frt, gross_bsc, trucking_orig & dest. but i must manipulate the rows first, in this example there are 1 TRKND and 5 TRKNO, and as always 1 FRT or 1 BSC if theres any. there is a case where theres only 1 FRT 1 TRKNO and 1 TRKND, so this produces 3 rows in the above query, then I must merge that into one row.

so if the computed value for FRT is 500, then for 1 TRKNO is 300 and for TRKND is 600 the row would be 1 in the table


[othercols]      f_std_frt        f_gross_bsc      truckingorig 	truckingdest
                 500		  0                300			600



now in my example above because theres too many TRKNO and a TRKND, the same rule apply for FRT, i just have to copy
its value to every row of TRKND and TRKNO, but for the row of TRKNO and TRKND i then need to "cross product" it like this
so if each has this value
1 FRT = 400
the 5 TRKNO has 20, 30, 40, 50, 60
and the TRKND has 70
then the rows that would be inserted should be like this


[othercols]      f_std_frt        f_gross_bsc      truckingorig 	truckingdest
                 500		  0                20			70
                 500		  0                30			70
                 500		  0                40			70
                 500		  0                50			70
                 500		  0                60			70






thank you so much =)




Previous Topic: Partial Primay Key
Next Topic: Maximum Number of Tables
Goto Forum:
  


Current Time: Wed Sep 17 09:02:08 CDT 2014

Total time taken to generate the page: 0.10313 seconds