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

SQL & PL/SQL :: How To Incorporate SUBSTR Into The Query

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

Forms :: How To Incorporate Unicode Font With 10g

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

SQL & PL/SQL :: Incorporate Parallel Loading To Load Data Into Database - Oracle Locking Not Working?

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

Using GROUP BY To Get Counts

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

Mismatch In Counts

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

Getting Different Record Counts When Select Overall?

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

PL/SQL :: Comparing Two Counts Using Subquery?

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

PL/SQL :: Counts On A Partitioned Table?

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

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

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

SQL & PL/SQL :: Get Row Counts Of Tables In Schema Without Using Num_rows

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

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 :: 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 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

PL/SQL :: Single Query Return Multiple Value

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

SQL & PL/SQL :: Show Same Record Twice With A Single Query?

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

SQL & PL/SQL :: Single Quote(') In Query Not Working?

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

Forms :: Checking For Password Verification For 3 Counts?

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

SQL & PL/SQL :: Query On Single Column To Display As Rows

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

SQL & PL/SQL :: ORA 01427 - Single Row Sub Query Returns More Than One Record?

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







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