Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Location
    Hamburg, Germany
    Posts
    1

    Unanswered: converting string to number

    Hi,

    I've got a question regarding the conversion from string to number.
    When I make the function call ToNumber('0.7') I get 7 as result instead of 0.7. Does anyone have an idea why and how I can solve the problem?

    Thanks for your help!
    calamari

  2. #2
    Join Date
    Jul 2009
    Posts
    2
    Make sure you have a decimal point not a comma between 0 and 7.

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    nvl(sum(case when a.DESCRIPTION_CODE in (181039) then a.AMOUNT end)/100,0)

    Please suggest how to implement above mentioned like query in crystal report
    Above mentioed query is part of below mwntioned query


    select

    g.DISPLAY_VALUE circle,

    g.MKT_CODE,

    b.BILL_PERIOD,

    b.TO_DATE,

    nvl(sum(case when a.DESCRIPTION_CODE in (181039) then a.AMOUNT end)/100,0) MCA_Rental,

    nvl(sum(case when a.DESCRIPTION_CODE in (181031) then a.AMOUNT end)/100,0) GPRS_Rental,

    nvl(sum(case when a.DESCRIPTION_CODE in (181042) then a.AMOUNT end)/100,0) CRBT_Rental

    from air_mis_delhi.mv_bill_invoice_detail a,

    air_mis_delhi.mv_bill_invoice b,

    air_mis_delhi.mv_cmf e,

    air_mis_delhi.mv_mkt_code_values g

    where a.BILL_REF_NO = b.BILL_REF_NO

    and b.TO_DATE >= '01-jun-2009'

    and b.PREP_STATUS = 1

    and b.BACKOUT_STATUS = 0

    and b.PREP_ERROR_CODE is null

    and b.jnl_status = 0

    and a.DESCRIPTION_CODE in (181039,181031,181042)

    and b.ACCOUNT_NO = e.ACCOUNT_NO

    and e.MKT_CODE = g.MKT_CODE

    group by

    g.DISPLAY_VALUE,

    g.MKT_CODE,

    b.BILL_PERIOD,

    b.TO_DATE


    i have designed report using SQL command but i want to create report without using SQL command.

  4. #4
    Join Date
    Jul 2009
    Posts
    3
    Hi,

    Please suggest how to use UNION ALL command in crystal report without using SQL COMMAND.

    i m having below mentioned query

    select
    mk.DISPLAY_VALUE Circle,
    trunc(d.trans_dt) TRANS_DATE,
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end VAS_FLAG,
    d.POINT_TARGET,
    substr(annotation,1,5) VAS_DIRECTION,
    sum(to_number(d.PRIMARY_UNITS)) mou,
    sum(d.BASE_AMT/100) amount
    from
    air_mis_delhi.mv_cdr_data d,
    air_mis_delhi.mv_cmf e,
    air_mis_delhi.mv_mkt_code_values mk
    where trunc(d.trans_dt) between '01-jul-2009' and trunc(sysdate-1)
    and d.TYPE_ID_USG in (20702,20600)
    and d.ACCOUNT_NO = e.ACCOUNT_NO
    and e.MKT_CODE = mk.MKT_CODE
    group by mk.DISPLAY_VALUE,trunc(d.trans_dt),
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end,
    d.POINT_TARGET,substr(annotation,1,5)
    union all
    select
    mk.DISPLAY_VALUE Circle,
    trunc(d.trans_dt) TRANS_DATE,
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end VAS_FLAG,
    d.POINT_TARGET,
    substr(annotation,1,5) VAS_DIRECTION,
    sum(to_number(d.PRIMARY_UNITS)) mou,
    sum(d.BASE_AMT/100) amount
    from
    air_mis_ap_kar.mv_cdr_data d,
    air_mis_ap_kar.mv_cmf e,
    air_mis_ap_kar.mv_mkt_code_values mk
    where trunc(d.trans_dt) between '01-jul-2009' and trunc(sysdate-1)
    and d.TYPE_ID_USG in (20702,20600)
    and d.ACCOUNT_NO = e.ACCOUNT_NO
    and e.MKT_CODE = mk.MKT_CODE
    group by mk.DISPLAY_VALUE,trunc(d.trans_dt),
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end,
    d.POINT_TARGET,substr(annotation,1,5)
    union all
    select
    mk.DISPLAY_VALUE Circle,
    trunc(d.trans_dt) TRANS_DATE,
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end VAS_FLAG,
    d.POINT_TARGET,
    substr(annotation,1,5) VAS_DIRECTION,
    sum(to_number(d.PRIMARY_UNITS)) mou,
    sum(d.BASE_AMT/100) amount
    from
    air_mis_ker_mum.mv_cdr_data d,
    air_mis_ker_mum.mv_cmf e,
    air_mis_ker_mum.mv_mkt_code_values mk
    where trunc(d.trans_dt) between '01-jul-2009' and trunc(sysdate-1)
    and d.TYPE_ID_USG in (20702,20600)
    and d.ACCOUNT_NO = e.ACCOUNT_NO
    and e.MKT_CODE = mk.MKT_CODE
    group by mk.DISPLAY_VALUE,trunc(d.trans_dt),
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end,
    d.POINT_TARGET,substr(annotation,1,5)
    union all
    select
    mk.DISPLAY_VALUE Circle,
    trunc(d.trans_dt) TRANS_DATE,
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end VAS_FLAG,
    d.POINT_TARGET,
    substr(annotation,1,5) VAS_DIRECTION,
    sum(to_number(d.PRIMARY_UNITS)) mou,
    sum(d.BASE_AMT/100) amount
    from
    air_mis_up.mv_cdr_data d,
    air_mis_up.mv_cmf e,
    air_mis_up.mv_mkt_code_values mk
    where trunc(d.trans_dt) between '01-jul-2009' and trunc(sysdate-1)
    and d.TYPE_ID_USG in (20702,20600)
    and d.ACCOUNT_NO = e.ACCOUNT_NO
    and e.MKT_CODE = mk.MKT_CODE
    group by mk.DISPLAY_VALUE,trunc(d.trans_dt),
    case when d.TYPE_ID_USG = 20702 then 'WAP_Browsing'
    when d.TYPE_ID_USG = 20600 then 'WAP_Content_DWLD'
    end,
    d.POINT_TARGET,substr(annotation,1,5)

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    nvl(sum(case when a.DESCRIPTION_CODE in (181039) then a.AMOUNT end)/100,0)


    how to implement above mentioned query without using sql commant option in crystal report

Posting Permissions

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