# Thread: Trying to construct code to provide chinese year

1. Registered User
Join Date
Oct 2004
Location
Oklahoma City OK -
Posts
122

## Unanswered: Trying to construct code to provide chinese year

Just for fun,

I am trying to construct or find code to provide chinese year. This information is available in a matrix format but I am trying to find or develop an equation to calculate the chinese year 1 thru 12 1900 = 1 , 1901 = 2 1902 = 3 and so on.
Code:
```1	Rat
2	Ox
3	Tiger
4	Rabbit
5	Dragon
6	Snake
7	Horse
8	Sheep ( Goat )
9	Monkey
10	Rooster
11	Dog
12	Pig```
I need to convert the input year to a value 1 thru 12 then use decode, or another better method?

Code:
```SELECT DECODE(DECODE(:YRIN,
1900,1,
1901,2,
1902,3,
1903,4,
1904,5,
1905,6,
1906,7,
1907,8,
1908,9,
1909,10,
1910,11,
1911,12,
1912,1,
1913,2,
1914,3,
1915,4,
1916,5,
1917,6,
1918,7
),
1,'RAT',
2,'OX',
3,'TIGER',
4,'RABBIT',
5,'DRAGON',
6,'SNAKE',
7,'HORSE',
8,'SHEEP/GOAT' ,
9,'MONKEY',
10,'ROOSTER',
11,'DOG',
12,'PIG')YR
FROM dual;```
Last edited by wrwelden; 12-29-06 at 14:11.

2. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

Try something like this:
Code:
```SELECT YRIN,
DECODE((MOD(YRIN-4,12)+1),
1,'RAT',
2,'OX',
3,'TIGER',
4,'RABBIT',
5,'DRAGON',
6,'SNAKE',
7,'HORSE',
8,'SHEEP/GOAT' ,
9,'MONKEY',
10,'ROOSTER',
11,'DOG',
12,'PIG')YR
FROM
(select level+1899 yrin from dual connect by level < 13)
/```

3. Registered User
Join Date
Oct 2004
Location
Oklahoma City OK -
Posts
122
Thank you very much. Using what you provided, I created the following function.

Code:
```CREATE OR REPLACE FUNCTION Chinese_Yr
--FUNCTION Chinese_Yr
(
INPUT IN VARCHAR2
)
RETURN VARCHAR2 IS
-- THIS FUNCTION WILL DETERMINE CHINESE YEAR ANIMAL

VALUEOUT    VARCHAR2(12);

BEGIN
--**

SELECT
DECODE((MOD(yrin-4,12)+1),
1,'RAT',
2,'OX',
3,'TIGER',
4,'RABBIT',
5,'DRAGON',
6,'SNAKE',
7,'HORSE',
8,'SHEEP/GOAT' ,
9,'MONKEY',
10,'ROOSTER',
11,'DOG',
12,'PIG')YR
INTO VALUEOUT
FROM
(SELECT LEVEL+ (INPUT-1 )yrin FROM dual CONNECT BY LEVEL < 2);

--**
RETURN VALUEOUT;
--**
END Chinese_Yr;```

4. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

A bit less convoluted would be:
Code:
```CREATE OR REPLACE FUNCTION Chinese_Yr
--FUNCTION Chinese_Yr
(
INPUT IN VARCHAR2
)
RETURN VARCHAR2 IS
-- THIS FUNCTION WILL DETERMINE CHINESE YEAR ANIMAL

VALUEOUT    VARCHAR2(12);

BEGIN
--**

SELECT
DECODE((MOD(INPUT-4,12)+1),
1,'RAT',
2,'OX',
3,'TIGER',
4,'RABBIT',
5,'DRAGON',
6,'SNAKE',
7,'HORSE',
8,'SHEEP/GOAT' ,
9,'MONKEY',
10,'ROOSTER',
11,'DOG',
12,'PIG')YR
INTO VALUEOUT
FROM DUAL;

--**
RETURN VALUEOUT;
--**
END Chinese_Yr;```

5. Registered User
Join Date
Oct 2004
Location
Oklahoma City OK -
Posts
122
LKBrwn_DBA,

Thank you for pointing out the convolution.

This has been a valuable learning experience.

6. Registered User
Join Date
Oct 2004
Location
Oklahoma City OK -
Posts
122
I have used mod before, in an easter date calculation, and am so happy it works, but why?

I understand the how as it relates to this post:

--**
-- mod ,In Oracle/PLSQL, the mod function returns the remainder of m divided by n , mod(m,n ).
-- mod(15,4 ) would return 3 -- 15/4= 3.75 then, 0.75 * 4 = 3
-- (MOD(INPUT-4,12)+1), (MOD(2007-4,12)+1) should return 12
-- (MOD(INPUT-4,12)+1), (MOD(2003,12)+1) would return 12
-- (MOD(INPUT-4,12)+1), (((166.9167-FLOOR(166.9167,1))*12)+1) would return 12 -- (Floor gives the fraction portion)
-- (MOD(INPUT-4,12)+1), (0.9167)*12)+1) would return 12
-- (MOD(INPUT-4,12)+1), ((11)+1) would return 12

but why, or faced with the problem of how to make 2007 translate to 12 , how did you know mod was the way to go?

7. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

What do you remember from school about the numbering systems?

Binary is basis 2 (0,1)
Octal is basis 8 (0-7)
Decimal is basis 10 (0-9)
...etc...
To compute/convert to/from any of these number system you may have to use the MOD(n,basis) function.

Chinese calendar is duodecimal (basis 12) (0-11) plus 1.

8. Registered User
Join Date
Oct 2004
Location
Oklahoma City OK -
Posts
122
I understand various number systems to a point, but the specific question I have is , with respect to the following equation:

(MOD(INPUT-4,12)+1), ((11)+1) would return 12

aside from the obvious answer of, because that is what made it work,

how were the -4 and +1 detemined as the proper values to reach the goal?

9. Registered User
Join Date
May 2004
Location
Dominican Republic
Posts
721
The -4 is *not* needed at all, but you would need to re-construte the whole list to match the especific mod( year, 12 ) value. For example, instead of 1 = 'RAT' it will be 5 = 'RAT' and so on. I *believe* that's why he used -4, so to match exactly as your list.

By the way, I was taught in school that mod is the "residual modificator" (or modificador de residuo for those of you who can read spanish).

10. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713

In spanish it is also refered to as "modulo" (from latin modulus).

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•