Forum Stats

  • 3,757,262 Users
  • 2,251,216 Discussions
  • 7,869,781 Comments

Discussions

Deal with exportion report query with value has comma

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

Hi everyone,

My report query is :

<>code

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

    dl.com_number,

    dl.unit,

    dl.commodity_name,

    dl.org,

    dl.loc,

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

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

    nvl(case

     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,

    nvl(case

     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.commodity_name,

        ls.com_quantity as SL_SS,

        0 as SL_KK,

        o.name org,

        l.name 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',

                                :EMP_ID,

                                '',

                                'KKHH'),

                    o.Oun_Id,

                    ':') = '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 ci.id) as SL_KK,

        o.name org,

        l.name loc,

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

        ci.remarks 

     from perp5.locations     l,

        perp5.organization_units o,

        --PERP5.EDU_PHYSICAL_INVENTORY pi,

        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',

                                :EMP_ID,

                                '',

                                'KKHH'),

                    o.Oun_Id,

                    ':') = '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,

         o.name,

         l.name,

         ci.unit,

         ci.phy_inv_id,

         ci.quality,

         ci.low_units,

         ci.remarks) dl

 where 1 = 1

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

    dl.remarks--,

<>/code

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.