Forum Stats

  • 3,757,262 Users
  • 2,251,216 Discussions


Deal with exportion report query with value has comma

Luffy Member Posts: 37 Red Ribbon
edited Apr 28, 2021 2:17AM in Reports

Hi everyone,

My report query is :


select ROW_NUMBER() OVER (order by dl.com_number, dl.commodity_name desc) STT,





    sum(nvl(dl.sl_ss, 0)) sl_ss,

    sum(nvl(dl.sl_kk, 0)) sl_kk,


     when sum(nvl(dl.sl_ss, 0) - nvl(dl.sl_kk, 0)) < 0 then

     abs(sum(nvl(dl.sl_ss, 0) - nvl(dl.sl_kk, 0)))

    end,0) SLCL_THUA,


     when sum(nvl(dl.sl_ss, 0) - nvl(dl.sl_kk, 0)) > 0 then

     sum(nvl(dl.sl_ss, 0) - nvl(dl.sl_kk, 0))

    end,0) SLCL_THIEU,

    NVL(dl.Bad_quality,0) Bad_quality,

    dl.remarks remarks

--sum(nvl(dl.sl_ss,0) - nvl(dl.sl_kk, 0)) SL_CL

 from (select ls.commodity_num com_number,

        edu_emp_pub.Get_lov2(ls.unit, 'DVT') as unit,


        ls.com_quantity as SL_SS,

        0 as SL_KK, org, loc,

        null Bad_quality,

        '' remarks

     from perp5.locations     l,

        perp5.organization_units o,

        perp5.edu_com_list    ls

     where ls.oun_id = o.oun_id

      and (ls.oun_id = :P312_OUN_ID_HH or :P312_OUN_ID_HH is null)

      and ls.loc_id = l.loc_id

      and ls.phy_inv_id = :P312_PHY_INV_ID

      /*and (wfl_pkg.Check_Select2_String(edu_emp_pub.Get_List_Org('KKHH',





                    ':') = 'TRUE')*/


    union all

    select ci.com_number,

        edu_emp_pub.Get_lov2(ci.unit, 'DVT') as unit,

        (select t.commodity_name

         from edu_commodity t

         where t.commodity_num = ci.com_number) commodity_name,

        0 as SL_SS,

        max(ci.com_quantity) keep(dense_rank last order by as SL_KK, org, loc,

        case when ci.quality = 'true' then ci.low_units end Bad_quality,


     from perp5.locations     l,

        perp5.organization_units o,


        perp5.EDU_COM_INVENTORY ci --,

    --perp5.edu_com_list   ls

     where ci.loc_id = l.loc_id

      and ci.oun_id = o.oun_id

      and ci.phy_inv_id = :P312_PHY_INV_ID

      and (ci.oun_id = :P312_OUN_ID_HH or :P312_OUN_ID_HH is null)

      /*and (wfl_pkg.Check_Select2_String(edu_emp_pub.Get_List_Org('KKHH',





                    ':') = 'TRUE')*/

    /*      and ls.phy_inv_id = ci.phy_inv_id

    and ls.oun_id = ci.oun_id

    and ls.loc_id = ci.loc_id

    and ls.commodity_num = ci.com_number*/

    /*  and pi.type = :type

    and pi.phy_inv_id = :idDot

    and ci.oun_id = :ounId

    and ci.loc_id = :locId*/

     group by ci.com_number,





         ci.remarks) dl

 where 1 = 1

 group by dl.com_number, dl.unit, dl.commodity_name,, dl.loc, dl.Bad_quality,



A problem related to function sum of value includes comma on web.

When I click a button that redirect to page in application = 0 and use request, it return file has nothing. I find a problem in my data which has a decimal value as 0,5 on web. When I test export xml from that report query on DB, and test local rtf layout, it is fine because of data has 0.5. I don't know how to fix that? Thank you for your time and in advance.

P/s: I try to convert my data to varchar, but it is not done for my rtf layout.