Friday 10 November 2017

C Program to prrint number in Words

The following C Program converts a number to its correspoiding Words.

    /*   numtoWord.c */
   /*Program to convert a no. having
                                maxim of 10 digitsinto equivalent words*/
   //#include<conio.h>
   #include<stdio.h>
   #include<stdlib.h>
   int ctr=0;
   char str[][20] = {" ","One","Two","Three","Four","Five","Six",
                         "Seven","Eight","Nine","Ten","Eleven","Twelve",
                         "Thirteen","fourteen","fifteen","sixteen","seventeen",
                         "Eighteen","Ninteen","Twenty","Thirty","Fourty","Fifty",                        "Sixty","Seventy","Eighty","Ninty"};
   int print(int,int);
   void numword(long int, int);
   void main()
   {
      long int i=0;
      int l=10,k=10;
      unsigned long int no,n;
      //clrscr();
      printf("Enter a number( having less than 11 digit ):: ");
      scanf("%ld",&n);
      printf("\nThe number in words is:\n\n");
      no=n;
      if(no==0)
      {
          printf("Zero ");
                printf("\n");
         exit(1);
      }
      /*****Count no of  digits*****/
      for(i=n;i>0;i=i/10)
           ctr++;
      /*****Function call*****/
      numword(no,10);
      printf("\n\n");

   }
   /*****Recursive Function converts no: into words*****/
   void numword(long int no, int k)
   {
         static int a,b;
         static int rm1,rm2;
         int rm;
         rm=no%k;
         if(no == 0)
             return;
         else
         {
             numword(no/k, k);
             /*****Checks for the digits*****/
             if(ctr==10)
             {
                 printf("%s   ",str[rm]);
                 printf("Billion   ");
                 ctr=9;
             }
             else if(ctr==9 )
             {
                 rm1 = rm;
                 ctr=8;
             }
             else if(ctr==8 )
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Crore   ");
                 ctr=7;
             }
             else if(ctr==7)
             {
                 rm1=rm;
                 ctr=6;
             }
             else if(ctr==6 )
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Lakh   ");
                 ctr=5;
             }
             else if(ctr==5)
             {
                rm1=rm;
                ctr=4;

             }
             else if(ctr==4)
             {
                 rm1=print(rm1,rm);
                 if(rm1>0)
                     printf("Thousand   ");
                 ctr=3;
             }
             else if(ctr==3)
             {
                if(rm>0)
                {
                   printf("%s   ", str[rm]);
                   printf("Hundred   ");
                }
                ctr=2;
            }
            else if(ctr==2)
            {
                rm1=rm;
                ctr=1;
            }
            else if(ctr==1)
                print(rm1,rm);
         }

   }
   /*****Function to Print equivalent words*****/
   int print(int rm1,int rm)
   {
       int a,b;
       rm1=rm1*10+rm;
       /***Only one digit & hence points to the correct position***/
       if(rm1>0 &&rm1<21)
           printf("%s   ",str[rm1]);
       else
       {
           /*****First & 10th digit to be extracted seperately*****/
           if(rm1>0)
           {
               a=rm1/10;
               b=rm1%10;
               if(a>0)
                   printf("%s   ",str[18+a]);
               if(b>0)
                   printf("%s   ",str[b]);
           }
       }
       /*variable rm1 to be available at the calling portion*/
       return rm1;

   }

 Output
---------- 

 $gcc -o numtoWord numbtoWord.c

 $./numtoWord

 Enter a number( having less than 11 digit ):: 1234567890

 The number in words is:
One   Billion   Twenty   Three   Crore   Fourty   Five   Lakh   Sixty   Seven   Thousand   Eight   Hundred   Ninty
Posted by at No comments:

Tuesday 24 October 2017

Generating JSON Objects in Oracle SQL using JSON Functions

