Home » SQL & PL/SQL » SQL & PL/SQL » logic help
| logic help [message #198371] |
Mon, 16 October 2006 21:05  |
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 529 times)
|
|
|
|
| Re: logic help [message #198411 is a reply to message #198371] |
Tue, 17 October 2006 02:14  |
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 |
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 =)
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 00:26:42 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
