Remove Count In First Line

Nov 27, 2009

My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students

SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num)
From Student s left outer join Student s2
on s2.mentor_num=s.std_num
GROUP BY s.std_num, s.birth_date
ORDER BY s.birth_date ASC;

This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore

SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num)
FROM (SELECT s1.std_num, s1.birth_date, COUNT
(e2.super_id)
From Student s left outer join Student s2
on s2.mentor_num=s1.std_num
GROUP BY s1.std_num, s1.birth_date
ORDER BY s1.birth_date ASC) S
WHERE row_num<=5;

This code only works if I remove the count in the first line but I need to display the number in my result.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Remove Line Breaks In Select Statement?

Sep 9, 2013

I have a simple column in a table which store a description. This can include line breaks so when you select from it, it looks similar to below

'Part number: X
Batch: Y
Size: Z'

I would like to be able to create a view (so not having any impact on the original data) to return it as below

'Part number: X Batch: Y Size: Z'

View 3 Replies View Related

SQL & PL/SQL :: Email With Attachment / How To Remove Empty Line

Jun 22, 2010

I need to send email notifications with attachments. Below is my testing codes.

declare
text varchar2(32000);
crlf VARCHAR2(2) := chr(13)||chr(10);
begin
text := 'Test only'||crlf||'test 2'||crlf||'test 3';
UTL_MAIL.SEND_ATTACH_VARCHAR2(
[code]......

I notice that, there is an empty line at the top of the content of this file. I don't want the empty line to be included in my file.

View 4 Replies View Related

Client Tools :: How To Remove Dotted Line While Spooling

Jan 27, 2010

i have a issue that when i spool the sql command i get a doted lines below the heading. how to eliminate the doted line but need the heading.

Previous:
OCCUPACODE OCCUPADESC
-------------------- -----------------------------------
1 BUSINESS
2 SERVICE

Needed :
OCCUPACODE OCCUPADESC

1 BUSINESS
2 SERVICE

View 1 Replies View Related

Reports & Discoverer :: How To Remove Empty Line In Output

Sep 16, 2011

I am getting an empty line after every 988 records in the output. There are no pagebreaks defined in the layout.

This is a "|" delimited report.

View 2 Replies View Related

Forms :: Get Count Of Line From Client_Text_Io.Get_Line

Mar 14, 2012

how to get the count from Client_Text_Io.Get_Line??

For Example :
Client_Text_Io.Get_Line(vOutFile,voutput);
vBaris := voutput + 1;
If vBaris = 3 then
v_reg := SUBSTR (voutput, 1, 3);
tanggal := SUBSTR (voutput,1, 4);
v_outlet := SUBSTR (voutput, INSTR (voutput, ',', 4) + 1, 3);
v_refer := SUBSTR (voutput, INSTR (voutput, ',', 6) + 1, 4);
[code]....

View 1 Replies View Related

Count Number Of Lines In Multi Line String

Aug 2, 2007

I have a string like this:

s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';

How can I find the number of lines in this string? I tried using

INSTR('s_list', '
', 1, 1)

but it gives 0.

Is there any inbuilt function/proc SQL or PL/SQL which can do this?

View 2 Replies View Related

PL/SQL :: How To Make SQL*Plus Count Comment And Blank Line Numbers

Jul 3, 2013

 ParameterParameter valueOracle versionEnterprise Edition Release 11.2.0.1.0 - 64bitOSLinux Fedora Core 17 (X86_64) 

is there any way to force SQL*Plus to provide the actual line number in the source file whenever there is an error? I often put at the first line of my script SET SQLBLANKLINES ON so that I may be able to put several consecutive blank lines lines in my code (I do this sometimes, when I find it to be appropriate in order to make my code more readable or to group a sequence of instructions which I believe make their logical link more understandable for the reader) Now the problem is that SQL*Plus ignores these blank lines and whenever there is an error, the line number provided in the error message, doesn't correspond to the actual line number in the source file but it seems to be the last non blank line in the file.  Consider the following example: 

SET SQLBLANKLINES ON;DECLARE    var PLS_INTEGER := 10;BEGIN        var := 20END;/  In the above code at line 9

(by counting also the blank lines) there is an error (no semicolon at the end of var := 20) but when I run the script inSQL*Plus  here is the error message that I get  

SQL> @myscript.sql;END;*ERROR at line 8:ORA-06550: line 8, column 1:PLS-00103: Encountered the symbol "END"

when expecting one of the following:* & = - + ; < / > at in is mod remainder not rem<an exponent (**)> <> or != or ~= >= <= <> and or like like2like4 likec between || multiset member submultisetThe symbol ";" was substituted for "END" to continue.SQL>   So as you can see the error message indicate that the error was found at the line 8 whereas in the file it is really at the line 9 As long as the number of lines in the script is limited, this may not be a problem and one can find rapidly the actual line number in the code raising the error but for a code including hundreds (or even thousands) of lines and with many comments and blank lines, finding the actual line number given by SQL*Plus error message becomes tricky. Is there any way to make SQ*LPlus trace correctly the line numbers as they appear in the source file?

View 5 Replies View Related

Client Tools :: Change Separator Line Of Compute SUM Or Count?

Jul 9, 2013

Is that possible to modify the ***** and ----- in the TEXT report?

DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
************* ********** ----------
sum 13000

From above lines, you can see ****** and ----- which are above sum&13000.

Is that possible to change them as '='?

DEPARTMENT_ID JOB_ID LAST_NAME SALARY
============= ========== ========================= ==========
20 MK_MAN Hartstein 13000
============= ========== ==========
sum 13000
"set underline =" is only shot the headers.

View 3 Replies View Related

Spatial :: Convert Multi-line To Simple Line

Feb 13, 2013

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We are processing spatial data from another source to display in our GIS environment. The data is a set of multi lines. The gtype is 2006. A typical geometry looks like:

SDO_GTYPE     2006
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1, 7,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96,
105094.84, 195084.96,
105094.68, 195082.47 )

Now, this is not an actual multiline... it's just encoded as a multi line, but if you look at the coordinates you'll see that the end point of the first line is the same as the beginning of the second line (105094.84, 195084.96).

A better way to encode this geometry would be:

SDO_GTYPE     2002 <---
SDO_SRID      31370
SDO_POINT.X   NULL     
SDO_POINT.Y   NULL
SDO_POINT.Z   NULL
SDO_ELEM_INFO (1,2,1)
SDO_ORDINATES (105094.84, 195084.96,
105094.54, 195080.22,
105094.84, 195084.96, <---
105094.68, 195082.47 )

Is there a standard function in Oracle that does this conversion for me? Or do I have to write my own :)

