SQL & PL/SQL :: Subselect Have Too Much Values

May 2, 2012

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]....

View 5 Replies


ADVERTISEMENT

Create Sequence Using Subselect / Value From Select

Jul 8, 2008

CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1

i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?

View 2 Replies View Related

SQL & PL/SQL :: Create Table - Tablename With Data From Subselect (variable)

Sep 2, 2013

I wish to create a table with week-number as suffix _36 or _30

CREATE TABLE TEST1_$WEEKNUMBER AS (SELECT * FROM TEST1);

$WEEKNUMBER is this statement:

SELECT TO_CHAR (TO_DATE(SYSDATE, 'dd.mm.yyyy'), 'IW') from DUAL
CREATE TABLE doesn't accept subselect

How to do that with SQL?

View 8 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

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 Related

Replacing Null Values Of Outer Join With Meaningful Values

Dec 3, 2010

I 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

View 3 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

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]...

View 9 Replies View Related

SQL & PL/SQL :: Ad Hoc MINUS - Compare Values In Code To Values In Table

Oct 28, 2013

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?

View 6 Replies View Related

PL/SQL :: How To Fetch Values From Two Columns Using Values From One Column

Jul 25, 2013

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 Related

PL/SQL :: ORA-00947 - Not Enough Values But Enough Values Are Being Returned

Dec 18, 2012

I 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]...

View 2 Replies View Related

SQL & PL/SQL :: Displaying Multiple Row Values As Multiple Column And Row Values

May 4, 2010

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

View 4 Replies View Related

How To Get Values From A Particular Row

Jul 17, 2012

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)

View 1 Replies View Related

SQL & PL/SQL :: Corresponding Values Of Max Value?

Mar 28, 2012

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

View 1 Replies View Related

PL/SQL :: Sum Of The Values In A Row

Sep 27, 2012

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

View 2 Replies View Related

PL/SQL :: How To Add Row Values

Jun 2, 2013

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

View 3 Replies View Related

Sum Of All Drop Down Values

Nov 28, 2012

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]...

View 1 Replies View Related

SUM TOP N Values Using Rank?

Jul 20, 2010

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]...

View 3 Replies View Related

Getting Error - Too Many Values

Jul 23, 2010

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

View 2 Replies View Related

SQL & PL/SQL :: ORA-00947 / Not Enough Values

Aug 17, 2012

/* 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

View 8 Replies View Related

SQL & PL/SQL :: Max Row Values Into Column

Oct 3, 2011

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

View 2 Replies View Related

SQL & PL/SQL :: Returning Row With Same Values

Mar 22, 2012

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

View 2 Replies View Related

SQL & PL/SQL :: How To Get Values Distinctly

Oct 1, 2010

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]....

View 9 Replies View Related

SQL & PL/SQL :: ORA-00913 / Too Many Values

Oct 23, 2013

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)

View 9 Replies View Related

SQL & PL/SQL :: Column Values In Row

May 10, 2011

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.

View 6 Replies View Related

SQL & PL/SQL :: Combination Of Values

Jan 7, 2011

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

View 6 Replies View Related

PL/SQL :: Repeat Values From Min To Max Value?

Mar 5, 2013

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

View 11 Replies View Related

How To Insert Values In Database

Dec 10, 2006

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>

View 1 Replies View Related

Inserting Values From A Sequence?

Jun 10, 2011

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'

View 1 Replies View Related

Get Values From Table Column Using PL/SQL Or Just Using SQL

Dec 3, 2008

I'm having a table which has a column which has values given inside square brackets.

[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]

Now, the requirement is to get the values which are inside the innermost square brackets.Such as in

First case : 123412
Second one: werer34
Third : qw2_w3rt

Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?

View 3 Replies View Related

Getting Different Values For Session Report

Sep 3, 2010

query for number of users connected to the database:

select count(*) "Number of users Connected" from v$session where type='USER'; it produces 125

The following query is used to describe where and when the users are connected to the database.

set pages 500
set linesize 750
column box format a20
column username format a7
column program format a20
column os_user format a20
[code]...

It should produce 125. But its showing only 119 users. so around 6 users missing in the list. find why 6 users are not listed..

View 6 Replies View Related

How To Remove Obsolete Values

Sep 7, 2012

In V$BACKUP_PIECE there are many backup pieces OBSOLETED and EXPIRED with fields: handle set to null and deleted to as "YES"

I ran the CROSSCHECK BACKUP and DELETE OBSOLETE and DELETE EXPIRED BACKUP, and they works.

But how can I remove these lines? Are they still important for anything?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved