Show Posts

This section allows you to view all posts made by this member. Note that you can only see posts made in areas you currently have access to.


Messages - oratune

Pages: [1]
1
PSOUG / Re: analytical function question
« on: May 20, 2009, 01:43:21 PM »
Hi all,
I am trying to find out if I can use the analytical functions to perform the following:
I have a table that has two columns
---------------------------
customer_id   | partner_id  |
---------------------------
Customer_id has the unique index.
Hence one vendor can have more than one customer.

If I need to have the data for the distribution or histogram, I can easily do it with the simple SQL code:

select total_customers, count(total_customers) from (
      select count(vendor_id) total_customers, partner_id from table_a group by parnter_id_id order by total_customers desc )
group by total_customers

And I will be able to do have the distribution of customers/ per partners.

Is that possible to do the same using Oracle analytical functions?

Also, I need to know if I need a scale of 5, not 1?  :)   e.g. how many partners have 1-5 contacts, 6-10 contacts etc...

Any help would be greatly appreciated!!!
thanks,
alex

SQL>
SQL> --
SQL> -- Create table
SQL> --
SQL> create table table_a(
  2          customer_id  varchar2(16),
  3          partner_id varchar2(16)
  4  );

Table created.

SQL>
SQL> --
SQL> -- Create unique index
SQL> --
SQL> create unique index table_a_cust
  2  on table_a(customer_id);

Index created.

SQL>
SQL> --
SQL> -- Populate table
SQL> --
SQL> begin
  2          for i in 1..123456 loop
  3                  insert into table_a
  4                  values('Cust_'||i, 'Partner_'||mod(i, 9));
  5          end loop;
  6
  7          commit;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL>
SQL> --
SQL> -- Your original query
SQL> --
SQL> select total_customers, count(total_customers) from (
  2                select count(customer_id) total_customers, partner_id from table_a group by partner_id order by total_customers desc )
  3  group by total_customers;

TOTAL_CUSTOMERS COUNT(TOTAL_CUSTOMERS)
--------------- ----------------------
          13717                      6
          13718                      3

SQL>
SQL> --
SQL> -- Your query modified to remove useless columns
SQL> --
SQL> select total_customers, count(total_customers) from (
  2                select count(customer_id) total_customers from table_a group by partner_id order by total_customers desc )
  3  group by total_customers;

TOTAL_CUSTOMERS COUNT(TOTAL_CUSTOMERS)
--------------- ----------------------
          13717                      6
          13718                      3

SQL>
SQL> --
SQL> -- One version of analytics
SQL> --
SQL> with ttlcst as(
  2          select count(customer_id) total_customers
  3          from table_a
  4          group by partner_id
  5  )
  6  select distinct total_customers, count(*) over (partition by total_customers) cust_ct_by_vend
  7  from ttlcst
  8  order by total_customers desc;

TOTAL_CUSTOMERS CUST_CT_BY_VEND
--------------- ---------------
          13718               3
          13717               6

SQL>
SQL> --
SQL> -- Another take on analytics
SQL> --
SQL> select distinct count(customer_id) total_customers, count(*) over (partition by count(customer_id)) cust_ct_by_vend
  2  from table_a
  3  group by partner_id
  4  order by total_customers desc;

TOTAL_CUSTOMERS CUST_CT_BY_VEND
--------------- ---------------
          13718               3
          13717               6

SQL>


David Fitzjarrell

2
PSOUG / Re: Error message PLS 00103
« on: May 11, 2009, 01:12:12 PM »
Hi I am trying to write a store procedure that contains 2 select statements and I want the union of that to be the output. My select statements work very well at the command line. But when I use it in a store procedure, I get errors such as the above or errors where the entire SQL statement is ignored after the SELECT statement. Can somebody point if there is a different way to code select statements in a SP.
Thanks


Without seeing your code it's difficult to provide a solution.


David Fitzjarrell

