Re: The best elegant solution to override 65k rows limit in a ***

aaron.kempf@xxxxxxxxx wrote...
>pulling stuff from multiple tables is EASY
>that is why they make temp tables, derived tables; views.. i mean..
>COME ON

Pulling things from *MULTIPLE* tables is easy. Pulling related
information from the *SAME* table but from different records is
*DIFFICULT*. If I'm wrong, prove it! Show us how to do it elegantly!

You've suggested how to handle moving averages a while ago. Something
like

SELECT (D1.ObsVal+D2.ObsVal+D3.ObsVal+D4.ObsVal+D5.ObsVal)/5 AS MA5PT
FROM (((D AS D1 INNER JOIN D AS D2 ON D1.ObsNum=D2.ObsNum+1)
INNER JOIN D AS D3 ON D2.ObsNum=D3.ObsNum+1)
INNER JOIN D AS D4 ON D3.ObsNum=D4.ObsNum+1)
INNER JOIN D AS D5 ON D4.ObsNum=D5.ObsNum+1;

Maybe that seems clear to you. If there were column headings in A1:B1
and the first record in A2:B2, the Excel formula to calculate this
would be

C6:
=AVERAGE(B2:B6)

Double click the fill handle after entering this formula, and it's
done.

But the real strength of flexible referencing (a la Excel) comes when
you want to vary the number of points in the moving averages. Enter the
formula

C2:
=IF(ROW()>C$1,AVERAGE(OFFSET(B2,1-C$1,0,C$1,1)),"")

and fill it down in column C. Now enter 5 to get 5-point moving
averages, 3 to get 3 point moving averages, 10 to get 10-point moving
averages, etc. You'd need different SELECT queries for each of these,
and you're more deluded than usual if you believe anyone other than
yourself would find the necessary queries anything other than tedious
and repetitious.

>if i want to provide REPORTS to customers I would use the crappiest
>software ever; Adobe Acrobat. I would send them a copy of the REPORT
>instead of a copy of the data.

That's normal practice. It prevents later disputes or MUCHO work trying
to figure out how they derived numbers from raw data. You're just
putting your own ignorance on display again.

>that is my big beef with Excel; it isn't designed for REPORTING but you
>numnuts use it for reporting all the time

No, *YOU* or people *YOU* work with use it for reporting. Others may
also, but I don't.

>you can't export an Excel *** into a REPORT file and email it around.
>what happens when you DONT want people to change your numbers?
>what happens when you DONT want to email a 100mb spread***?

You send PDF files! Simple, ain't it?!

>Databases trump spreadsheets any day of the week.

For doing mindnumbingly repetitive stuff, which is all that you're
likely allowed to do, you're right.

>1) sorting and averaging; that stuff is OBVIOUSLY easier in a database.
>For starters; when you change the SORT ORDER it doesn't break your
>other reports. Do you seriously propose having multiple copies of the
>DATA in a spreadsheets; sorted in different order? LOL

Sorting is purely subjective. Some like you may prefer query predicates
like 'ORDER BY FOO ASC', others would prefer specifying columns via
dialogs. Agreed that Excel's limitation of just 3 sort columns is
absurd.

>2) starting with the 5th.. come on-- gag me with a spoon.. select top 5
>as a subquery.. that is easy.

You'd need to repeatedly subquery the top 5. If you have 100 records,
you have 96 5-point moving averages.

>3) instead of COPYING FORMULAS all over the world; you can have ONE
>FORMULA in ONE PLACE. I mean.. what happens when you change the
>formula?

Except as pointed out above, you could have one Excel formula capable
of generating N-point moving averages for any positive integer N (well,
up to 65535). You need separate queries for each N in rdbms's.

>(hint, it is easier to change a formula in one place than in 20,000
>rows on 20 different worksheets).

Hint: if you know how to use spreadsheets well, it takes *ONE* formula
to do what it'd take thousands of different queries to do.

.