SQL & PL/SQL :: Select Single Query And GroupBy Together?

Jul 8, 2011

i have a problem in the following query. i need to fetch the rows such that i want to fetch all the records keeping "segment1" column as distinct and sum all of the corresponding "quantities" column.

select prha.segment1 --as requisition_no
,prha.creation_date
,sum(prla.quantity)
,prha.description

[code]...

i tried to use the partition technique. using partition solved the problem apperently. the sum function worked but redundancy in "segment1" column still persists. i used the sum function only to extract the distinct "segment1" column and summing its corresponding "quantity" column (only quantity column differs in the redundant rows...)

the second query was like:

SELECT prha.segment1,
prha.creation_date,
SUM(prla.quantity) OVER(PARTITION BY prha.segment1) AS qty,
prha.DESCRIPTION,

[code]...

View 1 Replies


ADVERTISEMENT

Groupby Query - Not Getting Output?

Jun 25, 2013

I have a table with data as follows

Source name address city
File Y N N
File N N Y
DB Y N Y
DB N Y N
XML Y Y N

I am trying to get output as follows

Source Y/N CountName CountAddress
File Y 1 0
File N 1 2
DB Y 1 1
DB N 1 1
XML Y 1 1
XML N 0 0

View 2 Replies View Related

PL/SQL :: Select Top Row In Single Query?

Sep 19, 2013

write a query to get the first row after order by clause using single query alone.Example:I can write following query to select first rowselect * from (selec * from t order by col1) where rownum = 1;But here I should not use inline view to get the result. Because my original requirement needs to use this query in select list and it needs to use a column (of a table from the FROM clause) in the where clause of inline query. Because there is restriction that we can not use the column (of a table from the FROM clause) more than one level of inline query.

View 6 Replies View Related

Fetch Records In Single Select Query And Display

Mar 15, 2011

I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.

Name id dept_no
Ram 101 10
20
30
Ani 201 10
20

View 1 Replies View Related

SQL & PL/SQL :: Sum And GroupBy

Oct 25, 2012

I'm trying to a sum on a group by. However if you notice there are not data in year 1997 for id 20 and not data in year 1999 for id 21. I would like the sum to come back as empty since there are missing data within the group.

Currently:
GRP CNTY YR SUM
AGER1996150000
AGER1997130000 =====> Should be null
AGER1998170000
AGER199940000 =====> Should be null
AGER2000190000
AUSA1996150000
AUSA1997160000
AUSA1998170000
AUSA1999180000
AUSA2000190000

create table test (id integer, wage number, grp varchar2(5), cnty varchar2(5),yr integer);

[Code]....

View 6 Replies View Related

PL/SQL :: Single Select Output

Jun 19, 2012

Having a select query where the output looks as below, as you can see when there is no record for a particular date(01-JUn-12,03-JUN-12) the count will be zero.

SELECT TRUNC(updated_date) DATE1 , COUNT(1) COUNT FROM Temp_a
WHERE ZU_flag='N' AND circle_id 'NA'
AND TRUNC(updated_date) >=TRUNC(SYSDATE-18) AND TRUNC(updated_date) <=TRUNC(SYSDATE)
GROUP BY TRUNC(updated_date)
ORDER BY TRUNC(updated_date) DESC;

O/p

DATE1 count
10-JUN-12 14208
08-JUN-12 307825
06-JUN-12 138790
05-JUN-12 167562
04-JUN-12 51870
02-JUN-12 130582
01-JUN-12 239806

But i require a query in such a way that when there is no record for a date , the date has to get populated with zero count

Excepted O/P

DATE1 count
10-JUN-12 14208
09-JUN-12 0
08-JUN-12 307825
07-JUN-12 0
06-JUN-12 138790

[Code]....

I have tried with Case conditions and try to fetch that data through Sequence generating Date(not useful). I can do this with Cusror, but i need this to be done through single select query

View 5 Replies View Related

PL/SQL :: Single Select Statement

Apr 22, 2013

I am trying to find the best solution for the following problem

I have a table called HIERARCHY with 3 columns.