3
It works for you, and that's the important aspect.  It is, though, going through a bit more work than it should (I think) since you can select a sequence value from any table and p_ename is a string literal which can also be selected from any table.  Since your embedded select returns only one row the solution I posted seems, to me, to be a better possibility.  I prefer to use either an INSERT ... SELECT ... or an INSERT ... VALUES ... and not combine the two.

Of course we're all different and your choices may not be mine.  Again, it works, so why change it?



David Fitzjarrell

4
Thanks again David,
Since I am a newbie, looks like my questions evolve with answers. Last one I have is
1. how do we manage to add a new employee to either an existing or a totally new department at the same time.  All we need to do is to supply the emp_name and dept_name. The values should get inserted by either creating a new deptName(and deptID)or by using an existing dept name. I assume we need the earlier newdept trigger, but the combination may conflict if deptname already exists.
Thanks


It won't if you understand how PL/SQL functions:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create sequence newdeptseq
  2  start with 10 increment by 10 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> create or replace trigger newdeptid_trg
  2  before insert on newdept
  3  for each row
  4  begin
  5          select newdeptseq.nextval
  6          into :new.deptid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          select deptid
  8          into dept_id
  9          from newdept
 10          where deptname = :new.deptname;
 11
 12          insert into newemp
 13          (empname, deptid)
 14          values(:new.empname, dept_id);
 15
 16  exception
 17          when no_data_found then
 18                  insert into newdept
 19                  (deptname)
 20                  values(:new.deptname)
 21                  returning deptid into dept_id;
 22
 23                  insert into newemp
 24                  (empname, deptid)
 25                  values(:new.empname, dept_id);
 26
 27  end;
 28  /

Trigger created.

SQL>
SQL> CREATE or REPLACE procedure INPUT_RECORDS(
  2          p_ename in varchar2,
  3          p_dname in varchar2)
  4  IS
  5  BEGIN
  6          INSERT into newemp(empid, empname, deptID)
  7           select newEmpSeq.nextval, p_ename, deptID
  8           FROM
  9           newdept where deptname = p_dname;
 10          commit;
 11  end;
 12  /

Procedure created.

SQL>
SQL> insert into newdept
  2  (deptname)
  3  values('Marketing');

1 row created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3          INSERT_TO_VIEW('Zappo', 'Finance');
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL>
SQL> Begin
  2          INPUT_RECORDS('Clyde', 'Marketing');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales
Zappo                                    Finance
Clyde                                    Marketing

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             20
         2 Zappo                                            30
         4 Clyde                                            10

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Marketing
        20 Sales
        30 Finance

SQL>

Notice how the processing shifted in the INSTEAD OF trigger when the department wasn't found in the dept table.


David Fitzjarrell

5
David,
Thanks very much. I now have a clear idea. 
One thing I wanted to clarify is that  do we STILL have to have a trigger for  dept table in case I want to conduct NEW INSERT operations on EMP in  an existing department? All I want to do is to be able to add new employees to an existing dept.

Thanks once again


No, that trigger isn't necessary if you're expecting to add new employees to an existing department, although you do need to supply the deptid when adding new departments:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          select deptid
  8          into dept_id
  9          from newdept
 10          where deptname = :new.deptname;
 11
 12          insert into newemp
 13          (empname, deptid)
 14          values(:new.empname, dept_id);
 15
 16  exception
 17          when no_data_found then
 18                  raise_application_error(20001, 'Department does not exist', true);
 19  end;
 20  /

Trigger created.

SQL>
SQL> CREATE or REPLACE procedure INPUT_RECORDS(
  2          p_ename in varchar2,
  3          p_dname in varchar2)
  4  IS
  5  BEGIN
  6          INSERT into newemp(empid, empname, deptID)
  7           select newEmpSeq.nextval, p_ename, deptID
  8           FROM
  9           newdept where deptname = p_dname;
 10          commit;
 11  end;
 12  /

Procedure created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> insert into newdept
  2  values (10, 'Sales');

1 row created.

SQL>
SQL> insert into newdept
  2  values (20, 'Marketing');

