----- Original Message -----
From: "Joseph Drake-Brockman" <[EMAIL PROTECTED]>
To: "RBASE-L Mailing List" <[email protected]>
Sent: Saturday, March 15, 2008 7:40 AM
Subject: [RBASE-L] - Re: Stepping thru database
> LARRY:
>
> The assumptions are correct. Your example below to also correct.
>
>
> Assuming that each measurement (10, 20, etc) is in a separate row in a
> table, and assuming that each drill hole has an ID value that is consistent
> across all depth measurements for that hole then yes, this is a classic
> instance in which you'd use a cursor. EG:
>
> HoldID Depth Measurement FilterValue
> 101 10 154
> 101 20 96
> 101 30 100
>
Your original Example:
Index Depth cps
---------- ---------- ----------
1 10 100
2 20 100
3 30 90
4 40 125
5 50 145
6 60 200
7 70 150
8 80 110
9 90 90
10 100 85
11 110 75
12 120 85
Just for my test, I projected the above tabledata to a temp table tTbldata.
Here is the Code for the Select and the update I used on the Temp dataset.
PROJECT TEMP ttbldata FROM tbldata USING *
SET VAR iloop INTEGER = 2
SET VAR imax INTEGER = 12 -- or NumRows -1
WHILE iloop <> imax THEN
SELECT (LAVG((INT((SSUB((LISTOF(cps)),1)))), +
(INT((SSUB((LISTOF(cps)),2)))), +
(INT((SSUB((LISTOF(cps)),3)))))) +
INTO vavg IND vin FROM ttbldata WHERE +
idx BETWEEN (.iloop - 1) AND (.iloop + 1)
UPDATE ttbldata SET cps = .vavg WHERE idx = .iloop
SET VAR iloop = (.iloop + 1)
ENDWHILE
RETURN
Uses the ListOf to put the 3 rows (Row -1, current row , Row + 1) into a text,
then use the SSUB
to parse the elements and apply the INT function on each making them member
elements of the LAVG function.
Since I might of missed a couple of missives (the pop3 server was hosed all day
yesterday), I am unsure of your current proposed table structure, but you can
likely utilize the structure of the select above to mold it to your use.
> commands:
>
> HoldID Depth10Measure Depth20Measure Depth30Measure
> 101 154 96 100
>
> This is done by creating a view with a lot of IFEQ() functions and a GROUP
> BY clause. It can be very advantageous for reporting purposes, but reliese
> on the regularity of the data.
>
>
>
> See my second post on this subject. But it seems that you understand my
> problem. I would appreciate any hints of how to get started. Eventually
> there would be some look up values for other corrections but these would
> onlywork on a row by row basis.
>
>
> Finally, it's possible in standard SQL to write a SELECT statement that can
> get the values before and after the row you're operating on, but it's a bit
> tricky and I'm not sure that R:Base supports the full syntax. If you post
> the actual structure of your table I'll see if I can come up with something.
> That would let you update your filter values with one and only one SQL
> statement.
> --
> Larry
>
>
>
> Thanks Joe DB
>
> Drake-Brockman Geoinfo Pty Ltd
>
> 25 Deague Court
>
> North Perth 6006
>
> Tel / Fax 08 9328 4891
>
> Mob 0427 525952
>
> Email <mailto:[EMAIL PROTECTED]> [EMAIL PROTECTED]
>
>
>
>
--- RBASE-L
================================================
TO POST A MESSAGE TO ALL MEMBERS:
Send a plain text email to [email protected]
(Don't use any of these words as your Subject:
INTRO, SUBSCRIBE, UNSUBSCRIBE, SEARCH,
REMOVE, SUSPEND, RESUME, DIGEST, RESEND, HELP)
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: INTRO
================================================
TO UNSUBSCRIBE:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: UNSUBSCRIBE
================================================
TO SEARCH ARCHIVES:
Send a plain text email to [email protected]
In the message SUBJECT, put just one word: SEARCH-n
(where n is the number of days). In the message body,
place any
text to search for.
================================================
