1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Fill data to other column

Discussion in 'SQL PL/SQL' started by rosy, Mar 21, 2016.

  1. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Dear Everyone,
    Could everyone help me fill data to quick report using oracle 11, more detail could you please check attached file?
    I'm sorry because I cannot direct create db on database so I only show you data I want to get from db.

    It is important with me. so please help

    Thank you so much
     

    Attached Files:

  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    see on additional links:
    analytic function
    oracle-base


    for example ...

    Code (SQL):
    WITH
        your_datum (model,assy_code,assy_code_desc,part_no,descr,qty,fill)
        AS
        (
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFA163914G','R:CARBON FIXED CHIP(1005)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BCML311045G','C:CERAMIC M/L (1608) TAPE',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BCNA811002G','C:CERAMIC M/L (1005) TAPE',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BCXT311065G','C:CERAMIC M/L (2125) TAPE',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFA163324G','R:CARBON FIXED CHIP(1005)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFC121214G','R:CARBON FIXED CHIP(5025)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BPEG2453AAZ','PCB:BASE MAIN',1,'BPEG2453AAZ'  FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BCNA811504G','C:CERAMIC M/L (1005) TAPE',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFA161004G','R:CARBON FIXED CHIP(1005)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFA161544G','R:CARBON FIXED CHIP(1005)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BDBN1218001','TRANSISTOR',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BRFC016854G','R:CARBON FIXED CHIP(2125)',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BDEG5013003','INTEGRATED CIRCUIT',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BCNA813098G','C:CERAMIC M/L (1005) TAPE',1,NULL FROM dual UNION ALL
            SELECT 'UN453FV','AN453FVBAE','BASE MAIN ASSY','BDEG5121003','INTEGRATED CIRCUIT',1,NULL FROM dual
        )
    SELECT d.* ,
       first_value(fill) IGNORE NULLS OVER(partition BY model,assy_code,assy_code_desc) fill_new
    FROM
        your_datum d;
     
    Last edited: Mar 21, 2016
  3. rosy

    rosy Active Member

    Messages:
    19
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    vietnamese
    Thank you so much for your support,
    But in excel file it only one model, we have a lot of model not only one. I can create macro to process it after download all data but my manager he don't want to do it.
    I will continue thinking and please help me one more.

    Thank you.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    if a task not a constant, then it is possible to:

    save excel file as csv and loading of data by SQLLoader
    or
    use OO4O
     
    rosy likes this.