strsql
I need to know how to get the record count for a particular year. the date field is in YYYYMMDD format. I tried the query select SUBSTR(PLGLDT,0,4) AS YEAR, COUNT(*) AS “RECORD COUNT” FROM BPCSF60/APL GROUP BY SUBSTR(PLGLDT,0,4) ORDER BY SUBSTR(PLGLDT,0,4) DESC. But i am getting error “Argument 2 of substringing function not valid”. Can [...]

Answer Question   |  July 12, 2012  5:26 AM
AS/400, AS/400 error messages, SUBSTR

Select substring from clob-field with sql
How do you copy substring from clob-field with SQL if substring from/to positions are varables? This works OK (RPG free w embedded sql): exec sql select substr(myclobfield, 25, 70)                 INTO :mystring                 FROM MYLIB/MYFILE; [/pre] Whereas this causes a compilation error: from = 25; to    = 70; exec sql select substr(myclobfield, :from, :to)                 [...]

Answer Question   |  March 31, 2012  3:03 PM
Embedded SQL, RPG, SQL, SUBSTR, Variable Length Subnet Mask

UE SUBSTR or RTRIM?
I need to get cities’ name from a format like this: Los Angeles, CA New Orleans, LA etc.. I’ve tried: substr(a.air_location, -4) but that gives me the opposite…”, LA” Any suggestions? Thanks.

Answer Question   |  May 7, 2009  5:21 AM
InStr function, RTRIM, SUBSTR, Substring

oraoledb non working WHERE clause character comparison
Are there WHERE clause character comparisons that do not work when using oraoledb? I have a SQL query that is well formed and works with both Oracle 9 and Oracle 10g client, but will not execute when parsed via oraoledb.

Answer Question   |  December 11, 2008  1:47 PM
Oracle 10g, Oracle 9i, OraOLEDB, OraOLEDB.Oracle, SUBSTR, WHERE statement

Trim Between Characters
How do I Trim the text after a spcific character? (ie. www.website.com#http://www.website.com#) Trimed to www.website.com

Answer Question   |  September 8, 2008  3:55 AM
Strings, SUBSTR, TRIM function

Seeking Help with SQL SUBSTR Function using Host Variables in COBOL
Hello All I am currently on version V5R2 and I am trying to make a COBOL program produce some parsed fields from one large field. I have trying to use host variables for al three arguments in the SUBSTR function. The SQL compiler will take a host variable as argument number one but not for [...]

Answer Question   |  April 28, 2010  8:52 PM
AS/400, COBOL, SQL, SUBSTR, V5R2