Buddy,
I am using SQL statements more often now where I used Cursors before. Using
your code, you can combine getting the last character and string length to
create the condition.
SET VAR vColLength = (SLEN(.vColValue))
SET VAR vColLastChar = (SGET(.vColValue,1,.vColLength))
UPDATE johantab SET kalla2 = (kalla2 + '.') WHERE (SGET(kalla2,1,
(SLEN(kalla2)))) <> ‘.’
*Might need to check parentheses 😊
Having said that, Karen’s solution:
UPDATE johantab SET kalla2 = (kalla2 + '.') WHERE kalla2 NOT LIKE '%.'
is more elegant/simpler.
It would be interesting to see if one runs faster than the other and I guess it
would depend on the size of column kalla2, but with a one time update of under
8,000 records, it is really not relevant.
I am currently working with a table with 170,000+ records where the process
requires a number of updates and the process is run multiple time, so execution
time is definitely a concern.
Javier,
Javier Valencia, PE
913-915-3137
From: [email protected] <[email protected]> On Behalf Of Buddy
Walker
Sent: Saturday, July 3, 2021 1:53 PM
To: [email protected]
Subject: RE: [RBASE-L] - UPDATE a column WHERE a dot is missing in the end?
Bo
If all else fails then try looping through table with declare cursor
I’m assuming there could be a dot anywhere in the column but you also need one
at the end.
Buddy
---Code Starts Here
SET VAR vColValue TEXT
SET VAR vColLength INTEGER
SET VAR vDot = ('.')
DECLARE C1 CURSOR FOR SELECT kalla2 +
FROM SomeTable
OPEN c1
FETCH c1 INTO +
vColValue INDICATOR iv1
WHILE SQLCODE <> 100 THEN
SET VAR vColLength = (SLEN(.vColValue))
SET VAR vColLastChar = (SGET(.vColValue,1,.vColLength))
IF vColLastChar <> .vDot THEN
SET VAR vColValue = (.vColValue + .vDot)
UPDATE SomeTable SET kalla2 = .vColValue WHERE CURRENT OF C1
ENDIF
SET VAR vColLength = 0
SET VAR vColLastChar = NULL
SET VAR vColValue = NULL
FETCH c1 INTO +
vColValue INDICATOR iv1
ENDWHILE
DROP CURSOR c1
CLE VAR vColValue
RETURN--Code Ends Here
From: [email protected] <mailto:[email protected]>
<[email protected] <mailto:[email protected]> > On Behalf Of Bo
Franzén
Sent: Saturday, July 3, 2021 10:43 AM
To: R:BASE <[email protected] <mailto:[email protected]> >
Subject: [RBASE-L] - UPDATE a column WHERE a dot is missing in the end?
Dear R:BASE!
I have a price database (Middle Ages) that is going to be printed in due time.
A column, källa2, with the (often printed) source in a TEXT FORMAT often lacks
a dot [.] in the end. How to UPDATE all rows that lack a dot? I have tried:
R>UPDATE johantab SET källa2 = LAST LIKE '%.' WHERE källa2 LAST NOT LIKE '%.'
-ERROR- Column källa2 must be a valid TEXT. ( 122)
Strange, since källa2 is a TEXT column. Operators change over time. Probably
I'm behind. One way would be to add a dot to all rows in order to SRPL '..'
with '.' But that did not work either:
R>UPDATE johantab SET källa2 = LAST LIKE '%.'
-ERROR- Column källa2 must be a valid TEXT. ( 122)
To UPDATE 7733 columns by hand seems like a terrible waste of time. But, as
said, I am probably behind.
Bo Franzén
Department of Economic History
Stockholm University
R>COMPUTE ALL tran_id FROM johantab WHERE källa2 LAST NOT LIKE '%.'
tran_id Count = 7733
Rows = 8008
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected]
<mailto:[email protected]> .
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/28791a1702b0408da38541b47ad63903%40ekohist.su.se
<https://groups.google.com/d/msgid/rbase-l/28791a1702b0408da38541b47ad63903%40ekohist.su.se?utm_medium=email&utm_source=footer>
.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/002201d7703c%24a7a228d0%24f6e67a70%24%40comcast.net
<https://groups.google.com/d/msgid/rbase-l/002201d7703c%24a7a228d0%24f6e67a70%24%40comcast.net?utm_medium=email&utm_source=footer>
.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups
"RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAA0VCJ/KWT5Nro6SIEyxOKwBAAAAAA%3D%3D%40vtgonline.com.
