Thursday, 26 July 2012
#20 Join table dengan data yang di select
03:42 | 
Posted by
Koral Web
select distinct a.tahun, a.bulan, a.tanggal, userid from (
select distinct year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
from ngac_log
where year(logtime)=2012 and month(logtime)=5
)a left join (
select distinct userid, year(logtime) tahun, month(logtime) bulan, day(logtime) tanggal
from ngac_log
where year(logtime)=2012 and month(logtime)=5 and userid='002412430000000'
) b on (a.tahun=b.tahun and a.bulan=b.bulan and a.tanggal=b.tanggal)
order by 3
Friday, 20 July 2012
#19. Menampilkan seluruf field dalam tabel di MySQL
18:02 |
Posted by
Koral Web
show full fields from database.nama_tabel
#18 Menampilkan seluruh tabel di MySQL
18:00 |
Posted by
Koral Web
show full tables from nama_tabel where table_type = 'BASE TABLE'
Thursday, 19 July 2012
#16 Partition Tabel di MySQL
19:06 |
Posted by
Koral Web
tutorial yang bagus bagi yang ingin mempelajari Partition Table di MySQL
http://www.slideshare.net/datacharmer/mysql-partitions-tutorial
http://www.slideshare.net/datacharmer/mysql-partitions-tutorial
Tuesday, 17 July 2012
#15 Penggunaan Cursor di SQL Server
20:18 |
Posted by
Koral Web
DECLARE @urutan int
declare @tipeid int
declare @approver1 int
declare @approver2 int
declare @approver3 int
declare @approver4 int
declare @approver5 int
DECLARE cur_approval cursor FOR
select urutan, a.tipeid, b.approver1, b.approver2, b.approver3, b.approver4, b.approver5
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where tipe='HO' order by urutan
OPEN cur_approval
FETCH next from cur_approval into @urutan, @tipeid, @approver1, @approver2, @approver3, @approver4, @approver5
WHILE @@fetch_status =0
BEGIN
select urutan, a.tipeid, b.approver1 approver
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where urutan=@urutan and a.tipeid=@tipeid
union
select urutan, a.tipeid, b.approver2
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where urutan=@urutan and a.tipeid=@tipeid and approver2 is not null and approver2<>0
union
select urutan, a.tipeid, b.approver3
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where urutan=@urutan and a.tipeid=@tipeid and approver3 is not null and approver3<>0
union
select urutan, a.tipeid, b.approver4
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where urutan=@urutan and a.tipeid=@tipeid and approver4 is not null and approver4<>0
union
select urutan, a.tipeid, b.approver5
from MApprovalCapexTipe a
inner join mApprovalCapex b on (a.tipeid=b.tipeid)
where urutan=@urutan and a.tipeid=@tipeid and approver5 is not null and approver5<>0
FETCH next from cur_approval into @urutan, @tipeid, @approver1, @approver2, @approver3, @approver4, @approver5
END
CLOSE cur_approval
DEALLOCATE cur_approval
Monday, 9 July 2012
#14. Query dengan data di server yang berbeda
00:44 |
Posted by
Koral Web
Berikut adalah sampel melakukan query data dengan menggunakan query dengan data di server yang berbeda...
:)
select a.approvalid, approvalname, tipe + ' - ' + name tipe,
a.approver1, b.position_name_en approvername1,
a.approver2, c.position_name_en approvername2,
a.approver3, d.position_name_en approvername3,
a.approver4, e.position_name_en approvername4,
a.approver5, f.position_name_en approvername5
from MApprovalCapex a
inner join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) b on (a.approver1=b.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) c on (a.approver2=c.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) d on (a.approver3=d.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) e on (a.approver4=e.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) f on (a.approver5=f.position_id)
inner join MApprovalCapexTipe g on (a.tipeid=g.id)
a.approver1, b.position_name_en approvername1,
a.approver2, c.position_name_en approvername2,
a.approver3, d.position_name_en approvername3,
a.approver4, e.position_name_en approvername4,
a.approver5, f.position_name_en approvername5
from MApprovalCapex a
inner join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) b on (a.approver1=b.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) c on (a.approver2=c.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) d on (a.approver3=d.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) e on (a.approver4=e.position_id)
left join (SELECT position_id, position_name_en FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=HRMSDB;User ID=developer;Password=d3v3l0p3r').namaserver.dbo.VW_POSITION where position_flag=3) f on (a.approver5=f.position_id)
inner join MApprovalCapexTipe g on (a.tipeid=g.id)
:)
Labels:
Subscribe to:
Posts (Atom)