1 row created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> Begin
  2          INPUT_RECORDS('Clyde', 'Marketing');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales
Clyde                                    Marketing

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             10
         3 Clyde                                            20

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Sales
        20 Marketing

SQL>


David Fitzjarrell


6
You cannot put a SELECT in a VALUES clause.  Your procedure should look like this instead:

CREATE or REPLACE procedure INPUT_RECORDS(
p_ename in varchar2,
p_dname in varchar)
IS
BEGIN
INSERT into emp(emp_id, ename, deptID)
 select EmpSeq.nextval, p_ename, deptID
 FROM
 dept where deptname = p_dname;
commit;
end;
/



David Fitzjarrell

7
it doesn't matter that empID is a not null field, it isn't unique therefore it cannot be used to identify each record in the underlying base table. Also you cannot modify more than one base table to a view, even if you set up the primary keys:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /
Begin
*
ERROR at line 1:
ORA-01776: cannot modify more than one base table through a join view
ORA-06512: at "BING.INSERT_TO_VIEW", line 6
ORA-06512: at line 2


SQL>

You'll need an INSTEAD OF trigger to perform the updates you desire.  Starting over from the beginning:

SQL> create table
  2  newdept(deptID NUMBER NOT NULL, deptName varchar2(30));

Table created.

SQL>
SQL> alter table newdept
  2  add constraint newdept_pk primary key(deptid);

Table altered.

SQL>
SQL> create table
  2  newemp(empID  number not null, empname     varchar2(40), deptID NUMBER);

Table created.

SQL>
SQL> alter table newemp
  2  add constraint newemp_pk primary key(empid);

Table altered.

SQL>
SQL> alter table newemp
  2  add constraint newemp_dept_fk
  3  foreign key(deptid) references newdept;

Table altered.

SQL>
SQL> create sequence newempseq
  2  start with 1 increment by 1 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create sequence newdeptseq
  2  start with 10 increment by 10 nocycle nomaxvalue nocache order;

Sequence created.

SQL>
SQL> create or replace trigger newempid_trg
  2  before insert on newemp
  3  for each row
  4  begin
  5          select newempseq.nextval
  6          into :new.empid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> create or replace trigger newdeptid_trg
  2  before insert on newdept
  3  for each row
  4  begin
  5          select newdeptseq.nextval
  6          into :new.deptid
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> Create view E_VIEW as
  2  Select empName, deptName
  3  From newemp, newdept
  4  Where newemp.deptID = newdept.deptID;

View created.

SQL>
SQL> CREATE or REPLACE procedure INSERT_TO_VIEW(
  2          p_ename in varchar2,
  3          p_dname in varchar)
  4  IS
  5  BEGIN
  6          INSERT into E_VIEW values(p_ename, p_dname);
  7          commit;
  8  end;
  9  /

Procedure created.

SQL>
SQL> create or replace trigger e_view_insert
  2  instead of insert on e_view
  3  for each row
  4  declare
  5          dept_id number;
  6  begin
  7          insert into newdept
  8          (deptname)
  9          values(:new.deptname);
 10
 11          select deptid
 12          into dept_id
 13          from newdept
 14          where deptname = :new.deptname;
 15
 16          insert into newemp
 17          (empname, deptid)
 18          values(:new.empname, dept_id);
 19
 20  end;
 21  /

Trigger created.

SQL>
SQL> show errors
No errors.
SQL>
SQL> Begin
  2          INSERT_TO_VIEW('Mark', 'Sales');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from e_view;

EMPNAME                                  DEPTNAME
---------------------------------------- ------------------------------
Mark                                     Sales

SQL>
SQL> select *
  2  from newemp;

     EMPID EMPNAME                                      DEPTID
---------- ---------------------------------------- ----------
         1 Mark                                             10

SQL>
SQL> select *
  2  from newdept;

    DEPTID DEPTNAME
---------- ------------------------------
        10 Sales

SQL>


David Fitzjarrell

Pages: [1]