10 thoughts on “random statements”

In the format string '%E / %N / %L' I replace the %E, %N and %L by a random employee, a random number of employees (from 0 to count) and a random location, respectively.

So I look until there is no more % in the string, and one by one I replace each string containing a % with [begin][substitution][end], where substitution is a value of a reference model where the dimension is a random value (pointing to a random row number).

I apologize for the lack of readability of the substr and instr due to the very short line of the wordpress template, which make proper indenting hard to implement

The point of using model and not REGEXP_REPLACE is to have %E replaced by different values (accidentally it will be the same) within the same string.

Here I avoid using PL/SQL, which is the natural way to translate T-SQL, in order to demonstrate the power of MODEL.

Those who attended my unconference at OpenWorld know what it is about 😎

Reading over the random excuse generator more closely, you folks are looking for the functional equivalent of good, random, data. (Since I make around a gig of that a quarter for RIT… I’m good at this)

The trick to good random data that doesn’t require a lot of PL is the ugly cartesian product.

should work. However, I’ll actually be testing it later today, since my previous post was full of errors…

10,000 bottles of wine, 15,000 cases, and 100,000 orders over 3 years. It makes for a fairly compact data warehouse. (Versus last year’s which, one of our students computed, had a business one third the size of wal-mart’s.

Each loop, I replace the first % (the second SUBSTR) with a value. i check what kind of % it is (the second DECODE), if it is a %E, i then use the reference model (REFERENCE EMP ON) and a random value (DBMS_RANDOM) as a dimension (ROWNUM R).

And then I loop for next value. It means, my str[1]= is executed as many time as you have % in the string.

I hope it puts some light 😎

10’000 bottles over 3 years, it is about 9 bottles a day, you must be very thirsty 😆

On a completely random tangent, I love your human-checking. It’s quite a bit better than a bloody image.

100,000 cases over 3 years Since the date dimension is by weekday only, that’s *quite a few* cases per day. Aren’t all DBAs alcoholics?

With respect to your iterate function, it’s a neat way to do it, but I’m not sure why you chose to do it that way. Was it a “look what I can do” hack? I’d be interested in seeing the explain plan output from this. If your reference tables are only full-table-scanned once, this might be a really shiny way for me to generate data.