View Full Version : SELECT statement
01-01-1995, 02:00 AM
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!
08-04-2000, 01:02 PM
Have you tried parens from CAST alll the way to AS offset . bobh
08-04-2000, 01:05 PM
Another shot in the dark... How about a case statement in the select??? bobh
08-04-2000, 01:29 PM
Alan, You should just move/copy the whole Cast statement where to where you are referencing the offset field. Bill
08-04-2000, 02:05 PM
Would you use SELECT ..... , substring(' janfeb...... ', CAST(.....) , 3) as monthnm Just a wild shot... bobh
08-04-2000, 02:41 PM
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
08-04-2000, 02:53 PM
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
Powered by vBulletin® Version 4.1.5 Copyright © 2013 vBulletin Solutions, Inc. All rights reserved.