Oracle 12c Release 2 has enhanced the JSON Functionality where we can generate the JSON Objects directly from the table data. The following are the functions available. 
  • JSON_OBJECT

    • The JSON_OBJECT function converts a comma-separated list of key-value pairs into object members within a JSON object. 
    1. SELECT json_object (  
    2.          KEY 'EmployeeId' VALUE empno,  
    3.          KEY 'EmployeeName' VALUE ename  
    4.        ) AS employee  
    5. FROM   emp   
    6. ORDER BY empno;  
    7.   
    8. Output  
    9. ------  
    10. {"EmployeeId":7369,"EmployeeName":"SMITH"}  
    11. {"EmployeeId":7499,"EmployeeName":"ALLEN"}  
    12. {"EmployeeId":7521,"EmployeeName":"WARD"}  
    13. {"EmployeeId":7566,"EmployeeName":"JONES"}  
    14. {"EmployeeId":7654,"EmployeeName":"MARTIN"}  
    15. {"EmployeeId":7698,"EmployeeName":"BLAKE"}  
    16. {"EmployeeId":7782,"EmployeeName":"CLARK"}  
    17. {"EmployeeId":7788,"EmployeeName":"SCOTT"}  
    18. {"EmployeeId":7839,"EmployeeName":"KING"}  
    19. {"EmployeeId":7844,"EmployeeName":"TURNER"}  
  • JSON_OBJECTAGG

    • The JSON_OBJECTAGG aggregate function creates a single JSON object containing a list of object members formed by aggregating a key-value pair from each row.
    1. SELECT JSON_OBJECTAGG(KEY dname VALUE total_count) FROM (  
    2. SELECT d.dname, count(1) total_count from emp e, dept d  
    3. WHERE e.deptno=d.deptno  
    4. group by d.dname);  
    5.   
    6. Output  
    7. ------  
    8. {"ACCOUNTING":3,"RESEARCH":5,"SALES":6}  

  • JSON_ARRAY

    • The JSON_ARRAY function converts a comma-separated list of expressions into a JSON array of JSON values. 

    1. SELECT json_array(  empno, ename, sal,mgr,e.deptno,dname,loc  ) json_array    
    2. FROM emp e, dept d WHERE e.deptno=d.deptno  
    3. ORDER BY empno;    
    4.   
    5. Output  
    6. ------  
    7. [7369,"SMITH",800,7902,20,"RESEARCH","DALLAS"]  
    8. [7499,"ALLEN",1600,7698,30,"SALES","CHICAGO"]  
    9. [7521,"WARD",1250,7698,30,"SALES","CHICAGO"]  
    10. [7566,"JONES",2975,7839,20,"RESEARCH","DALLAS"]  
    11. [7654,"MARTIN",1250,7698,30,"SALES","CHICAGO"]  
    12. [7698,"BLAKE",2850,7839,30,"SALES","CHICAGO"]  
    13. [7782,"CLARK",2450,7839,10,"ACCOUNTING","NEW YORK"]  
    14. [7788,"SCOTT",3000,7566,20,"RESEARCH","DALLAS"]  
    15. [7839,"KING",5000,10,"ACCOUNTING","NEW YORK"]  
    16. [7844,"TURNER",1500,7698,30,"SALES","CHICAGO"] 


    1. SELECT json_array( json_object('EmployeeId' VALUE empno, 'EmployeeName' VALUE ename  
    2. , 'Salary' VALUE sal, 'Manager' VALUE mgr),  
    3. json_object('DepartmentId' VALUE d.deptno ,'DepartmentName' VALUE d.dname,  
    4. 'Location' VALUE d.loc)  
    5. ) json_array  
    6. FROM emp e, dept d   
    7. WHERE e.deptno=d.deptno   
    8. ORDER BY empno;  
    9.   
    10. Output  
    11. ------  
    12. [{"EmployeeId":7369,"EmployeeName":"SMITH","Salary":800,"Manager":7902},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    13. [{"EmployeeId":7499,"EmployeeName":"ALLEN","Salary":1600,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    14. [{"EmployeeId":7521,"EmployeeName":"WARD","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    15. [{"EmployeeId":7566,"EmployeeName":"JONES","Salary":2975,"Manager":7839},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    16. [{"EmployeeId":7654,"EmployeeName":"MARTIN","Salary":1250,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    17. [{"EmployeeId":7698,"EmployeeName":"BLAKE","Salary":2850,"Manager":7839},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  
    18. [{"EmployeeId":7782,"EmployeeName":"CLARK","Salary":2450,"Manager":7839},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    19. [{"EmployeeId":7788,"EmployeeName":"SCOTT","Salary":3000,"Manager":7566},{"DepartmentId":20,"DepartmentName":"RESEARCH","Location":"DALLAS"}]  
    20. [{"EmployeeId":7839,"EmployeeName":"KING","Salary":5000,"Manager":null},{"DepartmentId":10,"DepartmentName":"ACCOUNTING","Location":"NEW YORK"}]  
    21. [{"EmployeeId":7844,"EmployeeName":"TURNER","Salary":1500,"Manager":7698},{"DepartmentId":30,"DepartmentName":"SALES","Location":"CHICAGO"}]  

  • JSON_ARRAYAGG

    • The JSON_ARRAYAGG aggregate function, similar to the LISTAGG function, aggregates an expression from each row into a single JSON array. 

    1. SELECT JSON_ARRAYAGG(e.ename) employee_array  
    2. FROM   emp e  
    3. WHERE  e.deptno = 20;  
    4.   
    5. Output  
    6. ------  
    7. ["SMITH","JONES","SCOTT","ADAMS","FORD"]  

  • Complex Example

    • The Below Query gives a single JSON Array Object contains all the employee & department informations. 
    1. SELECT JSON_ARRAYAGG(json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name   
    7.             ,   KEY 'LastName' VALUE e.last_name     
    8.             ,   KEY 'Email' VALUE e.email    
    9.             ,   KEY 'PhoneNumber' VALUE e.phone_number             
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) ) json FROM departments d  
    14. WHERE ROWNUM<5;  
    15. Output  
    16. ------  
    17. [  
    18.    {  
    19.       "EmployeeDetails": [  
    20.          {  
    21.             "DepartmentInfo": {  
    22.                "DepartmentId": 10,  
    23.                "DepartmentName": "Administration",  
    24.                "EmployeeInfo": [  
    25.                   {  
    26.                      "EmployeeId": 200,  
    27.                      "FirstName": "Jennifer",  
    28.                      "LastName": "Whalen",  
    29.                      "Email": "JWHALEN",  
    30.                      "PhoneNumber": "515.123.4444"  
    31.                   }  
    32.                ]  
    33.             }  
    34.          }  
    35.       ]  
    36.    },  
    37.    {  
    38.       "EmployeeDetails": [  
    39.          {  
    40.             "DepartmentInfo": {  
    41.                "DepartmentId": 20,  
    42.                "DepartmentName": "Marketing",  
    43.                "EmployeeInfo": [  
    44.                   {  
    45.                      "EmployeeId": 201,  
    46.                      "FirstName": "Michael",  
    47.                      "LastName": "Hartstein",  
    48.                      "Email": "MHARTSTE",  
    49.                      "PhoneNumber": "515.123.5555"  
    50.                   },  
    51.                   {  
    52.                      "EmployeeId": 202,  
    53.                      "FirstName": "Pat",  
    54.                      "LastName": "Fay",  
    55.                      "Email": "PFAY",  
    56.                      "PhoneNumber": "603.123.6666"  
    57.                   }  
    58.                ]  
    59.             }  
    60.          }  
    61.       ]  
    62.    },  
    63.    {  
    64.       "EmployeeDetails": [  
    65.          {  
    66.             "DepartmentInfo": {  
    67.                "DepartmentId": 30,  
    68.                "DepartmentName": "Purchasing",  
    69.                "EmployeeInfo": [  
    70.                   {  
    71.                      "EmployeeId": 114,  
    72.                      "FirstName": "Den",  
    73.                      "LastName": "Raphaely",  
    74.                      "Email": "DRAPHEAL",  
    75.                      "PhoneNumber": "515.127.4561"  
    76.                   },  
    77.                   {  
    78.                      "EmployeeId": 115,  
    79.                      "FirstName": "Alexander",  
    80.                      "LastName": "Khoo",  
    81.                      "Email": "AKHOO",  
    82.                      "PhoneNumber": "515.127.4562"  
    83.                   },  
    84.                   {  
    85.                      "EmployeeId": 116,  
    86.                      "FirstName": "Shelli",  
    87.                      "LastName": "Baida",  
    88.                      "Email": "SBAIDA",  
    89.                      "PhoneNumber": "515.127.4563"  
    90.                   },  
    91.                   {  
    92.                      "EmployeeId": 117,  
    93.                      "FirstName": "Sigal",  
    94.                      "LastName": "Tobias",  
    95.                      "Email": "STOBIAS",  
    96.                      "PhoneNumber": "515.127.4564"  
    97.                   },  
    98.                   {  
    99.                      "EmployeeId": 118,  
    100.                      "FirstName": "Guy",  
    101.                      "LastName": "Himuro",  
    102.                      "Email": "GHIMURO",  
    103.                      "PhoneNumber": "515.127.4565"  
    104.                   },  
    105.                   {  
    106.                      "EmployeeId": 119,  
    107.                      "FirstName": "Karen",  
    108.                      "LastName": "Colmenares",  
    109.                      "Email": "KCOLMENA",  
    110.                      "PhoneNumber": "515.127.4566"  
    111.                   }  
    112.                ]  
    113.             }  
    114.          }  
    115.       ]  
    116.    },  
    117.    {  
    118.       "EmployeeDetails": [  
    119.          {  
    120.             "DepartmentInfo": {  
    121.                "DepartmentId": 40,  
    122.                "DepartmentName": "Human Resources",  
    123.                "EmployeeInfo": [  
    124.                   {  
    125.                      "EmployeeId": 203,  
    126.                      "FirstName": "Susan",  
    127.                      "LastName": "Mavris",  
    128.                      "Email": "SMAVRIS",  
    129.                      "PhoneNumber": "515.123.7777"  
    130.                   }  
    131.                ]  
    132.             }  
    133.          }  
    134.       ]  
    135.    }  
    136. ]  

    Another Example shown below is an SQL query which will return the Employee Details JSON Objects which contains a nested array object called EmployeeInfo. It contains all the employees for a particular department. 
    1. SELECT json_object(KEY 'EmployeeDetails' VALUE   
    2.   json_array(json_object(KEY 'DepartmentInfo' VALUE json_object(  
    3.   KEY 'DepartmentId' VALUE department_id, KEY 'DepartmentName'   
    4.   VALUE department_name,KEY 'EmployeeInfo' VALUE   
    5.   (SELECT json_arrayagg(json_object(KEY 'EmployeeId' VALUE e.employee_id,  
    6.                 KEY 'FirstName' VALUE e.first_name  
    7.             ,   KEY 'LastName' VALUE e.last_name       
    8.             ,   KEY 'Email' VALUE e.email   
    9.             ,   KEY 'PhoneNumber' VALUE phone_number               
    10.                 ))  
    11.                 FROM employees e   
    12.                 WHERE e.department_id=d.department_id)  
    13.   )))) json FROM departments d;  
    14.   
    15. Output  
    16. ------  
    17. {  
    18.    "EmployeeDetails": [  
    19.       {  
    20.          "DepartmentInfo": {  
    21.             "DepartmentId": 20,  
    22.             "DepartmentName": "Marketing",  
    23.             "EmployeeInfo": [  
    24.                {  
    25.                   "EmployeeId": 201,  
    26.                   "FirstName": "Michael",  
    27.                   "LastName": "Hartstein",  
    28.                   "Email": "MHARTSTE",  
    29.                   "PhoneNumber": "515.123.5555"  
    30.                },  
    31.                {  
    32.                   "EmployeeId": 202,  
    33.                   "FirstName": "Pat",  
    34.                   "LastName": "Fay",  
    35.                   "Email": "PFAY",  
    36.                   "PhoneNumber": "603.123.6666"  
    37.                }  
    38.             ]  
    39.          }  
    40.       }  
    41.    ]  
    42. }  
















    Posted by at No comments:
    Subscribe to: Posts (Atom)