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


ADVERTISEMENT

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 :: Get More Lines Into One Line?

Jul 31, 2012

how to get more lines into one line per customer.

For instance:

CREATE TABLE XXX_MAPE_CC
(
accounting_month_key number,
customer_key VARCHAR2(18 BYTE),
total_amount VARCHAR2(29 BYTE)
)
insert into XXX_MAPE_CC
values (201205, 313774201, '15,03')

[code]...

And I would like get every total_amount to individual columns by accounting_month_key

--------------------------------------------------------------------------
customer_key month_201205 month_201206 month_201207
----------------------------------------------------------------------------
313774201 15,03 10,03 13,10
----------------------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: Decode On One Line

Jul 28, 2010

I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.

What do I have to do to get the data on one line? My code is as follows:

SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Line Of Best Fit / Linear Regression

Jul 25, 2012

I am trying to get a line of best fit for a set of data in oracle

TERM Count
TERM_080178
TERM_080258
TERM_08030
TERM_080482
TERM_08050
TERM_08060
TERM_080732
TERM_08080
TERM_090192
[code]...

View 4 Replies View Related

SQL & PL/SQL :: Run Command And Connection In One Line

Dec 5, 2011

I want run sql connection and command in one line , something like this ...

sqlplus "/as sysdba" 'select * from dual'

is it possible to do it in one command ?

View 3 Replies View Related

SQL & PL/SQL :: Query Result In One Line

Aug 29, 2011

I have a select that return the query like this:

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br

so,I need the query result in just one line...

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br

I saw some exemples of "pivot query" but I still looking for a solution.

View 10 Replies View Related

SQL & PL/SQL :: Can't Use PUT_LINE As This Will Put NEW_LINE At End Of Line

Sep 30, 2009

I'm writing following block,

Begin
str := '...'
....
f1 := utl_file.Fopen('EXT_REP_DIR',b,'W',32767);
utl_file.Put(f1,str);

Loop
....
utl_file.Put(f1,str);
End Loop;
utl_file.Fclose(f1);
End

There would be around 100000 records and I want everything in 1 line(i.e no line breaks). I'm getting error ORA-29285, WRITE_ERROR...By using PUT_LINE, the size of the file is for 10000 records is 3035542. But, I can not use PUT_LINE as this will put NEW_LINE at end of line.

View 4 Replies View Related

SQL & PL/SQL :: How To Combine 3 Decodes Together On One Line

Mar 4, 2011

I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:

ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37

if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC

[Code]....

View 7 Replies View Related

SQL & PL/SQL :: ORA-02063 - Preceding Line From?

Sep 16, 2011

I am getting the below error when executed the query below in production database.The database version of both the database is 10.2.0.4

ORA-01008: not all variables bound
ORA-02063: preceding line from DBLINK.PROD.NIC.CMS

select count(*)
from order_tbl@DBLINK.PROD.NIC.CMS a
group by a.col1
having count(distinct a.col2) > 1

the same sql is working fine in dev environment.Is this a bug?

View 12 Replies View Related

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

Missing Right Parenthesis All In Line 2?

May 22, 2011

