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

Make sure you have a decimal point not a comma between 0 and 7.

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.

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)

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