If by 'new to the programming world' you mean new to SQL or even programming in general, you are completely ****ed unless by some miracle the untested rough attempt below works correctly first time. I decided to make it a test of my rusty PLSQL, and it must have taken well over an hour in total. So no complaints please. Questions are welcome of course.
- ---TSQL
create proc rpt_schilindet_myn(
@p_case int = NULL, --you can't use %TYPE in TSQL.@p_pronum int = NULL --You need to find out the specific data type and use that (I'm guessing INT))ASset nocount on --since the recordset just falls out of the sp, not in a cursor output param,
set ansi_warnings off --you need to prevent Crystal getting hold of anything else by mistake.
--so you have to suppress warnings and rowcounts.--this would be better done outside the sp if possible, as it causes a recompile.--temp table and iteration index for tree walking. --TSQL doesn't have purpose built functioanlity for tr wlkg--I guessedd the datatypes. didn't know how long the node ids were, so used max for varchar2.create table #tmp_child(tra_serial int, tree_parent varchar(2000), tree_nodeid varchar(2000), tree_id int, tree_number int)
declare @lvl int
select @lvl = 1--this would confuse crystal--print 'p_case number = ' + cast(p_case as varchar(50))--First 'cursor'select ic.itm_id, sp.sp_cardno, sd.schid_serial,
sd.schid_shser, sd.schid_itemno,sd.schid_qty, sd.schid_cstctr,sd.schid_pronum,sd.schid_dpcoradd, ic.itm_itemno, ic.itm_descrp,ic.itm_traflg, ic.itm_crsref, ic.itm_altdesc, ic.itm_lookupnum, ic.itm_inact,ic.itm_type, ic.itm_serial, ci.inv_serial, ci.inv_auxnum1,ci.inv_auxnum2, ci.inv_auxnum3, ci.inv_loct01, ci.inv_bincode,ci.inv_uoi, cc.ctr_serial, cc.ctr_usebinmas, cc.ctr_descrp,pr.prd_catgry, pr.prd_auxnum1, pr.prd_auxnum2, pr.prd_auxnum3,pr.prd_auxnum4, pr.prd_auxnum5, pr.prd_auxnum6, ty.ityp_desc,ty.ityp_auxnum1, ty.ityp_auxnum2, ty.ityp_auxnum3,ty.ityp_auxnum4, ty.ityp_auxnum5, ty.ityp_auxnum6,ty.ityp_dpcorder, ig.igr_descrp, ig.igr_auxnum1, ig.igr_auxnum2,ig.igr_auxnum3into #tmp_parent
from schilindet sd
join itmcat ic on schid_itemno = ic.itm_itemno
join sched_pr sp on schid_shser = sp_serial
and schid_pronum = sp_cpronumleft join cstinv ci on ic.itm_itemno = ci.inv_itemno
left join cstctr cc on ci.inv_cstctr = cc.ctr_cstctr
left join prdmas pr on ic.itm_procat = pr.prd_code(+)
left join itypemas ty on ic.itm_type = ty.ityp_code(+)left join itmgrpdet igd on ic.itm_itemno = igd.igd_itmcode(+)left join itmgrp ig on igd.igd_igrcode = ig.igr_code(+)where sp_serial = @p_case
and sp_cpronum = @p_pronum
--AND ic.itm_traflg = 'Y' --commented out in the original--2nd 'cursor'insert #tmp_child
select @lvl tra_serial,
s.tree_parent,s.tree_nodeid,s.tree_id,s.tree_numberfrom sys_tree s
join #tmp_parent p
on s.tree_parent = p.itm_idwhere s.tree_handle = 0
and = @startwith
while @lvl < 6
begininsert #tmp_childselect @lvl tra_serial,s.tree_parent,s.tree_nodeid,s.tree_id,s.tree_numberfrom #tmp_child rjoin sys_tree son s.tree_parent = r.tree_nodeidwhere s.tree_handle = 0and r.tra_serial = @lvlselect @lvl = @lvl + 1end--outputselectparent_rec.sp_cardno, parent_rec.schid_serial, parent_rec.schid_shser,parent_rec.schid_itemno, parent_rec.schid_pronum, parent_rec.schid_dpcoradd,parent_rec.schid_qty, parent_rec.schid_cstctr,parent_rec.itm_descrp, parent_rec.itm_traflg, parent_rec.itm_inact,parent_rec.itm_altdesc, parent_rec.itm_type, parent_rec.itm_lookupnum,parent_rec.itm_crsref, parent_rec.ctr_serial, parent_rec.ctr_usebinmas,parent_rec.ctr_descrp, parent_rec.inv_serial, parent_rec.inv_auxnum1,parent_rec.inv_auxnum2, parent_rec.inv_auxnum3, parent_rec.inv_loct01,parent_rec.inv_bincode, parent_rec.inv_uoi, parent_rec.prd_catgry,parent_rec.prd_auxnum1, parent_rec.prd_auxnum2, parent_rec.prd_auxnum3,parent_rec.prd_auxnum4, parent_rec.prd_auxnum5, parent_rec.prd_auxnum6,parent_rec.ityp_desc, parent_rec.ityp_auxnum1, parent_rec.ityp_auxnum2,parent_rec.ityp_auxnum3, parent_rec.ityp_auxnum4, parent_rec.ityp_auxnum5,parent_rec.ityp_auxnum6, parent_rec.ityp_dpcorder, sn.node_typeid,child_rec.tra_serial, child_rec.itm_itemno, su.usg_schedule,child_rec.tree_number, parent_rec.igr_descrp, parent_rec.igr_auxnum1, parent_rec.igr_auxnum2,parent_rec.igr_auxnum3, child_rec.itm_serial, child_rec.itm_itemno,child_rec.itm_descrp, child_rec.itm_altdesc, child_rec.itm_lookupnum,child_rec.itm_crsref, cast('' as varchar(550)) v_comments -- cannot retrieve this value; see below.from #tmp_parent parent_rec
left join #tmp_child child_rec
on child_rec.tree_parent = parent_rec.itm_id--3rd 'cursor'left join sys_node sn
on tray_rec.node_id = child_rec.itm_idleft join sys_usage su
on su.usg_treeid = child_rec.tree_id--then use:-- nullif(su.usg_department,' ') usg_department, su.usg_cstctr--to join to whatever goes on inside the proc [get_comments]./*v_comments := rpt_dpc_pkg.get_comments (parent_rec.schid_shser,tray_rec.usg_department,tray_rec.usg_cstctr,child_rec.itm_itemno,0)*/
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant