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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Mar 12, 2011
how to Completely remove Oracle 11g from my computer ?
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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