CREATE TABLE LOCATION(
Locid NUMBER (5) LOCATION_LOCID_PRIMARY KEY,
Bldg_Code VARCHAR2 (10) NOT NULL,
Room VARCHAR2 (6) NOT NULL,
Capacity NUMBER (5));
CREATE TABLE FALCULTY(

[code]...

View 6 Replies View Related

Missing Right Parenthesis Error On Line 7

Mar 10, 2012

I'm working on my database homework, and I came across an error that I cannot seem to fix. I'm getting a missing right parenthesis error on line 7, but I seem to have all my parenthesis, endless I'm overlooking one.

DROP TABLE RoomDM CASCADE CONSTRAINTS PURGE;
1 CREATE TABLE RoomDM (
2 rNo VARCHAR(4),
3 hNo NUMBER(3),
4 type CHAR(6) NOT NULL,
5 price NUMBER(3,2) NOT NULL,
6 CONSTRAINT RoomDM_PK PRIMARY KEY (rNo, hNo),
7 CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
8 REFERENCES HotelDM (hNo) ON DELETE CASCADE,
9 CONSTRAINT RoomDM_type_CK CHECK(type IN(single, Double, Family))
10 );

The error looks like this:

CONSTRAINT RoomDM_hNo_FK FOREGIN KEY (hNo)
* ERROR at line 7:
ORA-00907: missing right parenthesis

View 2 Replies View Related

Loader Skip Blank Line?

Mar 21, 2012

I am running Oracle 9 and using sql loader to import text file into table. Can sql loader skips the record which blank line or carriage return? Do I need to set up with options?

View 1 Replies View Related

Displaying Result In Single Line

Sep 18, 2010

I have a table test_test

Name Mark
------- ------
ABC 10
DEF 10
GHI 10
JKL 20
MNO 20
PQR 30

The result should be

mark count names
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR

View 3 Replies View Related

Sqlplus And MS Windows Command Line

Feb 18, 2013

I have some Oracle Databases and sometimes for me is easier to access to the databases with SQLPLUS instead SQLDEVELOPER or TOAD.

But i can't reach to a good configuration of command line prompt to do queries against the database, normaly if it returns a lot o rows and columns it is unreadable.

View 5 Replies View Related

SQL & PL/SQL :: Replace New Line Character In Clob

Sep 2, 2010

Replacing a new line character in clob column i Oracle 10g.I have tried replacing the new line using regexp_replace,replace and translate. I have also tried using lob_relace procedures.

View 2 Replies View Related

SQL & PL/SQL :: Results Of Query To Be Displayed In One Line

May 19, 2010

I have this query:

select distinct event_number from events_total WHERE event_id = 16395493
minus
select distinct event_number from event_details_ford WHERE event_id = 16395493

result of which is :
6L2Z-7861693-AAC
6L2Z-7862187-CAC

i want to put this in dynamic sql where clause :

where event_number in ('6L2Z-7861693-AAC','6L2Z-7862187-CAC'). and if the result of the query is only one number, then where event_number in (6L2Z-7861693-AAC) result of the query can be NULL also. but i think i can use IF condition for "SELECT ..WHERE event_number in " query

so how can i put the results of query in one line, so that i can use it in where clause.

View 12 Replies View Related

SQL & PL/SQL :: UTL_TCP Returning Blank Line?

Feb 8, 2010

I am trying to use utl_tcp to connect, through TCP/IP, to a 3rd party program PC Miler. When I use telnet, PC Miler works without any issue. However, when I use utl_tcp, beginning from the second PC Miler API call, when I do a utl_tcp.get_line to get the result, the first line that return is always blank (with 1 white space). Here is my program.

---------------------------------------------------
DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

Note that the 1st and 3rd API call is the same and should give me the same results. The 2nd API is supposed to give me 1 line back. At the end of each output, the server will end will the word "Ready". I use that to indicate when to exit the loop.

If I disconnect and reconnect after each API call, each output result will be correct because each API call will become the "1st" call since connection - like this program:

DECLARE
c utl_tcp.connection;
ret_val PLS_INTEGER;
v_data VARCHAR2(4000);
BEGIN
c := utl_tcp.open_connection('mars', 8145);

[code]....

If there is something wrong with PC Miler, why would it work when I telnet through a Windows Command prompt? If it is not PC Miler and utl_tcp, what else can be wrong?

View 8 Replies View Related

SQL & PL/SQL :: Update Statement With NEW LINE Character?

Feb 14, 2005

My Table "demo" has a column "Combined_attributes". I want to update this column with some value having a new line character in it.

View 3 Replies View Related

SQL & PL/SQL :: Search New Line Values In Column?

Jun 11, 2012

I have a column in a table say ename in emp table, which allows values with newlines and carriage return.

I need to find all the values (i.e ename's) which are having newline chars or carriage return.

View 4 Replies View Related

PL/SQL :: How To Reset Line Number Value For Each Header

Jul 1, 2013

I need to reset the line number for each header values.

create table header_table(header_value varchar2(100));
create table line_table(header_value varchar2(100), line_number number);
insert into header_table values('ALAOF');

[Code]....

But My line table output looks like below

LINE TABLE:

header value line_number

ALAOF 1
ALAOF 2
ALAOF 3

[Code].....

View 6 Replies View Related

PL/SQL :: Getting Error ORA-6502 - Numeric Or Value At Line 11?

Apr 13, 2013

declare
type idstab is table of employees.employee_id%type;
type nametab is table of employees.last_name%type;
ids idstab;

[code]...

View 4 Replies View Related

Execute Command Line Program Within Java?

Jan 10, 2012

I've created a Java class in my Oracle DB that calls a Visual Basic program to convert a XLS file into a CSV file in order to load it into an external table. The problem that I have is that when I call the Visual Basic program from the Java class, nothing happens. I had the same problem with a Python program, and I thought that the problem was from Python, but now with Visual Basic the problem remains, both aren't executed.

The strange thing is that when I call the same Java class outside Oracle, directly from a command line, it executes both Python and Visual Basic programs.

Here is the Java class defined in Oracle:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "OSCommand" AS
import java.io.*;
public class OSCommand{
public static void Run(){
try
{

[code]....

And here is the procedure that calls the Java class:

create or replace
procedure run_os_command
as language java
name 'OSCommand.Run()';

View 1 Replies View Related

Reading Specific Line In Util File

Jul 1, 2013

I would like to read specific line in the file in the util file.

View 1 Replies View Related

How To Get Line And Error Code From EXECUTE IMMEDIATE P_SQL

Nov 19, 2010

I'm trying to write my own application ( in Delphi) which should be work similar to the Oracle SQL Developer. I received the body of the selected stored procedure by select * from all_users where type='PROCEDURE' and NAME='name_of_the_selected_proc'

Next, I put the body of the procedure into richedit component, make necessary changes, then put the corrected body into the variable ( varchar2) which is the input parameter of my stored procedure PW_DO_IT

create or replace PROCEDURE PW_DO_IT(P_SQL in varchar2)
as
begin
EXECUTE IMMEDIATE P_SQL;
end;

Everything works fine , the only problem is that I don't know how to get the line in which simulated error occured and its details. The only thing I get is the ORA-24344: success with compilation error

View 4 Replies View Related

Oracle Reports Blank Line In Output

Feb 21, 2008

i have a problem with oracle reports 6i. the output has 1000s of lines. in my output first to 999th line is fine..bt then 1000th line is blank and again the data continues to be shown from 1001th line till 2000. and again i see a blank. how can i get rid of these blank lines very 1000th line.

View 3 Replies View Related

SQL & PL/SQL :: ENTER (line Break) In Name Field Column?

Jun 12, 2013

There is a problem with name field like ex:

ENTER(line break) between 'HHD DDD PRIVATE' and 'LTD.

how to put a check in code to avoid these type of scenarios.

View 4 Replies View Related

SQL & PL/SQL :: How To Go Ahead Of Another Line Of Stored Procedure If Value Is Not There In Table

Oct 16, 2012

i am running one store procedure and checking the value if it is there then it's storing in one variable. but by checking through breakpoint i got that if value is not there which condition it's checking it's stuck there.

is it not possible if value is not there it should ignore and don't store value in variable.

my query is like this:

BEGIN
SELECT 1 Into v_temp FROM MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate AND STATUS='A';
IF v_temp = 1 THEN
BEGIN
DELETE MASTERPROCESSDAILYDATA WHERE Emp_ID = v_ReadEmpID AND PDate = v_ReadPDate;
END;
END IF;
End;

View 14 Replies View Related

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







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