SELECT statement

[Guest.Visitor]

Can someone tell me what's wrong with this select statement? SELECT cyymmdd, CAST(SUBSTRING(DIGITS(cyymmdd),4,2) AS NUMERIC(2,0)) * 3 + 1 AS offset, ' JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC' AS monthlist, SUBSTRING(monthlist,offset,3) AS month FROM filename The goal here is to retrieve the month abbreviation corresponding to a date field defined as numeric 7,0. The error message I'm getting is Column OFFSET not in specified tables. I'm still green when it comes to SQL. Please help!

[Guest.Visitor]

Have you tried parens from CAST alll the way to AS offset . bobh

[Guest.Visitor]

Another shot in the dark... How about a case statement in the select??? bobh

[Guest.Visitor]

Alan, You should just move/copy the whole Cast statement where to where you are referencing the offset field. Bill

[Guest.Visitor]

Would you use SELECT ..... , substring(' janfeb...... ', CAST(.....) , 3) as monthnm Just a wild shot... bobh

[Guest.Visitor]

Alan, Using Cast or a Case statement you can accomplish what you want but I would think about two other alternatives. The first would be to create a table of month names/abbreviations and just join in your description. This is the most flexible and moves this from code to table. The second alternative is to create a UDF. You can find an example of a UDF that does exactly what you need at this link: http://www.midrangecomputing.com/mc/article.cfm?titleid=a289&md=20004 If you have the C compiler this will be relatively simple and the easiest to use. If you don't have the C compiler, there is an RPGIV UDF on the downloads page. I would encourage you to look at this to understand how you can extend SQL to meet your business needs. David Morris

[Guest.Visitor]

Finally!! CAST was the culprit. The following select statement does the job. SELECT cyymmdd, SUBSTRING(DIGITS(cyymmdd),6,2) CONCAT SUBSTRING(' JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC',(INTERGER(SU BSTRING(DIGITS(cyymmdd),4,2)) * 3 + 1), 3) CONCAT SUBSTRING(DIGITS(cyymmdd),2,2) AS ddmonyy FROM filename Converts cyymmdd to ddmonyy, if database field content is 1000804 -> 04AUG00