Senin, 23 Juli 2012
Belajar ABAP (2)
REPORT yrp_andy_latihan1 .
write:/ '------------------------- Date ------------------'.
write:/ sy-datum.
write:/ sy-datlo.
write:/ sy-dayst.
write:/ sy-fdayw.
write:/ sy-timlo.
write:/ sy-uzeit.
write:/ '------------------------- Date ------------------'.
write:/ '------------------------- Date ------------------'.
write:/ sy-datum.
write:/ sy-datlo.
write:/ sy-dayst.
write:/ sy-fdayw.
write:/ sy-timlo.
write:/ sy-uzeit.
write:/ '------------------------- Date ------------------'.
Kamis, 12 Juli 2012
Belajar ABAP (1)
Akhirnya dateng juga kesempetan gwe belajar ABAP. Mumpung dikasih kesempatan buat ngoprek ABAP dikantor, harus dimanfaatin bener2 nih :D ini koding pertama gwe...
REPORT yrp_andy_latihan1 .
* Definisi data
DATA : dg_a TYPE i, dg_b TYPE i, dg_c TYPE p DECIMALS 2, dg_d TYPE p
DECIMALS 2.
DATA: dg_name LIKE sy-uname, dg_tanggal LIKE sy-datum.
* initialisasi
dg_a = 10.
dg_b = 5.
dg_c = dg_a / dg_b.
dg_d = dg_a * dg_b.
dg_name = sy-uname.
dg_tanggal = sy-datum.
* output
WRITE:/ 'Hasil ', dg_a, ' + ', dg_b, '=', dg_c.
WRITE:/ 'Hasil ', dg_a, ' * ', dg_b, '=', dg_d.
WRITE:/ 'USERNAME : ', dg_name.
WRITE:/ 'Tanggal : ', dg_tanggal.
REPORT yrp_andy_latihan1 .
* Definisi data
DATA : dg_a TYPE i, dg_b TYPE i, dg_c TYPE p DECIMALS 2, dg_d TYPE p
DECIMALS 2.
DATA: dg_name LIKE sy-uname, dg_tanggal LIKE sy-datum.
* initialisasi
dg_a = 10.
dg_b = 5.
dg_c = dg_a / dg_b.
dg_d = dg_a * dg_b.
dg_name = sy-uname.
dg_tanggal = sy-datum.
* output
WRITE:/ 'Hasil ', dg_a, ' + ', dg_b, '=', dg_c.
WRITE:/ 'Hasil ', dg_a, ' * ', dg_b, '=', dg_d.
WRITE:/ 'USERNAME : ', dg_name.
WRITE:/ 'Tanggal : ', dg_tanggal.
Sabtu, 11 Februari 2012
Parsing data
update profil_dokter
set isi_profil=replace(isi_profil,
(
substring(
substring(isi_profil,instr(isi_profil,"![]()
1,
instr(substring(isi_profil,instr(isi_profil,"![]()
')
)
),"")
where isi_profil !='' and isi_profil like '%src="%'
set isi_profil=replace(isi_profil,
(
substring(
substring(isi_profil,instr(isi_profil,"
1,
instr(substring(isi_profil,instr(isi_profil,"
)
),"")
where isi_profil !='' and isi_profil like '%src="%'
Akal-akalan
create table x as
select id_dokter, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"![]()
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
select id_dokter, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
inner join (
SELECT id_dokter, reverse(substring(REVERSE(pathgambar), 1, instr(REVERSE(pathgambar),'/')-1)) gambar_dokter
from x
)y on (a.id_dokter=y.id_dokter)
set gambar=gambar_dokter
Bermain-main dengan path
select id_dokter, gambar, substring(gambar,1,instr(gambar,'"')-1) pathgambar
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"![]()
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
from (
select id_dokter, path, substring(path,instr(path, "src")+6) gambar
from (
select id_dokter, isi_profil, gambar,
substring(isi_profil,instr(isi_profil,"
from profil_dokter where isi_profil !='' and isi_profil like '%src="%'
)a
)x
Senin, 06 Februari 2012
Subquery lagi
update k set incentive=(case when percentage_am=1 then k.incentive else k.incentive/2 end)
from trmasterincentive_summary k
inner join (
select x.idstructure, incentive,
case when (cast(y.jumlahAM_ach as float)) / (cast (y.JumlahAM as float)) > 0.6
then 1
else 0
end percentage_am
from trmasterincentive_summary x
inner join (
select a.idstructure,
(select COUNT(distinct idstructure_am) from structorg where year=2011 and month=11 and idcat=10 and idstructure_sm=a.idstructure)JumlahAM,
(
select COUNT(distinct idstructure_am)
from trmasterincentive_summary p
inner join structorg q on (p.idstructure=q.idstructure_am and q.idstructure_sm=a.idstructure)
where idjob in(3,29) and percentage>=90
)jumlahAM_ach
from trmasterincentive_summary a
where idlogstructure=176 and idjob in(1,24) and percentage>=90
) y on (x.idstructure=y.idstructure)
) l on (k.idstructure=l.idstructure)
from trmasterincentive_summary k
inner join (
select x.idstructure, incentive,
case when (cast(y.jumlahAM_ach as float)) / (cast (y.JumlahAM as float)) > 0.6
then 1
else 0
end percentage_am
from trmasterincentive_summary x
inner join (
select a.idstructure,
(select COUNT(distinct idstructure_am) from structorg where year=2011 and month=11 and idcat=10 and idstructure_sm=a.idstructure)JumlahAM,
(
select COUNT(distinct idstructure_am)
from trmasterincentive_summary p
inner join structorg q on (p.idstructure=q.idstructure_am and q.idstructure_sm=a.idstructure)
where idjob in(3,29) and percentage>=90
)jumlahAM_ach
from trmasterincentive_summary a
where idlogstructure=176 and idjob in(1,24) and percentage>=90
) y on (x.idstructure=y.idstructure)
) l on (k.idstructure=l.idstructure)
Rabu, 01 Februari 2012
Query Terburuk
query terburuk yang pernah gwe buat... execution timenya 30menit... :((
Senin, 30 Januari 2012
Penggunaan Sub Query untuk mengghitung incentive perkategori
select c.* from (
select idstructure, sum(salesgrosspharma1) salesgrosspharma1, sum(targetpharma1) targetpharma1, sum(PercentagePharma1)PercentagePharma1,
sum(salesgrosspharma2) salesgrosspharma2, sum(targetpharma2) targetpharma2, sum(PercentagePharma2) PercentagePharma2,
sum(salesgrosspharma3) salesgrosspharma3, sum(targetpharma3) targetpharma3, sum(PercentagePharma3) PercentagePharma3
from (
select idstructure,
case
when idsubcat=21 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end salesgrossPharma1,
case
when idsubcat=21 then (select target from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end targetPharma1,
case
when idsubcat=21 then (select percentage from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end PercentagePharma1,
case
when idsubcat=22 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end salesgrossPharma2,
case
when idsubcat=22 then (select target from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end targetPharma2,
case
when idsubcat=22 then (select percentage from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end PercentagePharma2,
case
when idsubcat=23 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end salesgrossPharma3,
case
when idsubcat=23 then (select target from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end targetPharma3,
case
when idsubcat=23 then (select percentage from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end PercentagePharma3
from TrMasterIncentiveSubCat a
)b
group by idstructure
) c inner join trrekapidcovjob d on (c.idstructure=d.idstructure)
where idlogincentive=167
select idstructure, sum(salesgrosspharma1) salesgrosspharma1, sum(targetpharma1) targetpharma1, sum(PercentagePharma1)PercentagePharma1,
sum(salesgrosspharma2) salesgrosspharma2, sum(targetpharma2) targetpharma2, sum(PercentagePharma2) PercentagePharma2,
sum(salesgrosspharma3) salesgrosspharma3, sum(targetpharma3) targetpharma3, sum(PercentagePharma3) PercentagePharma3
from (
select idstructure,
case
when idsubcat=21 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end salesgrossPharma1,
case
when idsubcat=21 then (select target from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end targetPharma1,
case
when idsubcat=21 then (select percentage from TrMasterIncentiveSubCat where idsubcat=21 and idstructure=a.idstructure)
else 0
end PercentagePharma1,
case
when idsubcat=22 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end salesgrossPharma2,
case
when idsubcat=22 then (select target from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end targetPharma2,
case
when idsubcat=22 then (select percentage from TrMasterIncentiveSubCat where idsubcat=22 and idstructure=a.idstructure)
else 0
end PercentagePharma2,
case
when idsubcat=23 then (select salesgross from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end salesgrossPharma3,
case
when idsubcat=23 then (select target from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end targetPharma3,
case
when idsubcat=23 then (select percentage from TrMasterIncentiveSubCat where idsubcat=23 and idstructure=a.idstructure)
else 0
end PercentagePharma3
from TrMasterIncentiveSubCat a
)b
group by idstructure
) c inner join trrekapidcovjob d on (c.idstructure=d.idstructure)
where idlogincentive=167
Rabu, 25 Januari 2012
Update subquery
update trmasterincentive set average_incentivenp=incentivenp
from trmasterincentive a
inner join (
select a.idstructure, incentive, cast(incentive*score as int) incentiveNP
from (
select idstructure, percentage, sum(incentive) incentive from _incentiveNPPercentage
group by idstructure, percentage
)a inner join trmasterincentive b on (a.idstructure=b.idstructure)
inner join mscore c on (status=10 and a.percentage between fromrange and torange)
) b on (a.idstructure=b.idstructure)
from trmasterincentive a
inner join (
select a.idstructure, incentive, cast(incentive*score as int) incentiveNP
from (
select idstructure, percentage, sum(incentive) incentive from _incentiveNPPercentage
group by idstructure, percentage
)a inner join trmasterincentive b on (a.idstructure=b.idstructure)
inner join mscore c on (status=10 and a.percentage between fromrange and torange)
) b on (a.idstructure=b.idstructure)
Senin, 23 Januari 2012
Sub Query
select * from trmasterincentive x
inner join (
select idstructure, average_incentive, fromrange, torange, score,
case when average_incentive between fromrange and torange then score
else average_incentive
end new_average_incentive
from _average_incentive a
inner join mscore b on (a.idscheme=b.idscheme and status=6 and isactive='T')
) y on (x.idstructure=y.idstructure)
inner join (
select idstructure, average_incentive, fromrange, torange, score,
case when average_incentive between fromrange and torange then score
else average_incentive
end new_average_incentive
from _average_incentive a
inner join mscore b on (a.idscheme=b.idscheme and status=6 and isactive='T')
) y on (x.idstructure=y.idstructure)
Kamis, 19 Januari 2012
Query Iseng
update TrMasterIncentive set idscheme=idscheme2 from TrMasterIncentive aaa inner join (
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
select *
from (
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=0 and ('2012-01-01' between c.StartDate and c.EndDate))
where idcat=10 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and b.iscombo=1 and ('2012-01-01' between c.StartDate and c.EndDate))
where b.iscombo=1 and a.idlogincentive=162
union
select distinct b.idstructure, a.idscheme, c.idscheme idscheme2, c.iscombo
from TrMasterIncentive a
inner join TrRekapIdCovJob b on (a.idstructure=b.idstructure)
inner join mscheme c on (b.idjob=c.idjob and b.idsubcat=c.idsubcat AND IsActive = 'T' and ('2012-01-01' between c.StartDate and c.EndDate))
where b.idjob in(1,24) and a.idlogincentive=162
) xyz
) bbb on (aaa.idstructure=bbb.idstructure)
Langganan:
Postingan (Atom)