Ilmu Sql

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