Results 1 to 10 of 10
  1. #1
    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 15:11.

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

    Talking


    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)
    /


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    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. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    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;


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    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. #6
    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. #7
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    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)
    Hexadecimal is basis 16 (0-F)
    ...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.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  8. #8
    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. #9
    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. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


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

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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