SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query
Oct 21, 2012
I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :
1. The number of assignments created from it (after a given constant assign date)
2. The number of assignments created from it that have been approved.
3. The number of unique users who have either created or approved an assignment from it
Test data :
create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');
[code]....
Assignment :
id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY
[code]....
Research_paper:
id name
----------
1A
2B
Expected result :
r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3
I wrote the following query for that :
SELECT rp.id r_paper_id,
COUNT(*) created,
COUNT(
CASE
WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
View 7 Replies
ADVERTISEMENT
Mar 17, 2013
To display highest marks,least marks,average marks,total marks of the student name entered.
desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER
How do I do that using PL/SQL and without Cursor.
View 4 Replies
View Related
May 1, 2011
I have a problem executing a function.
There are two tables,
1. Table with column names of a second table mapped to certain variables.
ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------
2. Table with the values for columns as given in table 1 ex:
col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r
I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.
The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.
I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.
Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)
View 2 Replies
View Related
Oct 8, 2013
Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error
SQL Error : ORA-14551: cannot perform a DML operation inside a query
Below Is the
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;
[code].....
View 27 Replies
View Related
Aug 9, 2011
Calling function
select PACK.MAIN('blah') from dual
generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245
Unfortunately the Body is not accessible to see.The spec of the function is:
FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:
var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar
But this generates: ORA-01008: not all variables bound
View 3 Replies
View Related
Oct 13, 2012
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END
View 11 Replies
View Related
Apr 13, 2013
when i run a form no information shows up until i click execute query... i need the info to be their automatically to browse with the previous and next button
View 3 Replies
View Related
Jun 11, 2008
I have a table structure like :-
Create table test(A varchar2(50),B NUMBER);
The data in that table is like that:-
A B
----------------------
2*3
2*4*5
4*5
column B contain no data.
I want to create a function which can be used in a select query,and the output should come like that :-
A B
----------------------
2*3 6
2*4*5 40
4*5 20
Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.
View 20 Replies
View Related
Sep 7, 2011
I am trying to create a view of a query that i would be using regularly...the query contains a function call in it...can i use it..
If yes when i try to do it . It gives out an oRa-01031:in sufficient privileges.
View 3 Replies
View Related
Nov 8, 2011
I have question.Using procedure inside the function ?can I get better performance?
View 8 Replies
View Related
Oct 10, 2013
In the follow code example, is it possible to save the seeds that I generated into a table when I call this table function without expliciting doinginsert into <some_table>select select * from table(pkg_seed.getSeed(200)); I try the automonous_transaction clause but it does not work.
--drop package pkg_seed--drop type seed_tab CREATE or replace TYPE seed_rec AS OBJECT( id number,seed number); CREATE or replace TYPE seed_tab AS TABLE OF seed_rec; CREATE or replace PACKAGE pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED;END pkg_seed;/ CREATE or replace PACKAGE BODY pkg_seed IS function getSeed(maxrow in number default 100) RETURN seed_tab PIPELINED IS cursor cur_seed(vmaxrow number) is select rownum id, floor(dbms_random.value(1,1000) ) seed from dual connect by level <= vmaxrow; l_seed cur_seed%rowtype; BEGIN open cur_seed(maxrow); LOOP FETCH cur_seed into l_seed; pipe row(seed_rec(l_seed.id,l_seed.seed)); END LOOP; RETURN; -- the function returns a single result END getSeed;END pkg_seed;/ select * from table(pkg_seed.getSeed(200));
View 15 Replies
View Related
Feb 25, 2012
i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet
create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin
begin
execute immediate 'select * from example1';
exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/
select * from example;
View 8 Replies
View Related
Mar 7, 2010
I have code inside function
.....
cursor cur1 is
select *
from sarchkler
where sarchkler_appl_no = in_appl_no
begin
select max(saradap_appl_no) into in_appl_no from saradap;
for rec1 in cur1 loop
......
my question I get variable for cursor after cursor declaration
View 7 Replies
View Related
Apr 5, 2010
DECLARE
cnt number(10);
BEGIN
SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP
IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227')));
DBMS_OUTPUT.PUT_LINE (cnt);
END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
View 22 Replies
View Related
Jun 2, 2011
I have created a function in form field(when validate item) this should be called in separate procedure. How to call this function in procedure?
View 4 Replies
View Related
May 14, 2011
While writing a procedure I went into this problem. Whenever I write Query : Select * from dba_pending_transactions It works fine.
But whenever I use same Select Query inside PL-SQL block it gives error Table or view not exist. Dba_pending_transactions is view.
SQL> declare
2 v_count number(2);
3 begin
4 execute immediate 'select count(*) from dba_ending_transactions' into v_count;
5 dbms_output.put_line(v_count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at line 4
Same error I get when i use it inside a procedure.
View 2 Replies
View Related
May 24, 2010
I have the folloiwng two queries:
Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;
I need to compute the following:
var:=(yy/zz)*100
How can I achieve this in a single query?
View 3 Replies
View Related
Oct 10, 2013
I am encountering error in this code.
WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept )
LOOP
v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ;
v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ;
END LOOP;
Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only
View 1 Replies
View Related
May 5, 2011
I hit a bottleneck where my insert trigger won't execute the insert statement (with subquery). See illustration below:
Step# 1 - Table definition:
Table_A(a1 number, a2 varchar(10), a3 varchar(10))
Table_B(b1 number, b2 varchar(10), b3 varchar(10))
Table_C(c1 number, c2 varchar(10), c3 varchar(10))
Step# 2 manipulated the tables:
Inserted 3 records in Table_C.
Then I created an Insert Trigger to Table_A with an insert statement into Table_B and a subquery to Table_C. Please see below:
CREATE OR REPLACE TRIGGER TABLE_A_TR
after INSERT OR UPDATE OR DELETE ON TABLE_A
FOR EACH ROW
DECLARE
[code]......
Step# 3 compiled the created trigger and I've successfully compiled it.
Step# 4 Tested the trigger (TABLE_A_TR) using an insert statement to TABLE_A.
Insert into TABLE_A values (1,'testa','testb')
I've successfully insert the values into TABLE_A however I've observed that the trigger didn't execute the insert statement because TABLE_B has an empty rows. I tried to manually execute the insert statement just to see if there's an issue in my insert statement but I've successfully populated the values into TABLE_B. So I'm wondering why the trigger didn't execute the insert statement.
View 4 Replies
View Related
Jul 26, 2011
CREATE OR REPLACE TYPE TEST_OBJ_TYPE IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
CREATE OR REPLACE TYPE TEST_TABTYPE AS TABLE OF TEST_OBJ_TYPE
/
[code]....
I need to include the above function in a plsql package. How I can declare a object type and table type in a pks file? the syntax to include the above code in a .pks and .pkb file?
I got this code snippet online when I was looking for function that returns a table type. what exactly that Exception block does? delete the table when there is an exception, otherwise return the table type?
View 10 Replies
View Related
Apr 9, 2013
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST
(
V_COUNT OUT NUMBER
)
AS
V_FOO NUMBER(2,0);
BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
where my mistake is?
View 7 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Jan 8, 2013
i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.
For example:
select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.
View 3 Replies
View Related
Nov 3, 2011
i am trying to do something the following .. but I can't get the syntax correctly for the select statement inside the secondary_loop ...
EmailBodyHTML := '';
main_loop := '';
secondary_loop := '';
[Code]....
View 4 Replies
View Related
Jul 25, 2013
Below is the block which i am trying to test in scott schema. I dont want to substute IN clause values directly. So i have written cursor and have added in separate variable separeated by comma.But its not working.
declares varchar2(1000);s1 varchar2(1000);v number := 0;v1 varchar2(2000) := 'SCOTT';j number := 0;cursor hhis select ename from emp;beginselect count(*) into v from emp; for i in hh loops := s||''''||i.ename||''''; j := j+1;if j <> vthen s := s||',';end if;s1 := s1||s;s := null; end loop;dbms_output.put_line(S1); case when v1 in (s1) then dbms_output.put_line('Y'); else dbms_output.put_line('N'); end case;end;
View 3 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998
But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 2 Replies
View Related
Jun 17, 2010
There is a table with column holding 3 NULL values one each in every record. When a count function is applied to the column with a filter on NULLs,it returns 0 instead of 3.
But when count(*)is applied,expected result is returned. Would be interested in knowing about this strange behavior of NULL/Count.
View 3 Replies
View Related
Dec 7, 2012
SELECT COUNT(*) FROM APP.Big_table; Number of record--222653402
This is a big table to get the it's taking 8 minutes time, so that I tried the following SQL to get the count quickly.
SELECT num_rows FROM all_tables WHERE table_NAME='BIG_TABLE'
and owner='APP'; --Number of records 237213998But I got the different count for the two SQLs. Why there is a difference in the count. Which one is correct.
View 3 Replies
View Related
Mar 28, 2011
I'm having trouble with some SQL code regarding count and an outer join.
Here is my code.
SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;
Where salesperson is a salesperson table and ord is a table containing orders.
The orders table contains a FK to salespersonid in the salesperson table.
I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.
View 13 Replies
View Related
Aug 29, 2012
I'm trying to SUM the results of "*SELECT originator, COUNT(*) as Inductions*" but am having trouble finding a solution? Can this be done and cause it to create the SUM in a row below the created Inductions column?
View 6 Replies
View Related