Sql query
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Sql query

  1. #1
    Join Date
    Nov 2002
    Location
    Toronto Canada
    Posts
    3

    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 01:19 PM.

  2. #2
    Join Date
    Oct 2002
    Posts
    5
    Which version of oracle database are you going to run the query on, 8i/9i?

    Adams

  3. #3
    Join Date
    Nov 2002
    Location
    PA
    Posts
    2
    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 11:42 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width