View 4 Replies View Related

SQL & PL/SQL :: Difference Between Count(*) And Count(1)?

Nov 16, 2009

When we execute select count(*) from table_name it returns the number of rows.

What does count(1) do? What does 1 signifies over here? Is this same as count(*) as it gives the same result on execution?

View 13 Replies View Related

SQL & PL/SQL :: Difference Between Count(1) And Count(*)

Nov 24, 2011

difference between count(1) and count(*). As i know count(*) will give number of rows irrespective of null and count(1) will not count the null.My Oracle version is 10 g.

SQL> select * from t1;

A B C
---------- -------------------- --------------------
1 2 3
2
5

SQL> select rownum,a.* from t1 a;

ROWNUM A B C
---------- ---------- -------------------- --------------------
1 1 2 3
2 2
3 5
4
[code]....

View 3 Replies View Related

How To Remove Fragmentation

Sep 24, 2012

I have table of 60 gb(indexes 60gb ) and it is subject to fragmentation around 10gb .

I m going to remove fragmentation . As i know i have three options

1.expdp/impdp .
2.CTAS (create table ........as.......) with parallel option .
3.Moving table into another tablespace with parallel option .

I have 4 physical and 4 logical cores(total 8 cores) on server .

View 1 Replies View Related

SQL & PL/SQL :: Remove Only One Leading 0?

Jun 3, 2010

I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:

CREATE TABLE leading_0_test
(
col_1 VARCHAR2(10)
)

insert into leading_0_test values('00123')
select trim(leading'0' from COL_1),trim(leading'0' from substr(COL_1,1,1)),COL_1 from leading_0_test

The first trim removes all leading zeros. Therefore I tried to solve it with the substr, but without success.

View 7 Replies View Related

SQL & PL/SQL :: How To Remove Space

Oct 20, 2010

query string in such like that index.php?name=tejaspatel

i have table in record is available below

select * from emp where name = :name // it is query string parameter

emp
name
tejas patel

then how to match this record ?

View 8 Replies View Related

PL/SQL :: Get Line With Max Value

Jul 18, 2012

I have a table

Line_num Amount
1 10
2 20
3 30

create table Test (line_num number, amount number);
insert into test values(1,10);
insert into test values(2,20);
insert into test values(3,30);

I need to get the line number which has a maximum amount.

View 2 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related

