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
My query is "How to find all special characters like (%$*&@,;'/+- etc. in a single query?"
e.g.
A_MIR
A%SIM
A*SIM
A)SIM
In literal terms, I'm interested in two fields, I'll call them Field A and Field B. I want to find all situations where a single unique value of Field A has both values Z AND X in Field B (not either or, but both together).
To go into some detail -
I need to make a query that finds ONLY occurrences where one employee id has a certain set of values together (without going into specifics, I'll say PermissionA, PermissionB, PermissionC). I can easily make a query that returns all user id's and all permissions belonging to them, and I can use criteria to filter the results to Permission A B and C only so as to exclude other permissions from getting returned (since there are hundreds),
however my objective is to get ONLY results where the same employee ID has all of those permissions (not just any one or two of the three). However, I don't want to have any criteria that limits the employee ID (I want to search all employee id's, and get a list of those with permissions a and b and c, not just any combination thereof, but all of them). I'm currently able to organize the output using a pivot table by employee id > role,
so that I can easily look at each employee and the roles they have, but I want to undertake a project that will involve searching a much larger number of employees (a # that makes it impractical for me to look through the list, I need to have a query that limits the results to the combinations that I'm looking for, as in this example - permission a b and c together).
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.
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;
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.
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')
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 RelatedI 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')
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.
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.
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 Relatedi 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]...
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
How to update two tables in single set or single query ?
View 8 Replies View Relatedhow 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)
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 ?
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
I have table say Messages. In which there is a column msg_text varchar2(900).My requirement is to fetch the very last character of the msg_text for a single row identified by its msg_code(primary key).
The problem is, whenever msg_text contain second last character as single quote( ' ), it doesn't give me the last character i.e. after the single quote.For example if msg_text is "Congratulations, you opted for 'A'." and if its message_code is 10 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 10;
returns nothing.
Whereas if msg_text is "Are you sure to continue?" and if its message_code is 20 then query
SQL> SELECT SUBSTR(msg_text,LENGTH(msg_text),LENGTH(msg_text)) AS LAST_CHAR
FROM messages
WHERE msg_code = 20;
returns character '?'.
I has a table of structure of varchar2 datatype.
NO
----------
1-2
3-4
5-6
desired output is:
SQL>1
2
3
4
5
6
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.
update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";
I understand that problem is there with inner select query.
How can we get 'space in every character of a string with Single select query'
for example:-
string 'INDIA'
result should be 'I N D I A'
I have a employee table with the below format
EMP
EMPID DEPID
1A
2A
2B
1B
4A
5B
5A
6C
7A
8D
Now I need to start with the employee 1 . As we can see the emp 1 is mapped to dept A and also with multiple dept. Similarly dept id is mapped to multiple emp.
I need the output as below (many to many I.e. cross referencing)
EMPID DEPIDGroup
1A A1
2A A1
2B A1
1B A1
4A A1
5B A1
5A A1
6C B1 -- new group as the emp and dept are not mapped previously
7A A1
8D C1
I have a below requirement let me know the optimized query.
Create table test_123 ( sr varchar2(1000));
Insert into test_123 values ('1,2,3,4,5');
Insert into test_123 values ('6,7,9,10,11');
Insert into test_123 values ('9,2,3,8,5');
I need output as :
Sr_output
1
2
3
4
5
6
7
8
9
10
11
I need all the values in the column sr in a single column and mutiple rows.
How to update two table column in single query ?
example :
update table1 t1 ,table2 t2
set t1.column = 'Yes',t2.column='Yes'
where t1.emp_code =t2.emp_code ;
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
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
I'm using Oracle 10g.
Question: How can I write query to return just ID only if all the codes for that ID end in 6.
If I use
SQLselect id from table_a where code like '%6'
then I also get ID=1, which I don't want.
TABLE_A
ID code
=======
1 100
1 106
2 206
3 306
3 336
4 400
Desired Result
ID
==
2
3
I have table T with 50,000 rows
create table T
(student_id number,
class_id number,
quiz_id number,
marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50);
INSERT INTO T VALUES (2,2, 2, 40);
INSERT INTO T VALUES (3,1, 3, 34);
INSERT INTO T VALUES (1,1, 4, 10);
INSERT INTO T VALUES (1,1, 5, 30);
INSERT INTO T VALUES (1,1, 6, 29);
INSERT INTO T VALUES (3,2, 7, 34);
INSERT INTO T VALUES (3,2, 8, 33);
INSERT INTO T VALUES (3,2, 9, 56);
INSERT INTO T VALUES (1,1, 7, 90);
INSERT INTO T VALUES (2,2, 8, 0,);
INSERT INTO T VALUES (1,1, 8, 80);
INSERT INTO T VALUES (2,2, 8, 65);
INSERT INTO T VALUES (1,1, 9, 34);
INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
SELECT QUIZ_ID,
STUDENT_ID,
SUM (MARKS)
[Code]....
SQL> /
QUIZ_ID STUDENT_ID CONSECMARKS
---------- ---------- -----------
7 1 170
6 1 166
8 1 129
5 1 106
8 3 89
8 2 76
3 3 68
7 3 67
8 2 65
1 1 60
9 3 56
9 1 49
2 2 40
4 1 40
9 2 11
15 rows selected.
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
my sample output is:
Nth consecutive quiz student_id sum(marks)
1 1 90
2 1 170
3 1 246
4
.
.
.
100
.
.
200
.
.
300
.
400 ? ?
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.