-
Sql query
Hi guys,
I am evergreen to ORACLE forgive my ignorance.
Can some one help me with this sql cmmd.
I got 2 tables from where I got to collect some info and I want it in the following fashion.
here are the tables first
Table1: ProdFile
col1:CODE
col2:Prdid
CODE--Prdid
AS-----123
AF-----334
AG-----445
AS-----662
AF-----221
AS-----567
AG-----555
Table2: ShpFile
col1: Prdid
col2: Dt(mm/dd/yy)
col3: Qty
col4: Transport
col5: Pkgd
Prdid---ShipngDT--Qty--T/R-Pkgd
123-----12/01/02-- 20 --R--BULK
221-----12/07/02-- 10 --T--BAG
662-----12/09/02-- 20 --R--BULK
445-----12/20/02-- 30 --T--BAG
567-----12/21/02-- 40 --R--BULK
334-----12/22/02-- 50 --T--BAG
567-----12/27/02--120 --T--BULK
221-----12/29/02--100 --R--BULK
555-----12/30/02--200 --R--BAG
I want to display the records in this manner
Code--R/Bulk--T/Bulk--R/Bag--T/Bag
-AS-----80-----120--------------
-AF----------------------------60
-AG--------------------200-----30
how did I get 80 in R-Bulk:
AS has the following Prdids in the file prodFile
123
662
567
The shpFile has
123 with 20 and its Transport is Rail and Pkgcd is BULK so is
662 with 20 and its Transport is Rail and Pkgcd is BULK so is
567 with 40 and its Transport is Rail and Pkgcd is BULK so the
total qty is 80.
Is it possible to create a single SQL statement based on dates between 12/01/02 and 12/31/02 of all the codes+prdid from table prodFile. It has to have seperate totals for Rail-Bag, Rail-Bulk and Truck-Bag,Truck-Bulk. If this is possible then it would save me a great deal of work.
Thanks
Sincerely
Paps
Last edited by paps; 11-15-2002 at 02:19 PM.
-
Which version of oracle database are you going to run the query on, 8i/9i?
Adams
-
Hi!
just try this sql...
select code,
sum(decode(rtrim(transport)||rtrim(pkgd),'RBULK',qty,0)) RBULKSUM,
sum(decode(rtrim(transport)||rtrim(pkgd),'TBULK',qty,0)) TBULKSUM,
sum(decode(rtrim(transport)||rtrim(pkgd),'RBAG',qty,0)) RBAGSUM,
sum(decode(rtrim(transport)||rtrim(pkgd),'TBAG',qty,0)) TBAGSUM
from prodfile a,shpfile b
where rtrim(a.prdid) = rtrim(b.prdid)
group by code
cheers
nanda
Last edited by r_nanda_k; 11-16-2002 at 12:42 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|