Re: [sqr-users] Thinking about trying something - looking for ideas

["the dragon" <ceprn@hotmail.com>]

Good points - I agree.  Unfortunately, like where I work, the idiots have a 
serious fetish for putting crap in through very poorly designed and almost 
untested interfaces, and then we waste too much time cleaning up the mess.  
Evidently, they (idiot users and even more clueless management) feel it's a 
better use of time, resources and money having developers determine the 
problem and then having PS admins and DBAs fix the errors in the database 
through sql scripts rather than taking the time to fix the feeds. /shakes 
head

peace,
clark 'the dragon' willis

PSA: Salary <> Slavery. If you earn a salary, your employer is renting your 
services for 40 hours a week, not purchasing your soul. Your time is the 
only real finite asset that you have, and once used it can never be 
recovered, so don't waste it by giving it away.

I work to live; I don't live to work.

"Time is the coin of your life. It is the only coin you have, and only you 
can determine how it will be spent. Be careful lest you let other people 
spend it for you." -- Carl Sandburg (1878 - 1967)

It is impossible to defeat an ignorant man in argument. -- William G. McAdoo

Religion is regarded by the common people as true, by the wise as false, and 
by the rulers as useful. -- Seneca




----Original Message Follows----

I am not sure about this.

If all the data comes in to the PS database thru its front end then orphan 
rows are impossible to create in first place.

Orphans row exists because of data imported thru back doors and most of the 
time during the initial conversion.

Once you fixed those process you will never have orphans, right?

Just my 2 cents.



----- Original Message ----

John,
This sounds like it would be a very useful sqr.  Could you please pass
on any suggestions you may get.  I'm interested in looking for orphans
too and agree that it is easy to imagine and put together an sqr to do
that, using dynamic sqls selecting from the tools tables, but also agree
that it would take forever to run.
Thanks,
Patti

-----Original Message-----

I have a rather different question today.  I am thinking about writing
an SQR that would look for orphan data anywhere in a PeopleSoft
database, using PeopleTools tables to figure out what to look at rather
than using a static list of tables.  I have an idea or two about how to
go about this... but I wondered if anyone out here had already written
something similar and/or had some suggestions for things to do/avoid.

For example, there will be a lot of dynamic SQL getting processed in
this SQR.  Does that usually stop indexes from being used?  If so, is
there any way I can force indexes to be used?

Also, would it be better to say "select [whatever] from [child] where
not exists ([whatever] in [parent])" or would it be better to write two
select statements, pulling all rows from the child table and trying to
match them with rows in the parent table in a separate select statement?
Either way, I already know this thing is going to take forever to run.

I don't want you fine folks to do my work for me, but I was pretty sure
someone out there would have suggestions on how I can make this a
better, faster tool than the way I would have written it without asking.

-John T.

_________________________________________________________________
Find sales, coupons, and free shipping, all in one place!  MSN Shopping 
Sales & Deals 
http://shopping.msn.com/content/shp/?ctid=198,ptnrid=176,ptnrdata=200639


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users