im new to Database i have to count no of messages based on the minuties like 1-5, 6-15,16-30,>30 min how to write query
select count(*) from table1
where (
select
( (extract(day from column2)-extract(day from column1))*24*60 +
(extract(hour from column2)-extract(hour from column1))*60 +
extract(minute from column2)-extract(minute from column1)) as Processed_time from table1
) IN (1,5)
getting an error TOO MANY VALUES
I have two tables
Table1
Id1 Name1
1 Jack
2 Jack
3 John
Table2
ID2 Name2
NULL Jack
NULL John
I would be assigning ID2 from ID1 based on name match.As Jack has 2 ids when I use the statement
UPDATE TABLE2
set id2 = (select distinct ID1 from table1 where table1.name1=table2.name2);
I get an error message as select statement would return more than one row and the update statement fails completely. with the sql statement to update the ID2 as error when we have duplicate records and continue with the update for other records.
like
table
ID2 Name2
ERROR Jack
I am trying to insert values into my database but it keeps coming up with the same error message: 'not enough values'
Here's what am I trying to do:
This is the insert I am having trouble with.
INSERT INTO Customers
VALUES (000120, 'Andy', 'Finnigan', 'M', '23/JUL/1978',
Address_varray_type
(Address_type('Home', 76, 'Fun Way', 'Semilong', 'NORTHAMPTON', 'NN3 8YF'),
Address_type('Work', 54, 'Region Street', 'Halfache', 'OXFORDSHIRE', 'OX4 7EG'),
Contact_table_type
(Contact_type('Non_work hours', '07659485743', '02084731131', 'AFinnigan@yahoo.com'),
Contact_type('Work_hours', '07795052846', '0768543323', NULL))));
Here are the Object tables.
CREATE TYPE Address_type AS OBJECT(
Location VARCHAR2(20),
House_number NUMBER(6),
Street_name VARCHAR2(30),
Town VARCHAR2(20),
County VARCHAR2(20),
Postcode VARCHAR2(20));
/
CREATE TYPE Contact_type AS OBJECT(
Contact_type VARCHAR2(30),
Home_no VARCHAR2(20),
Mobile_no VARCHAR2(20),
Email VARCHAR2(50));
/
CREATE TABLE Customers(
Customer_id NUMBER(6) NOT NULL,
Firstname VARCHAR2(20),
Familyname VARCHAR2(20),
Gender CHAR DEFAULT 'M',
DOB DATE,
Address address_varray_type,
Contact_details contact_table_type)
NESTED TABLE Contact_details
STORE AS customer_contact_table;
I'm having a problem with null values. I want to display old student in a display item if subjects text item has records. Otherwise, display new student if it has null values. This is the code that i tried so far..
IF :block2.subjects IS NULL THEN
:block3.type := 'NEW STUDENT';
ELSE
:block3.type := 'OLD STUDENT';
END IF;
But i got logic error during runtime.
We have recently shifted our database from 10G to 11G and after the intial hickups most of the thinghs have stablised. We had changed system by Alter System command so that database does not have case sensevity problem and with that all the reports from the forams have also stabilied However some of the forms are failing with the above error ORACLE ERROR Unable to read list of values
These form are running fine with users having DBA privelege. However other users this is failing.
We have also checked that with users not having DBA privelege we are able to read the data in the table within the form also. The query in the record group is very simple
"SELECT CODE,SHORTDESC FROM GENCODES WHERE CODETYPE='BS' AND CODE <> '00'"
All these forms were fine previously when database was on 10g.
Moreover there are many other fields where list of values are there and allthose are ruing fine.
I have also checked datatype and length in query as well as fields on the form
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies View RelatedI have a scenario where I have to get all the available dates of a resource. I am using the below query to get it.
Select Avail_Date AS MONTH
, Resource_Id
FROM res_tsk
, (SELECT Rownum - 1 + TRUNC (sysdate) avail_date
FROM Dual
[code].......
The result of this is:
Month Dates Resource_ID
12/3/10 0:00 NULL
12/4/10 0:00 NULL
12/5/10 0:00 NULL
12/6/10 0:00 100033868
As I am doing a outer join, if the resource is not available on a particular day the resource_id is coming as NULL as it is not available. Is there any way to populate this NULL resource_id with the original resource_id as the resource_id is same for all the result set.
I need the output to be
Month Dates Resource_ID
12/3/10 0:00 100033868
12/4/10 0:00 100033868
12/5/10 0:00 100033868
12/6/10 0:00 100033868
My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.
The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.
fixing the errors or if there is any new logic that can be implemented.
DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)
[Code]...
I am searching the simplest way for ad hoc MINUS.I do:
SELECT *
FROM uam_rss_user_XXXXXXX
WHERE host_name IN
('XXX0349',
'XXX0362',
'XXX0363',
'XXX0343',
'XXX0342',
'XXX0499',
[code]....
and look in the table which values are missing (values that are in host_name IN but not in actual table).is there a simpler way for doing an ad hoc MINUS? I know to insert values in temp. Table. How are experienced Oracle pros doing this task?
From two given tables, how do you fetch the values from two columns using values from one column(get values from col.A if col.A is not null and get values from col.B if col.A is null)?
View 2 Replies View RelatedI created a package with some types, and every was compiling fine. However, when I ran the new function, I got an error: ORA-21700: object does not exist or is marked for delete
After a little research, I realized that the types would have to be declared outside the package. As soon as I did that, I suddenly started getting the "not enough values" errors on all my types. I compared the number of columns being returned, and the number of columns in the type, and they match.Here is my type code:
CREATE OR REPLACE TYPE TSA_CUSTOM.Lost_Plan as object (
LP_Key number, -- The member key of the plan that is going away
LP_Type varchar2(20),
LP_Dept varchar2(12),
LP_SubDept varchar2(12),
LP_Class varchar2(12),
LP_VendorName varchar2(50)
[code]...
find the Test Case below.
--Creation of Table
create table tb1
(ID number(4),
event varchar2(20),
vdate date);
--Inserting Values into the Table.
INSERT ALL INTO tb1 (ID, event, vdate) VALUES (01, 'V1', '01-JAN-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V2', '02-FEB-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V3', '04-MAR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V4', '03-APR-2009')
INTO tb1 (ID, event, vdate) VALUES (01, 'V5', '05-MAY-2009')
[Code]...
--Selecting data from Table.
SELECT * FROM TB1;
ID EVENT VDATE
---------- -------------------- ---------
1 V1 01-JAN-09
1 V2 02-FEB-09
1 V3 04-MAR-09
1 V4 03-APR-09
1 V5 05-MAY-09
2 V1 01-JAN-10
2 V2 02-FEB-10
2 V3 04-MAR-10
2 V4 03-APR-10
2 V5 05-MAY-10
10 rows selected.
how can i display the data as below format using Oracle 9i SQL.
IDV1 V2 V3 V4 V5
--- ---------------- ------------ --------------- -------------- ------------
11-Jan-092-Feb-094-Mar-093-Apr-095-May-09
21-Jan-102-Feb-104-Mar-103-Apr-105-May-10
Oracle version - 10.1.0.4 OS - HP-UX
In my alert log I am getting the following error quite repeatedly; My application hangs, and have to restart the instance.
CODEORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6
Suppose I have a table containing 2 columns and 5000 rows.
I need to take values that are at 259th, 561st, 872nd rows.(just example, these values are not fixed, can be anything from 1 to last row)
Here's what I need to do.
1) I need to find the max(peak_area) where the detecor = 'spectrum'.
2) Then I need to return peak_area for detectors (dad and elsd) of the highest corresponding peak_id.
sampledetectorpeak_idpeak_area
123dad175
123elsd180
123spectrum168
123dad290
123elsd260
123spectrum295
So in this very small dataset I need to return
123dad290
123elsd260
I have a table table1 with columns a,b,c,d
I am fetching some results from this table by means of this query:
SELECT
a,
b,
sum(case when c='F' then '1' else '0' end) "c_data",
sum(case when d='S' then '1' else '0' end) "d_data"
[Code]...
My result will be like this:
a b c_data d_data
abcd efg 1 1
asff fjd 1 0
dfd fd 0 0
Now i need a 5th column, which should look like this:-
a b c_data d_data Total
abcd efg 1 1 2
asff fjd 1 0 1
dfd fd 0 0 0
i want to add the values of rows in a table. for example if we enter marks for five subjects to a student we need to calculate the total marks and the average for that student. how to do this.
sample as
m1 m2 m3 m4 m5 total avg
== == == === === === ====
55 79 80 81 82 377 75.4
68 94 84 73 94 413 82.6
I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8" />
[code]...
I certainly know this is possibly but I am trying to do this on the fly and can't seem to work it out:
I have a table A:
ID Name Priority
-------------------
1 Smith 1
1 SSmith 2
1 ASmith 3
1 BSmith 3
2 John 2
3 Ed 1
and I am looking to create the following table from this:
ID Name Sum(Top3Priority)
--------------------------------
1 Smith,SSmith,etc 8
Now, I've got listagg working and everything appears to be going swimmingly but: for every listagg grouping on name I need to only sum the highest top 3 priorities. So in the example above there are four Smiths but I need to only sum the top 3 priorities which are 3,3,2 and ignore the 1 even though I do want all the listagg Smith's (SSmiht, ASmith, etc) in there.
Now I can sum the priority, but don't really know how to sum only the top 3 in any ID ? There can be 1 to n ID's so if there are only 2 ID's I want to sum those 2, if there are 3 all 3 and 4 upwards only the top 3.Here is a snippet of the SQL I am using
SLECT id,
listagg(MN_CR_LOOKUP.f_name, ',') within group (order by Priority)) roadname,
**** sum top 3 here ?****
count(*) "NumI", Sum("Elevation") "CombinedElevation"
FROM jc,
[code]...
/* Formatted on 08/17/2012 11:56:42 AM (QP5 v5.163.1008.3004) */
DECLARE
src_csr1 INTEGER;
src_x INTEGER;
dst_csr1 INTEGER;
v_DB_link VARCHAR2 (20);
v_Db_name VARCHAR2 (20);
v_sql1 VARCHAR2 (2000);
v_sql_src VARCHAR2 (2000);
[code]....
Getting the following error - I have checked and made sure that the query returns 4 columns.
DECLARE
*
ERROR at line 1:
ORA-00947: not enough values
ORA-06512: at line 60
I have table output as shown below.
AID UCD U_TXT UDATE PID
116 1 Req Documents 01-OCT-2011 100
116 2 AGG APPR 01-OCT-2011 101
116 3 Docs received 02-oct-2011 102
116 1 Tmp received 02-oct-2011 103
117 2 Notice sent 03-oct-2011 104
UCD - We have total 19 codes (1 to 19), each can have multiple rows for one AID.. like 1 repeated twice for AID 116.
PID - Primary id (Primary key column)
Output I am looking
--------------------
AID COL1 COL1_TXT COL1_DATE COL2 COL2_TXT COL2_DATE..ETC
116 1 'Tmp received' 02-oct-2011 2 AGG APPR 01-OCT-2011
117 2 Notice sent 03-oct-2011
If the same UCD repeated multiple times then we should get the max(PID) record for that UCD and for that AID
I tried with group by AID,PID. but couldn't bring the rows to columns. I have attached the script with the post
create table testing
( id number (10),
key number (10)
)
insert into testing values (1,10)
insert into testing values (1,10)
insert into testing values (2,10)
insert into testing values (2,20)
insert into testing values (3,10)
My requirement is to return
id key
1 10
1 10
because both their id and key are same
I have to display the sum of CostPrice(CP) for individual employees only once. This is currently repeating.
create table emp_item(empno number, item varchar2(10))
INSERT INTO EMP_ITEM ( EMPNO, ITEM ) VALUES (
1, 'item1');
INSERT INTO EMP_ITEM ( EMPNO, ITEM ) VALUES (
1, 'item2');
INSERT INTO EMP_ITEM ( EMPNO, ITEM ) VALUES (
1, 'item3');
[code]....
when i ran this below query it shows an error: ORA-00913: too many values
select * from oe_order_lines_all
where header_id in(select * FROM oe_order_headers_all where order_number=4721)
I'm new to Oracle so I try to become familiar with SQL.
In my script I want to become only this results back in the select of the Where clause which have more than 1 entrys per job_id.
But my try didn't work ... error: too much entries ??!!
SELECT
last_name
, job_id
, salary
, AVG(salary) OVER(PARTITION BY job_id) Average
[Code]....
i've follow problem:
select x1
from table
and i get
a
b
c
What i want is
a,b,c
The main problem here is, that it can be
a
b
as such as
a
b
c
d
e
f
in detail, i don't know prior how many i will get. With declare a cursor is it easy, but i try to find one solution only with sql.
I have a requirement in this format.
These are the scripts for the 2 input tables
CREATE TABLE TMP_split
AS
SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL
UNION ALL
SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
[Code].....
The output needs to be in this format
key | val | grp_id | pct
------------------------------------------
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM21~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM22~DIM31 | 1000 | 40
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 60
6~7~8 |DIM_11~DIM23~DIM31 | 1000 | 40
Is there a good way to dynamically obtain this output either by PL/SQL or by query?
This is a case where combination needs to be used. However, I am not able to arrive at the right algorithm for this
CREATE TABLE TEST
(
NAME VARCHAR2(100),
STR NUMBER,
ED NUMBER
)
Insert into TEST (NAME, STR, ED)
Values ('raj', 2, 4);
Insert into TEST (NAME, STR, ED)
Values ('kri', 1, 3);
COMMIT;i want output like below
raj 2
raj 3
raj 4
kri 1
kri 2
kri 3
i want to create database. i have created the schema & done all activity like decide foreign & primary key of table all things on paper? when i open oracle 8i & what should i write at front of sql prompt. For enter the value into database should i give the set path? How to store value into particular directory or folder in hard disk? should i directly start with create database?
I know proper syntax. Suppose i want to store the database & values in folder " D:apurva " what should i do. After entering oracle 8i it show sql> so what i do to store database in D:apurva . should i directly start with create table command in front of sql>
I created a Table with a single column varchar2.. in which I wanted to insert value like 'BBBBAB1'... till 'BBBBAB100'
Created a sequence starting with 1...
and inserted single row, and multiple rows using loop also; by using below code -
insert into Trans SELECT CONCAT('BBBBAB', Trans1.NEXTVAL) from dual;
but whenever I see the values they are not as required ... 'BBBBAB1' but one character 'B' is missing, and the values populating are 'BBBAB1'.. 'BBBAB100'