col1 col2 col3
------------------------
1
1 2
1 2 3
4 5
6 7 8

I would like to check if the table contains invalid records. Col1 , Col2 and Col3 are seen as hierarchy levels and col1 is the top level.

For instance record 1,2,3 (level3) is valid because it exists record 1,2 (level 2). Moreover 1,2 record (level 2) is valid because it exists record 1 (top level).
However 4,5 and 6,7,8 are not valid as higher levels are not completed.

How can I check in a single SELECT statement if the table contains invalid records? Maybe analitical functions or regular expressions?!

View 5 Replies View Related

SQL & PL/SQL :: How To Select Data When It Has Single Quote In Value

Sep 4, 2013

The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)

select descrip into v_result
from c_ship_hist
where shipment_dtl_id = :SDID;
exception when others then null;

The error I get is "Missing right quote". How do I code around this issue without having to change the data?

View 8 Replies View Related

SQL & PL/SQL :: Single Select Statement Instead Of Procedure?

Oct 2, 2010

presenting the case as follows:There are two dates with time components like

26-sep-2010 13:00 and 29-sep-2010 19:00

In between these dates hours between 20.00 to 05.00 (night hours)need to be considered and In these night hours less than three hours can be ignored and between 3 to 9 hours should be treated as 1. Need the number of one's in between the dates.

Procedure, cursor solution is not needed and need single select statement.

View 5 Replies View Related

SQL & PL/SQL :: Select From - Single Row Subquery Returns More Than One Row

May 17, 2010

select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));

and it is showing:

ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

View 5 Replies View Related

PL/SQL :: Multiple IF Else Statement In A Single Select?

Oct 17, 2012

I want to run multiple IF Else statements in a single select SQL, each statement is one SQL operating on the same table, what is the best way to write this select SQL query ? If it is PL/SQL, when i get the result from the first IF statement I will skip the remaining execution, and so on..

View 9 Replies View Related

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

SQL & PL/SQL :: Merge Two Statements (Select ID) Into Single Output

Aug 11, 2013

I want to merge the following two sql statements into single output.

select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;

select id,code from dbo;

View 11 Replies View Related

SQL & PL/SQL :: Tracking Exception In One Single Block For Three Different Select Statement?

Mar 8, 2010

how can i track the exception for three select statement in one pl-sql block. here is synario.......

begin
select * from emp where empno=1234; --statement 1
select * from cusotmers where cust_id=125; --statement 2
select * from products where product_id='a-3'; --statement 3
end;

i want to track exception any one for ex no_data_found for all these three different statement.

I know if i put this three statement in three different pl-sql sublock then i can trap it....

how can i trap it in one single block?

View 4 Replies View Related

Application Express :: Multi-Selection With Single Select Value

Jul 30, 2012

Earlier i used select list item type with single select values so, According to my requirement in where clause i have written the condition like this

NVL(publish_id, :P91_PUBLISH_ID) = NVL(:P91_PUBLISH_ID,publish_id)

but my requirement is multi selection. How could i write same condition for multi selection.

write the same condtion for multi selection.

View 8 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: Single Row Query Returns More Than One Row?

Oct 3, 2010

I am getting an error "Single row query returns more than one row" in an Exception block.

But in subqueries I am using IN operator not using =.

And I don't have duplicates rows in tables.

View 16 Replies View Related

SQL & PL/SQL :: Getting Total And Single Name In One Query?

Oct 29, 2013

I want to get any employee name of deptno 10 but total count of number of employees under dept 10.

DECLARE
l_deptno NUMBER:=10;
l_count NUMBER;
l_ename varchar2(20);
BEGIN
SELECT count(*) OVER(order by empno) ,ename INTO l_count,l_ename FROM emp WHERE ROWNUM=1 and deptno=l_deptno;
dbms_output.put_line(l_count||' '||l_ename);
end;

View 7 Replies View Related

PL/SQL :: Single Row Query Returns More Than One Row?

Mar 12, 2013

this is my sql=
" SELECT 

hpn.id AS id,
hpn.psn_id,
hpn.last_name,

