Oracle 12c için bir soru (ÖNEMLİ bilen arkadaşlar bakarsa Sevinirim) - R10.net
  • 21-06-2019, 10:22:42
    #1
    Merhaba Arkadaşlar
    çalıştığım yerde Oracle 12c kullanıyorum bir sql yazdım(PL/SQL kullanıyorum) lütfen kimse sql çalışmaz demesin çalışıyor zaten sorunum bundan sonra başlıyor
    yazmış olduğum sql'in içinde HESAPLAMA Modelleride bulunmaktadır.
    SQL sorguyu çalıştırdıktan sonra excel atıyorum ancak hesaplama modellerinin sql de çalışmasından sonra (50.9554140127388 gibi değerler çıkıyor buda normal )
    ancak ben bunu excel gönderdiğimde 50.95 şeklinde gözükmesini istiyorum bu konuda bana yardımcı olabilecek yada fikir verebilir misiniz

    select *
      from (select fmmat.slab_id as SLAB_ID,
                   fmmat.piece_id as COIL_ID,
                   fmmat.material_id as MATERIAL_ID,
                   ros_rm.start_date as PRODUCTION_DATE,
                   corp.customer_type AS SLAB_SOURCE,
                   pord.product_type AS PRODUCT_TYPE,
                   (select substr(pg.remark, -3)
                      from l3main.PG pg
                     where pg.program_id =
                           (select pgl.program_id
                              from l3main.PGL pgl
                             where pgl.material_id = fmmat.material_id
                             order by pgl.program_id desc offset 0 rows fetch next 1 row only)) as SCHEDULE_NUMBER,
                   corp.CUSTOMER_TYPE AS EXPORT_CODE,
                   1000 * pord.output_weight_target /
                   (pord.thickness * pord.width * 7.85) AS PROGRAM_LENGTH,
                   pord.output_weight_target AS PROGRAM_TONNAGE,
                   fmsteel.steel_grade_int_text_en as STEEL_GRADE,
                   insteel.steel_grade_int_text_en AS CASTING_GRADE,
                   rm.CHARGING_TIME AS CHARGING_TIME,
                   rm.DISCHARGING_TIME AS DISCHARGING_TIME,
                   rm.CHARGING_TEMP AS CHARGING_TEMP,
                   rm.DISCHARGING_TEMP AS DISCHARGING_TEMP,
                   inmat.length * 1000 AS SLAB_LENGTH,
                   inmat.weight * 1000 AS SLAB_WEIGHT,
                   fmmat.width AS STRIP_WIDTH,
                   fmmat.thickness AS STRIP_THICKNESS,
                   rm.NO_PASSES AS RM_NO_OF_PASSES,
                   rmmat.thickness AS RM_EXIT_TK,
                   null AS RM_EXIT_TEMP_MIN,
                   rm.roll_rm_temp AS RM_EXIT_TEMP_AVG,
                   null AS RM_EXIT_TEMP_MAX,
                   rm.RM_DESC_STATUS1 AS RM_DESCALE_PASS_1,
                   rm.RM_DESC_STATUS2 AS RM_DESCALE_PASS_2,
                   rm.RM_DESC_STATUS3 AS RM_DESCALE_PASS_3,
                   rm.RM_DESC_STATUS4 AS RM_DESCALE_PASS_4,
                   rm.RM_DESC_STATUS5 AS RM_DESCALE_PASS_5,
                   rm.RM_DESC_STATUS6 AS RM_DESCALE_PASS_6,
                   rm.RM_DESC_STATUS7 AS RM_DESCALE_PASS_7,
                   rm.COILBOX_STATUS AS COIL_BOX_STATUS,
                   fm.FM_ENTRY_DES_STATUS AS ENTRY_DESCALER_STATUS,
                   fm.FM_EXIT_DES_STATUS AS EXIT_DESCALER_STATUS,
                   fm.entry_fm_min_temp AS FM_ENTRY_TEMP_MIN,
                   fm.entry_fm_temp AS FM_ENTRY_TEMP_AVG,
                   fm.entry_fm_max_temp AS FM_ENTRY_TEMP_MAX,
                   fm.thickness_min AS FM_EXIT_TK_MIN,
                   fmmat.thickness AS FM_EXIT_TK_AVG,
                   fm.thickness_max AS FM_EXIT_TK_MAX,
                   fm.width_min AS FM_EXIT_WD_MIN,
                   fmmat.width AS FM_EXIT_WD_AVG,
                   fm.width_max AS FM_EXIT_WD_MAX,
                   fm.length_head_uncool AS UNCOOLED_HEAD,
                   fm.length_tail_uncool AS UNCOOLED_TAIL,
                   fm.cooling_strategy AS COOLING_STRATEGY,
                   fm.roll_fm_temp_min AS FM_EXIT_TEMP_MIN,
                   fm.roll_fm_temp AS FM_EXIT_TEMP_AVG,
                   fm.roll_fm_temp_max AS FM_EXIT_TEMP_MAX,
                   fm.profile_c40_min AS FM_CROWN_MIN,
                   fm.profile_c40 AS FM_CROWN_AVG,
                   fm.profile_c40_max AS FM_CROWN_MAX,
                   fm.wedge_c40_min AS FM_WEDGE_MIN,
                   fm.wedge_c40 AS FM_WEDGE_AVG,
                   fm.wedge_c40_max AS FM_WEDGE_MAX,
                   fm.flatness_sym as FM_FLATNESS_SYM,
                   fm.flatness_asym as FM_FLATNESS_ASYM,
                   fm.coil_temp_min AS FM_COILING_TEMP_MIN,
                   fm.coil_temp AS FM_COILING_TEMP_AVG,
                   fm.coil_temp_max AS FM_COILING_TEMP_MAX,
                   fmmat.weight * 1000 AS FM_COIL_WEIGHT,
                   fmmat.length AS FM_COIL_LENGTH,
                   fmmat.diameter_out AS FM_COIL_OD,
                   fm.downcoiler_id as COILER_NUMBER,
                   'n.a.' AS QUALITY_CONTROL_CODE,
                   'n.a.' AS QUALITY_CONTROL_EXPLANATION,
                   corp.order_reference_no AS CUSTOMER_CODE,
                   pord.production_order_id AS production_order_id
              from l3main.R_OS ros
              left join l3main.R_MAT fmmat
                on fmmat.r_os_id = ros.r_os_id
              left join l3main.R_FM fm
                on fm.r_os_id = fmmat.r_os_id
              left join l3main.R_MAT rmmat
                on (rmmat.r_os_id = fm.r_os_rm and
                   rmmat.piece_id = fmmat.slab_id)
              left join l3main.R_RM rm
                on rm.r_os_id = fm.r_os_rm
              left join l3main.R_OS ros_rm
                on ros_rm.r_os_id = fm.r_os_rm
              left join (select t.*,
                               row_number() over(partition by t.piece_id order by o2.start_date desc) as rn
                          from l3main.R_MAT t
                         inner join (select o1.*
                                      from l3main.R_OS o1
                                     where (o1.production_line_name = 'ATT' or
                                           o1.production_line_name = 'SY' or
                                           o1.production_line_name = 'CCM1')) o2
                            on t.r_os_id = o2.r_os_id) inmat
                on inmat.piece_id = rmmat.slab_id
               and inmat.rn = 1
              left join l3main.POR pord
                on ros.production_order_id = pord.production_order_id
              left join l3main.CORP corp
                on (pord.customer_order_id = corp.customer_order_id and
                   pord.customer_order_pos = corp.customer_order_pos)
              left join masterdata.steel_grade_int fmsteel
                on fmsteel.steel_grade_id_int = fmmat.steel_grade_id_int
              left join masterdata.steel_grade_int insteel
                on insteel.steel_grade_id_int = inmat.steel_grade_id_int
              left join (select row_number() over(partition by t.material_id order by t.treatment_no desc) as rn,
                               t.*
                          from L3main.PDI_HSM t) pdihsm
                on pdihsm.material_id = rmmat.material_id
               and pdihsm.rn = 1
             where ros.production_line_name = 'FM'
               and ros.operation_status = 'FI'
               and ros.system_flag = 'A'
               and fm.r_os_rm is not null
            UNION
            select rmmat.slab_id as SLAB_ID,
                   '' as COIL_ID,
                   rmmat.material_id as MATERIAL_ID,
                   ros_rm.start_date as PRODUCTION_DATE,
                   corp.customer_type AS SLAB_SOURCE,
                   pord.product_type AS PRODUCT_TYPE,
                   (select substr(pg.remark, -3)
                      from l3main.PG pg
                     where pg.program_id =
                           (select pgl.program_id
                              from l3main.PGL pgl
                             where pgl.material_id = rmmat.material_id
                             order by pgl.program_id desc offset 0 rows fetch next 1 row only)) as SCHEDULE_NUMBER,
                   corp.CUSTOMER_TYPE AS EXPORT_CODE,
                   1000 * pord.output_weight_target /
                   (pord.thickness * pord.width * 7.85) AS PROGRAM_LENGTH,
                   pord.output_weight_target AS PROGRAM_TONNAGE,
                   '' as STEEL_GRADE,
                   insteel.steel_grade_int_text_en AS CASTING_GRADE,
                   rm.CHARGING_TIME AS CHARGING_TIME,
                   rm.DISCHARGING_TIME AS DISCHARGING_TIME,
                   rm.CHARGING_TEMP AS CHARGING_TEMP,
                   rm.DISCHARGING_TEMP AS DISCHARGING_TEMP,
                   inmat.length * 1000 AS SLAB_LENGTH,
                   inmat.weight * 1000 AS SLAB_WEIGHT,
                   null AS STRIP_WIDTH,
                   null AS STRIP_THICKNESS,
                   rm.NO_PASSES AS RM_NO_OF_PASSES,
                   rmmat.thickness AS RM_EXIT_TK,
                   null AS RM_EXIT_TEMP_MIN,
                   rm.roll_rm_temp AS RM_EXIT_TEMP_AVG,
                   null AS RM_EXIT_TEMP_MAX,
                   rm.RM_DESC_STATUS1 AS RM_DESCALE_PASS_1,
                   rm.RM_DESC_STATUS2 AS RM_DESCALE_PASS_2,
                   rm.RM_DESC_STATUS3 AS RM_DESCALE_PASS_3,
                   rm.RM_DESC_STATUS4 AS RM_DESCALE_PASS_4,
                   rm.RM_DESC_STATUS5 AS RM_DESCALE_PASS_5,
                   rm.RM_DESC_STATUS6 AS RM_DESCALE_PASS_6,
                   rm.RM_DESC_STATUS7 AS RM_DESCALE_PASS_7,
                   rm.COILBOX_STATUS AS COIL_BOX_STATUS,
                   0 AS ENTRY_DESCALER_STATUS,
                   0 AS EXIT_DESCALER_STATUS,
                   null AS FM_ENTRY_TEMP_MIN,
                   null AS FM_ENTRY_TEMP_AVG,
                   null AS FM_ENTRY_TEMP_MAX,
                   null AS FM_EXIT_TK_MIN,
                   null FM_EXIT_TK_AVG,
                   null AS FM_EXIT_TK_MAX,
                   null AS FM_EXIT_WD_MIN,
                   null AS FM_EXIT_WD_AVG,
                   null AS FM_EXIT_WD_MAX,
                   null AS UNCOOLED_HEAD,
                   null AS UNCOOLED_TAIL,
                   null AS COOLING_STRATEGY,
                   null AS FM_EXIT_TEMP_MIN,
                   null AS FM_EXIT_TEMP_AVG,
                   null AS FM_EXIT_TEMP_MAX,
                   null AS FM_CROWN_MIN,
                   null AS FM_CROWN_AVG,
                   null AS FM_CROWN_MAX,
                   null AS FM_WEDGE_MIN,
                   null AS FM_WEDGE_AVG,
                   null AS FM_WEDGE_MAX,
                   null AS FM_FLATNESS_SYM,
                   null AS FM_FLATNESS_ASYM,
                   null AS FM_COILING_TEMP_MIN,
                   null AS FM_COILING_TEMP_AVG,
                   null AS FM_COILING_TEMP_MAX,
                   null AS FM_COIL_WEIGHT,
                   null AS FM_COIL_LENGTH,
                   null AS FM_COIL_OD,
                   null AS COILER_NUMBER,
                   'n.a.' AS QUALITY_CONTROL_CODE,
                   'n.a.' AS QUALITY_CONTROL_EXPLANATION,
                   corp.order_reference_no AS CUSTOMER_CODE,
                  pord.production_order_id AS production_order_id
              from l3main.R_OS ros_rm
              left join l3main.R_MAT rmmat
                on rmmat.r_os_id = ros_rm.r_os_id
              left join l3main.R_RM rm
                on rm.r_os_id = ros_rm.r_os_id
              left join (select t.*,
                               row_number() over(partition by t.piece_id order by o2.start_date desc) as rn
                          from l3main.R_MAT t
                         inner join (select o1.*
                                      from l3main.R_OS o1
                                     where (o1.production_line_name = 'ATT' or
                                           o1.production_line_name = 'SY' or
                                           o1.production_line_name = 'CCM1')) o2
                            on t.r_os_id = o2.r_os_id) inmat
                on inmat.piece_id = rmmat.slab_id
               and inmat.rn = 1
              left join l3main.POR pord
                on ros_rm.production_order_id = pord.production_order_id
              left join l3main.CORP corp
                on (pord.customer_order_id = corp.customer_order_id and
                   pord.customer_order_pos = corp.customer_order_pos)
              left join masterdata.steel_grade_int insteel
                on insteel.steel_grade_id_int = inmat.steel_grade_id_int
             where ros_rm.production_line_name = 'RM'
               and ros_rm.usage_type = 0
               and ros_rm.system_flag = 'A')
    where PRODUCTION_DATE >= TO_DATE('19.06.2019', 'dd.mm.yyyy')
       and PRODUCTION_DATE < TO_DATE('21.06.2019', 'dd.mm.yyyy') + 1
    order by PRODUCTION_DATE desc
  • 21-06-2019, 10:31:20
    #2
    https://docs.oracle.com/cd/B19306_01...lements004.htm

    Kendi dökümanlarında aşağıda açıklamışlar, basit bir formatlama kullanmanız gerekiyor. Eğer sorununuzu yanlış anlamadıysam.
  • 21-06-2019, 10:43:00
    #3
    ByNF adlı üyeden alıntı
    https://docs.oracle.com/cd/B19306_01...lements004.htm

    Kendi dökümanlarında aşağıda açıklamışlar, basit bir formatlama kullanmanız gerekiyor. Eğer sorununuzu yanlış anlamadıysam.
    Hocam düşünüp araştırıp konuma yazdığın için teşekkür ederim ancak sorumu yanlış anlamışsın
    benim problemim sorguyu yaptıktan sonra seçmiş olduğum tarih aralıklarının DBde ki çıktısını excel aktarmak
    bu arada EXCEL hiç sevmem
    excel aktarınca ya kadar her şey normal
    excel aktardığımda SQL içinde çalışan hesaplama modeli ''virgül, nokta'' 'dan sonraki değerlerin tamamını excelde görmek istemiyorum yani söyle düşün XX.XX şeklinde sütünda gözükmesini istiyorum