>>>select r1.system, r1.rtp_id, trim(r1.st_name) + " / " + trim(r1.termini) + " / " + trim(r1.project) as st_name_a, r1.lead, r1.del1, ; >>> r2.rtp_id, trim(r2.st_name) + " / " + trim(r2.termini) + " / " + trim(r2.project) as st_name_b, r2.lead, r2.del1 as del2, ; >>> r3.rtp_id, trim(r3.st_name) + " / " + trim(r3.termini) + " / " + trim(r3.project) as st_name_c, r3.lead, r3.del1 as del3 ; >>> from rtp_projects r1 ; >>> left join rtp_projects r2 on r1.rtp_id = r2.parentproject ; >>> left join rtp_projects r3 on r2.rtp_id = r3.parentproject ; >>> where (r1.parentproject = 0 or isnull(r1.parentproject)) and ! isnull(r1.st_name) and ! isblank(r1.st_name); >>> order by r1.system, r1.st_name, r2.st_name, r3.st_name>>>
>select system, tip.tip_id, 000.00 as rtp_id, rtp_id as parentproject, trim(st_name) + " / " + trim(termini) + " / " + trim(project) as st_name, lead, .f. as del1 ; >from TIP ; >inner join tipdatabase!proposed on tip.tip_id = proposed.tip_id ; >where proposed.status = "Active" ; >union ; >select system, 000.00 as tip_id, rtp_id, parentproject, trim(st_name) + " / " + trim(termini) + " / " + trim(project) as st_name, lead, del1 ; >from rtp_projects ; >into cursor crsTemp > >select r1.system, r1.rtp_id, r1.st_name as st_name, r1.lead, r1.del1, ; > r2.tip_id, r2.rtp_id, r2.st_name as st_name, r2.lead, r2.del1 as del2, ; > r3.tip_id, r3.rtp_id, r3.st_name as st_name, r3.lead, r3.del1 as del3 ; > from crsTemp r1 ; > left join crsTemp r2 on r1.rtp_id = r2.parentproject ; > left join crsTemp r3 on r2.rtp_id = r3.parentproject ; > where (r1.parentproject = 0 or isnull(r1.parentproject)) and ! isnull(r1.st_name) ; > and ! isblank(r1.st_name); > order by r1.system, r1.st_name, r2.st_name, r3.st_name ; > into cursor crsRTP>My solution of setting this value to 0 in the first SELECT creates duplicate keyids and spoils the second SELECT. I am thinking on a solution to this but so far I'm blank.