SQL & PL/SQL :: Get Count On Group And Total Count For Each Group

Mar 23, 2013

I'm using this code, and it performs fine, but I'm wondering if there is a more elegant way to do it--maybe with "ROLLBACK". Basically (as you can see) I need to get a normal count for each group but also for each group take a percentage of the total count (so all groups pct adds up to 100 (oh yeah, don't test for zero below, but just a test... )

select
c.Event,
c.code,
count(1) as calls,
total.total_count,
count(1) / total.total_count * 100 as pct_of_total
from
table1 c

[Code]....

[Edit MC: add code tags, do it yourself next time]

View 4 Replies View Related

SQL Statement To Remove Extension

Oct 15, 2008

I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.

View 1 Replies View Related

How To Completely Remove 11g From Computer

Mar 12, 2011

how to Completely remove Oracle 11g from my computer ?

View 2 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

Install And Remove Oracle 10 G

May 26, 2008

I install oracle 10 g but I don't remember password . so I then remove oracle 10 g by select Universal Installer and click Deinstall and then reinstall but I can't reinstall because I don't remove/delete oracle_home I then delete folder in path C:Oracle and reinstall Question

1.in this remove oracle 10 g method true or wrong if wrong , How effect on my computer
2.why after reinstall my computer is very slow.

View 5 Replies View Related

SQL & PL/SQL :: How To Remove Duplicates From Collection

Mar 24, 2011

remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.

Object creation

create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');

create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;

Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;

If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error

ORA-22950: cannot ORDER objects without MAP or ORDER method

View 4 Replies View Related

SQL & PL/SQL :: Remove String Duplicates

Oct 11, 2013

Removing duplicates from a string that contains years and "-".

Example: 1988-1997-2000-2013-1998-1965-1997-1899

I know this can be done in regular expressions but have no experience in this subject.

select REGEXP_REPLACE(.....) from dual;

View 16 Replies View Related

SQL & PL/SQL :: Remove Repeating Duplicates

Aug 3, 2011

i have two tables

Quote:
Table has following data.

id indicator
---------------
1 A
2 A
3 A

Quote:
Table2 has

id indicator
---------------
1 X
1 X
2 X
3 X

I would like to have following output ( Am running query on Toad)

t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X

View 4 Replies View Related

SQL & PL/SQL :: How To Remove Special Characters

Mar 22, 2013

I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.

How can i do that ?

i have a string with |R!$#&2-_D%2 and i want to get R2-D2

SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct
FROM DUAL

View 5 Replies View Related

Forms :: Remove Duplicate Value

May 28, 2012

how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.

declare
qry varchar2(5000);
n number;
alert number;
Begin
[code]....

View 8 Replies View Related

SQL & PL/SQL :: Remove Columns Which Contain Same Data

Jun 10, 2013

I do have a query which gives me two rows of data.(This might be more than two in other cases)

The data I get is formed as :

COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
European Patent EP 101 Mokilosu

What I want to get should be formed as :

COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT

European Patent EP 101 Kiksu
Mokilosu

As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.

The code I do use to get this data:

SELECT
TABLECOUNTRY.COUNTRYDESCRIPTION "COUNTRY_DESCRIPTION"
,TABLECOUNTRY.COUNTRYCODE "COUNTRY_CODE"
,OWNER.NAME "APPLICANT"
,CASEEVENT.EVENTNUMBER "EVENT_NUMBER"
,TO_CHAR(CASEEVENT.EVENTDATE,'DD.MM.YYYY') "EVENT_DATE"

[code]....

So what to modify in the query to get these columns of the second row blank?

View 17 Replies View Related

SQL & PL/SQL :: Remove / Trim More Than One Character?

Aug 15, 2010

How to write a pl/sql query to trim/remove more than one character from string.

Like the itemfield is 'Profit CY' I want it to show as 'Profit' but only for itemfields that say 'Profit CY' in the column for remaining items in column such as 'Loss CY' should stay as it is.

View 25 Replies View Related

PL/SQL :: How To Use REGEXP_REPLACE To Remove ORACODES

Dec 27, 2012

Basically I need to use REGEXP_REPLACE in order to remove from a string certain oracle code pricislly those which are used on RAISE_APPLICATION_ERROR from 20000 to 20999. We have an application here that handles those exceptions and each exception append each other so inside them we can find those ORA-20001: Some error. ]

So I was wondering if I can achieve that by using REGEXP_REPLACE. So which regexp I could use so that I can remove those oracode only!

View 6 Replies View Related







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