Hi guys -    I have a Data element A015478 I need to remove the zero.   i figured i can do  (SUBSTRN(dataelement,1,1)) to get the A separate. (SUBSTRN(dataelement,3,5)) to get the numbers after the zero and concatenate them together.  (SUBSTRN(dataelement,1,1)) ||(SUBSTRN(dataelement,3,5))  hoping to get A15478 the issue is that sometimes the data in that field is A01457 sometimes A02457896  or even A0210501 (a second zero that i need to keep) -- the length of the number portion after the zero is variable. i was thinking i could use a length statement in that last part... and thinking i may need a put or input to change the type --- but keep getting errors - can anyone help?   What i tried, and the resulting error: (SUBSTRN(dataelement,1,1))|| (SUBSTRN(dataelement,3,(length((dataelement)-2)))) ERROR: Expression using subtraction (-) requires numeric types. ERROR: Function LENGTH requires a character expression as argument 1. ERROR: Function SUBSTRN requires a numeric expression as argument 3. Thanks! ... View more

i've been reading through the other posts/questions about CRLF and i can't seem to get it to go without error.   Situation: i have one data element, one Entry that has a CRLF at the end of the data.  Creating a pipe delimited txt file and that CRLF is making things not work.   When i do this: SELECT DISTINCT TRANWARD(B.MED_SCHL_COND_NM,'0D0A'x,'') AS MED_SCHOOL_NM, B.MED_SCHL_LOC AS MED_SCHOOL_LOC FROM GGDD.MED_SCHL B i get this error: ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "X" was found following "". Expected tokens may include: "AT MICROSECONDS MICROSECOND SECONDS SECOND MINUTES MINUTE HOURS". SQLSTATE=42601 if i try this: SELECT DISTINCT prxchange('s/(\n|\r)//', -1, B.MED_SCHL_COND_NM) AS MED_SCHOOL_NM, B.MED_SCHL_LOC AS MED_SCHOOL_LOC FROM GGDD.MED_SCHL B I get this error: ERROR: CLI describe error: [IBM][CLI Driver][DB2] SQL0440N No authorized routine named "PRXCHANGE" of type "" having compatible arguments was found. SQLSTATE=42884   ... View more