Javier I agree with you. I would sooner do it with one command, that’s I why I started out as “if all else fails”. Sometimes the declare cursor is the only thing that works .
Happy 4th everyone Buddy From: [email protected] <[email protected]> On Behalf Of [email protected] Sent: Saturday, July 3, 2021 4:29 PM To: [email protected] Subject: RE: [RBASE-L] - UPDATE a column WHERE a dot is missing in the end? 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] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > On Behalf Of Buddy Walker Sent: Saturday, July 3, 2021 1:53 PM To: [email protected] <mailto:[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] <mailto:[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] <mailto:[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 <https://groups.google.com/d/msgid/rbase-l/!%26!AAAAAAAAAAAYAAAAAAAAAG1gU%2BA71stIig71BAFnx9iihQAAEAAAAA0VCJ/KWT5Nro6SIEyxOKwBAAAAAA%3D%3D%40vtgonline.com?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/001701d770c5%240ecaa9f0%242c5ffdd0%24%40comcast.net.
