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

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

Not just a perfect world, but in a rational, logical, intelligent world.  Of 
course, that would exclude the in-duh-viduals we work for...

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----

Only in the perfect world :)  In my world we are always running batch
processes underneath to add, update, and delete rows in cases where our
users do not want to do mass data entry for hundreds or thousands of
rows.

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

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.

_________________________________________________________________
Dave vs. Carl: The Insignificant Championship Series.  Who will win? 
http://clk.atdmt.com/MSN/go/msnnkwsp0070000001msn/direct/01/?href=http://davevscarl.spaces.live.com/?icid=T001MSN38C07001


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