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
ADVERTISEMENT
Aug 19, 2010
This piece of code returns this below:
RPAD(LPAD(' ',12, ' ') || SUBSTR(adj_second_line,(INSTR(adj_second_line,'~',1,1) + 1),
(INSTR(adj_second_line,'~',1,2) - INSTR(adj_second_line,'~',1,1)
- 1)),80,' ') ADJ_SECOND_LINE
Current Output ( ADJ#1-2M1YE2 TYPE 20100524 20100624 1MO/0DY )
===============================================================================================
1) I would like to alter that code above and substr the back 68 characters only with the 12 spaces padded in front, and the trailing nulls to total 80 characters staying as is. How would I put the below substr into that piece of code above?
Desired Output ( 20100524 20100624 1MO/0DY )
SUBSTR(adj_second_line,LENGTH(adj_second_line)-69)
===============================================================================================
2) I also would like the take whatever else there is preceding the 69th character counting it from the back and calling the column ADJ_FIRST_LINE.
Desired Output2 (ADJ#1-2M1YE2 TYPE)
No leading or trailing nulls and This is not a fixed amount of characters.
===============================================================================================
Result would be:
ADJ_FIRST_LINE
-----------------
ADJ#1-2M1YE2 TYPE
ADJ_SECOND_LINE
---------------
20100524 20100624 1MO/0DY
===============================================================================================
View 10 Replies
View Related
May 12, 2008
I am using 10g Database and 10g forms/reports on Windows.
NLS_LANG = AMERICAN_AMERICA.AL32UTF8 for both database and forms/reports. I set this from REGEDIT.
I want to use, Arial Unicode Ms or Mangal font to add or view the data in MARATHI (Regional).In the database browser, I can see the ported data properly with these two fonts, but through form if I try to add then it not showing proper characters, it showing squraes instead of typed characters.
I also typed a label in the form, at design time it showing proper word, but at runtime it not showing proper letters. This I observed only for joined characters.If I generates the report in PDF, format then it not showing data properly. but in runtime it showing properly.
View 15 Replies
View Related
Aug 3, 2011
At the moment, we were loading the file in our system serially. This is a very old and established system.We would like to incorporate parallel loading for our loaders to load data into the database.
Most of the issues would be due to multiple inserts happening due to the files being loaded in parallel. For some reasons, we cannot give regular commits untill the entire batch of items is processed in case the process needs to rollback. A file can contain different set of batch of items clubbed together for loading.
The issue here is untill the first file finishes loading and commits, the second file would just hang. In fact, mulitiple files might hang for the first file to finish. what can I do to overcome this?I tried to used "lock table t1 in SHARE ROW EXCLUSIVE mode nowait". When the leading process is doing inserts, the failing process will fail with a resource busy and acquire with NOWAIT specified. We would catch this exception and redirect that batch to an error file to be reloaded at a later date.
View 15 Replies
View Related
Jan 22, 2008
how to logically connect two tables. Here is an example of what I'm trying to do:
HOSTS TABLE CPU TABLE
----------- ---------
ID HOST ID CPU
01 host1 01 proc01
02 host2 01 proc02
03 host3 02 proc01
02 proc02
02 proc03
02 proc04
03 proc01
Based on the above, I can see that 'host1' has 2 CPUs, 'host2' has 4 CPUs, and 'host3' has 1 CPU. What I'd like to do is create a query that would output:
HOST CPU
----------
host1 2
host2 4
host3 1
I'm looping through the hosts and passing the them into another query as bind variables. That's slow and cannot be the best way to do this. I'm aware that I need to using a GROUP BY HAVING COUNT, but that doesn't seem to be working. It tends to return the total count of all CPUs rather than CPUs per host.
View 2 Replies
View Related
Jan 28, 2012
I have taken the export backup of a table using the following command
userid=system/manager@DBATEST
file=abc.dmp
log=abc.log
Tables=X.S_ORG_EXT_X
feedback=100000
buffer=10000000
[code]......
but on querying the table it shows the number of rows as
select count(*) from X.S_ORG_EXT_X;
COUNT(*)
----------
25656051
Explain why there is a mismatch in the count of the rows in the table.
View 1 Replies
View Related
Nov 7, 2011
I have a UNION query having 3 parts, 1st gets date, 2nd data and 3rd displays the formatted data count :WHERE clause of 2nd and 3rd queries are same.
Problem is that I an getting different record counts when I select the overall count of records given by the whole UNION query and when I run to count the records given by each query individually.First count. Here I am selecting the overall count of records given by the query :
select count(1)
from (
SELECT SUBSTR ( '0'
|| TO_CHAR (SYSDATE, 'MM/DD/YYYY')
|| TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)
[code]...
This count is : 1751525 Second count. Now when I run to count the records given by each query individually, here is the result
select count(1) a
from (
SELECT SUBSTR ( '0'|| TO_CHAR (SYSDATE, 'MM/DD/YYYY') || TO_CHAR (SYSDATE, 'HH:MI:SS')
|| LPAD (' ', 180)|| chr(13), 1, 200 ) dtl_record from dual
select count(1) b from (
SELECT '1'
[code]...
why there is difference of 1 (1751526 - 1751526) in the count results.
View 1 Replies
View Related
Feb 10, 2013
Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
Eg:
R(x,y)
Select count(x)
from R
group by x;
Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
View 6 Replies
View Related
Apr 26, 2013
We have a partitioned transaction table in our Datawarehouse environment which has the following partition strategy
SCHEME=DATE-HASH
GRAIN=DAILY
SUBGRAIN=NONE
HASH=8
FROM=31/12/2011
We recently had to delete data from the table. This was a simple delete statement with a where clause and without taking into consideration any partition/subpartition clauses. Post committing the delete we have a count mismatch problem with two queries in particular
select count(0) count_without_parallel FROM TRANSACTION_TABLE t;
--THIS RETRIEVES *15774811* ROWS
select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t+
--THIS RETRIEVES *15777617* ROWS WHICH IS THE ACTUAL EXPECTED COUNT.
I also ran the following just to summarize
select (select count_with_parallel from (
select /* parallel(t,default) */count(0) count_with_parallel FROM TRANSACTION_TABLE t))+
-
+(select count_without_parallel from (+
select count(0) count_without_parallel FROM TRANSACTION_TABLE t)) as false_difference
from dual;
The difference in *2806* rows as expected.To re-affirm my counts I ran
select /*+ parallel(t,default) */
'count_on_t',count(*) from TRANSACTION_TABLE t
group by 'count_on_t'
order by 1;
--THIS RETRIEVES *15777617* ROWS
Removing the parallel hint reverts back to the lesser count. Not sure what is wrong but something prevents the query from parsing the whole table and/or partitions and subpartitions.
View 0 Replies
View Related
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
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
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
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
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
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
Aug 9, 2010
I am trying to get a row count(*) for all the tables in my schema. The NUM_ROWS column in DBA_TABLES is not appropriate in this case because they are as good as the last analyze. So I need to get real time counts.
I tried the following code but I can't seem to catch my error.
DECLARE
l_sql varchar2(150);
cursor tablelist is
select table_name from dba_tables where owner = 'ME';
[Code]....
My expected results are :
TABLE_NAME ROW_COUNT
---------- ----------
View 13 Replies
View Related
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
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
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
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
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
View Related
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
Nov 22, 2011
How to update two tables in single set or single query ?
View 8 Replies
View Related
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
Sep 6, 2012
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
View 3 Replies
View Related
Sep 24, 2012
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.
View 7 Replies
View Related
Jan 5, 2013
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 '?'.
View 4 Replies
View Related
Dec 22, 2011
I have made a simple form in which user will be entering his username and relevant password to get authorised. My issue is , if the user is entering wrong password for 3 times then both fields should get disabled. I did the form until the point where it checks wether the user is authorised or not. But how to give count on failure of passwords.
View 5 Replies
View Related
Aug 5, 2010
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.
View 10 Replies
View Related
Oct 10, 2013
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.
View 3 Replies
View Related