Am running the below query and getting the error below :
ORA-00934: group function is not allowed here
SELECT CONTRNO,
SUBNO AS USER_CODE,
TOTAL_POINTS AS AMOUNT,
STARTDATE + 30 AS DATE_F,
'SUB' USER_TYPE
[Code]....
I have 2 tables used in this problem: ODETAILS and ORDERS.
ODETAILS has the following columns: ONO, PNO, QTY, COST
ORDERS has the following columns: ONO, CNO, ENO, RECEIVED, SHIPPED, ORDER_COST
UPDATE ORDERS
SET ORDER_COST= 1 * ( select SUM(
SELECT COST
FROM ODETAILS
WHERE ORDERS.PNO=ODETAILS.PNO
)
);
In ODETAILS there can be more than 1 row for 1 order. So I'm trying to add all the COSTs in ODETAILS.
i create form,when i press F11, it displays this error:Frm-41009 function key not allowed ,press ctrl+k for list of valid values.And i can't enter value into fields to query records.
View 11 Replies View RelatedIs anyway to create function based index for group function columns.
For example
select max(timestamp),min(age),averge(sal).... ... .. from tab;
i am having two sql query which i have made union of it but i need group by common.
select
distinct ac.CUSTOMER_NAME cust_name,
ac.CUSTOMER_NUMBER cust_no,
ps.CUSTOMER_SITE_USE_ID,
raa_ship_ps.PARTY_SITE_NAME loc,
sum(days_30.AMOUNT_DUE_REMAINING) D_30,
[code]........
I want to use Analytical function instead of group by clause for below query..
select
CASE
WHEN ADMT.SOURCESYSTEM ='CLU'
THEN COUNT(ADMT.TOTAL_COUNT)*5
ELSE COUNT(ADMT.TOTAL_COUNT)
END TOTAL_COUNT
from ESMARTABC.ABC_DRVR_MFAILS_TMP ADMT
group by ADMT.SOURCESYSTEM
I am trying to use decode funtion with GROUP BY ROLLUP.
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB));
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB));
But when i use Decode funtion so that i can combine above two scenarios it is not working
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
I have a table like below:
COLUMN TYPE
USER_ID VARCHAR2 (10 Byte)
PROCESS_ID VARCHAR2 (30 Byte)
END_TIME DATE(STAMP)
TO_LOC VARCHAR2 (12 Byte)
TO_LOC_TYPE VARCHAR2 (15 Byte)
FROM_LOC VARCHAR2 (12 Byte)
ITEM_ID VARCHAR2 (25 Byte)
CASES NUMBER (12,4)
LMS_UDA1 VARCHAR2 (250 Byte)
ZONE VARCHAR2 (2 Byte)
I only want get one record with all columns, only have one clause MAX(END_TIME) But the other column have difference value. when i use MAX(END_TIME) and GROUP BY USER_ID,PROCESS_ID,CASES,... the sql didnot give one record, It give many records
I am having the following pivot query but I am not able to run it is giving error msg
ORA-00937: not a single-group group function
SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))
[code]...
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID",
LOC_ID AS "LOCATION ID",
TEST_DATE AS "DATE",
TEST_TIME AS "TIME"
FROM MON_TEST_MASTER
WHERE AREA_ID =89
AND LOC_ID ='3015'
AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
SQL> create table emp_column_level_cons(empno number(4) constraint empcol_empno_pk primary key
2 ename varchar2(15) unique,
3 job varchar2(20) not null,
4 sal number(7,2) check (sal between 1500 and 25000),
5 deptno number(4) constraint empcol_deptno_fk foreign key re
ferences dept(deptno) on delete cascade);
deptno number(4) constraint empcol_deptno_fk foreign key references dept(deptno) on delete c
*
ERROR at line 5:
ORA-02253: constraint specification not allowed here
================================================
I have a following select statement where I am not generating a sequence in any of these WHERE clause, GROUP BY or ORDER BY clause, DISTINCT clause, along with a UNION or INTERSECT or MINUS or sub-query.
I have query like this
"SELECT
AER_ID_TEMP.AER_ID,
AER_ID_TEMP.D__PRNO,
AER_ID_TEMP.D__PCNT,
CAST((select char_value from aris_param_data where param_id = 101)||seq_record_id.nextval AS INT) as RECORD_ID
FROM AER_ID_TEMP"
but still encountering a error "ORA-02287 sequence number not allowed"
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
We are running a code through informatica and are frequently getting the ORA-08007. The error and the code causing it are mentioned below. (The '?' implies a variable which is supplied by informatica). The source is a flat file.
ORA-08007: Further changes to this block by this transaction not allowed
UPDATE table_20
SET capxuser = ?,
capxtimestamp = ?,
capxaction = ?,
[code]...
1. Tried initially by reducing the commit interval to commit at 100 rows. But still getting the same error.
2. DBA has installed the patch # 6790768. But it still fails.
why commit is not allowed in trigger?
View 3 Replies View RelatedI have created the following package-
create or replace PACKAGE TABLE_PKG1 AS
PROCEDURE make(table_name VARCHAR2, colspec varchar2);
PROCEDURE add_row(table_name VARCHAR2, value_s VARCHAR2,cols VARCHAR2);
END TABLE_PKG1
CREATE OR REPLACE PACKAGE BODY TABLE_PKG1 AS
[code]....
The package and the package body compiles successfully. When I add table-
BEGIN
TABLE_PKG1.make('EMP_new','country varchar2(20), currency varchar2(20)');
END;
The table is created successfully.However when I invoke the package using the following anonymous block for adding the row-
SET SERVEROUTPUT ON
BEGIN
TABLE_PKG1.add_row('EMP_new','"India","Rs"','Country,Currency');
END;
The it generates the following error-
Error report:
ORA-00984: column not allowed here
ORA-06512: at "SCOTT.TABLE_PKG1", line 14
ORA-06512: at line 2
00984. 00000 - "column not allowed here"
I have a table stud_mst In which i have following columns STUD_NAME , ROLL_NO , COURSE
Table values are for e.g
STUD_NAME , ROLL_NO , COURSE
Harshita 1323 BSC
Atul 1734 BCOM
L'Pradhan 3739 BA
Still today user can enter any special character value in stud_name like L'Pradhan . I want to put a restriction on stud_name so that user can enter only alphabetical value into stud_name column.
I've got the following SQL script that I'm trying to run against a database: -
CREATE TABLE jdp_parameters (
id NUMBER(9) CONSTRAINT pk_jdp_parameters
PRIMARY KEY
CONSTRAINT ck_jdp_parameters_id
CHECK (id = 0),
[code]....
On running it I get the following errors: -
pricelevel_id NUMBER(9) CONSTRAINT fk_jdp_pricelevel_id
*
ERROR at line 10:
ORA-02253: constraint specification not allowed here
Sequence created.
REFERENCES jdp_parameters(id)
[code]....
I am trying to create one Materialized view from a Select Statement and I am getting the following error
SQL Error: ORA-01723: zero-length columns are not allowed
I have checked the table and found nothing like varchar2(0) or char(0) , but the column in my select statment may get a Null value. Will it result in error like above ?
Normal View : I have created this because I think a materialized view would not be created if we have a Sub query.
CREATE OR REPLACE VIEW mx_test
AS
SELECT t."EXTN_SERVICE_REQUEST_NO",
t."EXTN_SERVICE_TYPE",
[Code]....
Materialized View :
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error :
Error starting at line 1 in command:
CREATE materialized VIEW mx_ashok
AS
SELECT * FROM mx_test
Error at Command Line:3 Column:16
Error report:
SQL Error: ORA-01723: zero-length columns are not allowed
01723. 00000 - "zero-length columns are not allowed"
*Cause:
*Action:
Do I need to use NVLs for all columns which may result in NULL value ?
Does know why I am getting ORA-01733- virtual column not allowed here Following :
CREATE TABLE EMPL
(
EMP_ID NUMBER(10),
CNT NUMBER(10)
)
insert into empl(EMP_ID)
values(1)
[code]....
ORA-01733- virtual column not allowed here
where do I find a list or a description of allowed characters for a 10g password?
View 8 Replies View Relatedcreate sequence seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
create table t ( id number, b date);
insert into t values (seq.nextval,sysdate);
update t set b=sysdate-1 where id=seq.currval;
update t set b=sysdate-1 where id=seq.currval
*
ERROR at line 1:
ORA-02287: sequence number not allowed here
how can i update as above ..
I am trying to do bulk insert into a table. Attached is the script I am running:
But when I run this script I get exception : 'ORA-01438: value larger than specified precision allowed for this column'.
I have checked in my soucre table as well as in inserting table, everything looks fine to me.
how to handle such exception, which could actually tell me for what column and what data it is throwing exception.
Refer to the txt file to create table and insert data.
I executed the following query-
SELECT priority, detail, COUNT(1) FROM TEST GROUP BY priority, detail
and got the following result-
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial TSS 1
StandardInitial development 10
StandardProduction deployment5
High PriorPatch 1
Now I want that Initial TSS and Initial development should be combined as Initial together and I should get the result as follows:
PRIORITYDETAIL COUNT(1)
StandardPatch 27
StandardInitial 11
StandardProduction deployment5
High PriorPatch 1
1. we have to use shared server mode with Xa transactions, if not we get this error:
QUOTE ORA-24777: use of non-migratable database link not allowed
Cause: The transaction, which needs to be migratable between sessions, tried to access a remote database from a non-multi threaded server process
2. The SGA and PGA_AGGREGATE targets are limitted to 4GB as an Oracle consultant's recommendation [I don't know the reason, but I have been informed that it is recommended so I don't dare to increase it]
Taking into consideration the above points and the below information, what should I do then... I'm not willling to change the workarea size policy to manual as I'm affraid of the consequences...
QUOTE MTS becomes downright dangerous when Automatic Shared Memory Management (ASMM) or Automatic Memory Management (AMM) is in place.
When you use MTS and AMM (or ASMM) together, PL/SQL programs that try to create large collections can effectively consume all available server memory with disastrous consequences . .
AMM allocates virtually all memory on the system to the large pool in order to accommodate the PL/SQL memory request. First it consumes the buffer cache, then it reduces the PGA_AGGREGATE_TARGET - all the way to zero!"
I'm a software developer, not an Oracle DBA
Our product runs a lot of stored procedures in the background to do various things. These stored procedures obviously include a ton of select statements, insert statements, etc. Some of them get pretty complex. Once in a while, we run across the following error: "ORA-03127: no new operations allowed until the active operation ends(3127)." Once this happens, pretty much everything breaks with this error for a while. Eventually (LOOOONG time), this error "resolves itself" and things start working again. Conceptually, I understand that there seems to be some blocking operation on the DB, but because we run a LOT of stored procedures and SQL statements, it's extremely difficult to pin this down.
I write plsql code as following:
DECLARE
TYPE id_collection IS TABLE OF NUMBER;
TYPE title_collection IS TABLE OF VARCHAR2(60);
id ID_COLLECTION;
[Code]...
I got following error: PLS-00642: local collection types not allowed in SQL statements.
I have a table say MY_TAB with columns as below
emp_id number,
name varchar2(30),
from_dt date,
remarks varchar2(60)
insert into MY_TAB values (1,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (1,'TOM','02-JAN-13', 'some remark');
insert into MY_TAB values (2,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (3,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','01-JAN-13', 'some remark');
insert into MY_TAB values (4,'TOM','02-JAN-13', 'some remark');
How do I ensure that when a user tries to insert record with emp_id as 1, then he should only be allowed to enter another from_dt but the value in the name column have to be the same as in the previous row of emp_id 1.
insert into MY_TAB values (1,'TOOM','03-JAN-13') --shld not be allowed.
I have a function that returns the situation for one month for some database. I need to implement it in some report medium for one year. The one year function works ok.
My problem is when trying to make another function that runs the monthly function 12 times and that error is "PLS-00653: aggregate/table functions are not allowed in PL/SQL scope".am trying to get around some restrictions and somehow until this part things seem to be ok.
I tried to use a union with 12 blocks but it works very slow in the reporting environment and now i want to try to make another function that runs another function 12 times depending on the parameter.
here is the code (there might be some name misuse since i had to change the names of the original code -
CREATE OR REPLACE
FUNCTION anual(monthh in varchar2, year IN VARCHAR2)
return anual_REP_var PIPELINED is
BR anual_REP:=anual_REP(NULL,NULL, NULL,NULL);
contor INT(2);
[code]...