CTAS with function

user_9419867 0
Viewed 307

I have a table with the following columns: age(VARCHAR2), bothsexes(NUMBER), male(NUMBER), female(NUMBER). I am trying to write a CTAS statement using a stored function that will return a percentage (num1/num2). For the life of me, I cannot figure this out. essentially I want the CTAS to give me a second table with age(VARCHAR2) and percent(number), with each record from table 1 age being copied with the appropriate calculation for the percent.

This is what I have so far.

Here is my function.

CREATE OR REPLACE FUNCTION CALCULATE_PERCENT(NUM1 IN NUMBER,NUM2 IN NUMBER)
  RETURN NUMBER
    IS TOTAL NUMBER;
    BEGIN
      SELECT (NUM1/NUM2) INTO TOTAL FROM DUAL;
      RETURN TOTAL;
    END;

Here is my CTAS so far.

CREATE TABLE ARMalePercentage (AGE PRIMARY KEY) 
AS SELECT A.AGE, calculate_percent(num1=>A.MALE,num2=>A.BOTHSEXES) 
 "Percent Male Population" from arkansas2010census a;

I am new to SQL and have no clue what I'm doing wrong, so any help is appreciated.

Solution:

CREATE TABLE ARMalePercentage (AGE PRIMARY KEY, "Percent Male Population") AS SELECT A.AGE, (select calculate_percent(a.MALE,a.BOTH_SEXES) from dual) "Percent Male Population" FROM arkansas2010census a;

oracle11g plsql oracle sql
2 Answers

There's no need to create a Table just to see the calculated columns. Rather create a VIEW.

Your function can be simplified.

CREATE OR REPLACE FUNCTION calculate_percent (
     num1   IN NUMBER,
     num2   IN NUMBER
) RETURN NUMBER DETERMINISTIC --marks a function that returns predictable results
     IS
BEGIN
 RETURN ROUND( ( num1 / num2 ) * 100.00,2); --Optional rounding to 2 decimal places
END;
/

View

CREATE OR REPLACE VIEW armalepercentage AS
     SELECT a.age,
            calculate_percent(a.male,a.bothsexes) AS  "Percent Male Population"
     FROM arkansas2010census a;

Or simply add a VIRTUAL COLUMN to the existing table

ALTER TABLE arkansas2010census ADD (
     PCT_MALE NUMBER GENERATED ALWAYS AS ( calculate_percent(male,both_sexes) )
);
user_79985910

You are creating a table ARMalePercentage with one column, named AGE, that has a primary key constraint, and trying to populate it with a query that returns two columns, A.AGE and the results of your function.

I think you want

CREATE TABLE ARMalePercentage (AGE PRIMARY KEY, PCT_MALE) 
   AS SELECT A.AGE, 
      calculate_percent(num1=>A.MALE,num2=>A.BOTHSEXES) "Percent Male Population"
      from arkansas2010census a;

You can just try running the SELECT part of the statement to see you are getting back two columns.

user_41782620
Related