[Code].....

"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,

but it returns error single row query returns more than one row, because one people can have more that one card_number.

View 9 Replies View Related

PL/SQL :: Query To Find Single Row?

Oct 5, 2012

i have a requirement where i have to get the id who are only subscribed to only one course based on the below provided data.

ID Course
103812     CFH     
102968     REP     
103812     DFH     
102968     DFH     
103071     DFH     
102968     CFH

View 5 Replies View Related

PL/SQL :: Single Sub Row Query Returns More Than 1 Row?

Apr 19, 2013

I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.

I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'

Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:

PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..

So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?

I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?

For reference, here is the query.

update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')

View 5 Replies View Related

All Employee Types In Single Query

Jul 7, 2011

I have four employee types such as 'C' for consultants, 'S' for staffs, 'W' for workers ,, ('E','S','W') for all types of employees. i have write four queries for showing four types of employees. can it is possible in a single query.

I have written this in oracle forms . I have taken a list item for workers i have taken 'W' , for staffs i have taken 'S' , for consultants i have taken 'C' and for all i have taken 'A'. my column name is emp_type in( :block.list_item ) but it is not taking the value from the list item when the value is emp_type in('C') etc etc..

like this for workers ,staffs . when it is 'A' it will take emp_type in ('E','S','W')

View 3 Replies View Related

SQL & PL/SQL :: Convert Procedure To Single Query

Jul 26, 2012

I want to replace below multiple call to procedure with a Single query. Currently this proc is getting called multiple times from application.

FUNCTION f_get_shows_info(i_booking_wk_id IN NUMBER, i_screen_id IN NUMBER)
RETURN VARCHAR2 IS
v_act_shows booking_wk_screen.act_shows%TYPE;
v_expected_shows booking_wk_screen.expected_shows%TYPE;
v_return VARCHAR2(50);
BEGIN
SELECT NVL(act_shows, 1), NVL(expected_shows, 1)
INTO v_act_shows, v_expected_shows
FROM booking_wk_screen
WHERE booking_wk_id = i_booking_wk_id
[code]...

Is there anyway through which we can achieve this in Oracle 10g.

View 16 Replies View Related

SQL & PL/SQL :: Single Return Sub Query Returning More Value

Aug 21, 2013

i am trying to update below statement that has multiple rows but giving the error like :

update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))

error:- single return sub query return more value.

how to write update join query base on multi[ple ow.

View 8 Replies View Related

SQL & PL/SQL :: Drop All Views With Single Query

Jun 8, 2010

In scott/tiger user total 30 view tables are stored.Right now i want to drop all views with using query.

View 3 Replies View Related

SQL & PL/SQL :: Query - Get Details Of Each Employee In Single Row?

Jun 7, 2012

I have three tables as shown in the image. Need to get the details of each employee in a single row..

EMployee_id BaseSalary Bonus Hike shares

View 4 Replies View Related

SQL & PL/SQL :: How To Update Two Tables In Single Set Or Query

Nov 22, 2011

How to update two tables in single set or single query ?

View 8 Replies View Related

SQL & PL/SQL :: Multiple Selects In Single Query

Apr 10, 2012

how does this query execute? what kind of a query is this called?

mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;

+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)

View 8 Replies View Related

SQL & PL/SQL :: Incorporate Two Counts In A Single Query?

Sep 26, 2012

User table

id | name
----------
2 | Harry
3 | Mary

Course_User table

summer_course_completed | winter_course_completed | user_id | attendance
------------------------------------------------------------------------
y | n | 2 | 20
y | n | 2 | 40
y | y | 2 | 30
n | n | 3 | 20
n | y | 3 | 60

I wish to list each student's name with the number of summer courses he has completed and the number of winter courses he has completed. I am trying this :

select u.name, count(*)
from user u, course_user cu
where u.id=cu.user_id and cu.summer_course_completed = 'y'
group by u.id;

but I can get only the number of summer courses OR the number of winter courses that each student has completed, but never both simultaneously, through a single query. Is there a way to do that ?

View 7 Replies View Related







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