how to convert Month numbers into Month Names

[#1]

Hi ,

My table has a field called Payment Month which is in number. I wanted to have it in Month Names. I wrote a SQL Script in my load using case function and got the Month Names when i tried to get the Names in the List box it is not sorted properly. It is not in the proper Order.

How can i get this.Please help.

Thanks.

[#2]

SpendDetail:

SQL

SELECT
T1.SingleCountID,

T2.Month,

T2.Year,

T2.Quarter,

T2.Spend,

T2.Fiscal_Year,

T2.WBSElement,

T2.AP_CostCenter_ID,

T2.L1_Name,

T2.L2_Name,

T2.L3_Name,

T2.L4_Name,

T1.DCfromList,

T2.DCfromSpend,

T2.VendorSpend_ID

FROM

(SELECT Vendor_ID AS
VendorSpend_ID,

CAST(Vendor_ID

AS VARCHAR(20)) + diversitycategory_name AS
SingleCountID,

diversitycategory_name

AS
DCfromList

FROM

cvmsa.RptAllDCSupplierPDC

GROUP

BY
Vendor_ID,

CAST(Vendor_ID

AS VARCHAR(20)) +
diversitycategory_name,

diversitycategory_name)

AS
T1

LEFT

JOIN (SELECT DS.Vendor_ID AS
VendorSpend_ID,

payment_month

as
Month,

DS.Payment_Year

AS
Year,

'Q'

+ RIGHT(RTRIM(DS.PaymentQuarter), 1) AS
Quarter,

DS.Payment_Amount

AS
Spend,

DS.diversitycategory

AS
DCfromSpend,

CASE

WHEN

DS.Payment_Month >= 9 THEN ( DS.Payment_Year + 1
)

ELSE

DS.Payment_Year

END

AS
Fiscal_Year,

DS.WBSElement,

DS.AP_CostCenter_ID,

OU.L1_Name,

OU.L2_Name,

OU.L3_Name,

OU.L4_Name

FROM

cvmsa.View_DiverseSpend AS
DS

LEFT

OUTER JOIN cvmsa.OrgUnitHierarchy AS
OU

ON

DS.AP_CostCenter_ID =
OU.External_ID

WHERE

( OU.Delete_flag IS NULL )) AS
T2

ON

T1.VendorSpend_ID =
T2.VendorSpend_ID;

MonthMap:
MAPPING LOAD * INLINE [ Month, MonthName
1, Jan
2, Feb
3, Mar
4, Apr
5, May
6, Jun
7, Jul
8, Aug
9, Sep
10, Oct
11, Nov
12, Dec];

I have copied my Script.My Month I am taking it as the result of the date part function in SQL. It returns an integer value.

Can any of u help.

Thanks.

[johnw]

This is, for example, what Héctor was suggesting:

SpendDetail:
LOAD
T1.SingleCountID,
month(makedate(2010,T2.Month)) as T2.Month
...
;
SQL SELECT
T1.SingleCountID,
T2.Month,

If that doesn't work, perhaps you could explain what you mean by "doesn't work". No values? Wrong values? Numbers? Nulls?

As for the mapping load, you have to build a mapping load first, then use it with an applymap() or map using. It does no good building it at the end of the script, where it will simply be built and then immediately discarded. But there's no need for that sort of complexity anyway, which is why I'm focusing on Héctor's solution.

Also, try to fix the formatting of the code before pasting into the forum, since it's VERY hard to read as is. You could cut and paste into Notepad first. I think the other approach is Settings -> User Preferences -> Editor -> Copy as RTF -> remove the checkmark. The forum standard is then also to include [ code ] [ /code ] marks around your code, but I think it's easier to read if you just change the font to Courier New. I'm not a fan of the code boxes. Whatever you prefer, of course.