Dennis Cote wrote:
Where did you get the idea there are "fake" columns?
"SELECT 200" returns 200. I'd call that a fake column. What is the
proper name for it?
What I was picturing was something like this: "SELECT (BETWEEN 200 AND
500) as a, a+2" and then get 300 outputs. That would be cool, eh?
So here's the real problem. Sorry about the long explanation, but if
anyone wants to help with the query, that would be great. I have a 2d
sparse matrix in a table where each point has two values, a score and a
diagonal run count. In other words, my table has four columns:
x,y,score,drc. Even though a certain x,y does not show up in the table,
we can still calculate its score value by finding a piece farther along
on the same diagonal using this formula:
score(x,y) = max(score(x+n,y+n)-n*penalty) where n < drc(x+n,y+n) < 256
If that were all we had to calculate, that would be great. Here's how (I
think) that much looks:
SELECT MAX(score - (x - :x) * :penalty ) as score, x, y, (drc - (x -
:x)) as drc FROM resultsGrid WHERE (x-y)=(:x-:y) AND (x >= :x) AND (y >=
:y) AND (x < :x + 256) AND ( y < :y + 256) AND ( drc > x - :x)
So in other words, the x-y ratio is kept the same as the input
point(:x,:y) so that it stays on the same diagonal. That also allows us
to use x-:x as the distance to the point, though y-:y would work just as
well. But now, the tricky part. I have another table: bounds. The bounds
table has an lower bound :x and lower bound :y. I need to get a MAX of
the MAX(scr) on the above query for all (:xCurrent, :yCurrent TO
bounds.yLow OR :xCurrent TO bounds.xLow, :yCurrent). In other words,
it's a backward L shape query where each point in the L is
processed in the query above and the maximum score value of that is
returned.
The part I got confused on was trying to replace the :x,:y in the above
query with (BETWEEN :x AND bounds.xLow OR BETWEEN :y and bounds.yLow).
Thanks again for any help,
Brannon
