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
ADVERTISEMENT
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
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
May 16, 2007
In Oracle XE when I run PL/SQL in the SQL Commands window I will sometimes get a "Not found" error. It further reads, "The requested URL /apex/wwv_ flow.show was not found on this server". I found that if I remove all of the indentations from the lines of the PL/SQL code everything will work fine. Talk about a screwy problem. So, if you have that problem just take all of your PL/SQL code and slam it up against the left margin.
View 1 Replies
View Related
Sep 6, 2012
I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.
The query is :
Select DISTINCT ou.name||' Spare Parts' ORG, oh.order_number, trx.trx_number, trx.trx_date, p.party_name CUSTOMER, l.ordered_item,
/* ( Select Distinct it.description From MTL_SYSTEM_ITEMS_B it Where it.inventory_item_ID = l.inventory_item_id and it.organization_id = o.organization_id) Item_Desc, */
l.ordered_quantity, l.line_number,
l.inventory_item_id, l.unit_list_price , ----
a.account_number CST_NO ,
oh.Attribute1 MODEL , oh.Attribute2 SERL_NO, oh.attribute3 PAYM_MODE ,
u.user_name Prep_By, oh.CUST_PO_NUMBER PO_NUM,
[code]......
View 1 Replies
View Related
Oct 12, 2010
I got some records in the table between the sign / Some of these records are separated by / And I would like to get something like:
If exist / inside the string then display as lines as string between /
For instance:
select handset_brand, handset_model
from the_table;
display : handset_brand: Siemens
handset_model: A35 / A36 / A40 / B35N
I need to get 4 lines:
handset_brand: Siemens
handset_model: A35
handset_brand: Siemens
handset_model: A36
handset_brand: Siemens
handset_model: A40
handset_brand: Siemens
handset_model: B35N
View 31 Replies
View Related
Jun 6, 2012
formatting the mail message sent using utl_mail, i have created one table and it has around 17 rows inside as sample and it may increase as well, my present email format is in single line..find below the code i have written along with test case, you may substitute your emailid for test, especially my main focus is on the column mrk , where all the mrk are comming in one line but i want to be arranged in a line not more thant 10 rows, if it exceeds it should go to next line.
CREATE TABLE FAB_LOG ( MRK VARCHAR2(30))
INSERT ALL
INTO FAB_LOG VALUES ('1018017M-6001')
INTO FAB_LOG VALUES ('1018017M-6002')
INTO FAB_LOG VALUES ('1018017M-6003')
INTO FAB_LOG VALUES ('1018017M-6004')
INTO FAB_LOG VALUES ('1018017M-6005')
INTO FAB_LOG VALUES ('1018017M-6006')
[code]....
View 39 Replies
View Related
Nov 21, 2012
I'm struggling with a problem for quite a while now and can't find any solution online so far ...
OK I got three different Tables
cust_tbl:
customer:id
nuxx :1
naxx :2
baxx :3
suxx :4
[code]...
As you see, I only need the Customer one time followed by part, amount and address - separated hyphen - comma, next part, amount, address and so on ...I've tried the PIVOT way and STRAGG (wm_concat) but all failed so far.The main problem is that when one customer fits more than one condition, ora throws: ORA-01427: single-row subquery returns more than one row
View 9 Replies
View Related
Apr 4, 2007
When referencing a procedure during a trigger, can I split the parameters across multiple lines? Similar to a backslash in perl? I've written a simple send mail procedure and it works well, though the parameter list is large and I'd like to be able to format the code for readability, i.e.:
BEGIN
send_mail('from@domain.com', 'to@domain.com, ???
'Subject', 'Message');
END;
What would I replace ??? with to extend the procedure to the next line?
I know this sounds like a very elementary question, but I've yet to figure it out via queries on these forums or Google. Perhaps I'm not choosing the right words.
When creating the procedure, i was able to use || to extend the utl_smtp function parameters, but I get an error when using the same syntax during trigger creation.
View 3 Replies
View Related
Apr 8, 2008
I have some tables (below), but I'm having trouble entering multiple lines of data.Here are the tables I have created.
Quote: CUSTOMER table
SQL> create table customer
2 (customer_no char(6) not null,
3 name varchar2(30) not null,
4 address varchar2(50) not null,
5 credit_limit number(6,2),
6 constraint customer_customer_no_pk primary key (customer_no));
Table created.
SALESMAN table
SQL> create table salesman
2 (salesman_id char(8),
3 name varchar2(20) not null,
4 address varchar2(50) not null,
5 emaill_address varchar2(30),
6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created.
ITEM table
SQL> create table item
2 (ISBN char(13) not null,
3 title varchar2(30) not null,
4 price number(4,2) not null,
5 constraint item_ISBN_pk primary key (ISBN));
Table created.
INVOICE table
SQL> create table invoice
2 (invoice_no char(1) not null,
3 invoice_date date not null,
4 salesman_id char(8),
[code]...
Table created.
DELIVERY table
SQL> create table delivery
2 (invoice_no char(1) not null,
3 ISBN char(13) not null,
[code]...
Table created. I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
Quote: ORA-00001: unique constraint(SYSTEM.INVOICE_INVOICE_NO_PK) violated
I understand that I can only use invoice_no once since it is a unique key.
View 4 Replies
View Related
Oct 21, 2010
My issues is when I changes the data in Header block the detail block should corresponding data immediately related to header on the detail lines.
The form is based on master detail relationship.
View 7 Replies
View Related
Jun 23, 2010
if i execute
begin
insert into t values('hgahaha');
.......
....
<17000 times insert statement>
end;
I get following error
ERROR at line 1:
ORA-06550: line 16385, column 13:
PLS-00123: program too large
But instead of pl/sql block if i write the insert statements as sql file and execute it produces no error
Does it mean pl/sql has limit on number of lines of code to 16385?
View 10 Replies
View Related
Nov 12, 2012
i wanna copy data from a table to another one (same structure, only difference is that the second table is partitioned). the origin table consists of about millions of lines so i made an insert :
insert into table2 select * from table1. the insertion doesn't end correctly : TOAD crashes, any line was inserted in table2 : select * from ...etc.
However, the file system becomes full. i would if there is a way to purge something like cache ..., are these lines inserted somewhere : temporary table...,
so i can reduce filesystem size.
View 13 Replies
View Related
Jul 31, 2012
I would like to write a query on USER_SOURCE that can display the number of code lines for each procedure/function in a package. Is it possible to write such a query? Maybe by using analytical functions?
for example in the following example i would like to count the lines between
"PROCEDURE proc1 IS" and "END proc1;" and between "PROCEDURE proc2 IS" and "END proc2;"
SQL> select text from user_source where name='PKG_TEST' and type='PACKAGE BODY';
TEXT
--------------------------------------------------
PACKAGE BODY PKG_TEST IS
/*************************************************
****/
PROCEDURE proc1 IS
BEGIN
update t1 set EDITION_NAME = 'AAAAAAA';
commit;
END proc1;
[code]....
View 14 Replies
View Related
Apr 25, 2013
i need a chart with 4 lines and 2 y axis. 2 lines on the first y axis, 2 lines on the other y axis.First i tried to create 4 series and "multiple y axis", then i get the first line at one y axis, and all the other lines at the second y axis.
After that i have seen that it is possible to return more than one value within one series, so i create 2 series with 2 values each - i thought that then i will get the first 2 values at one y axis and the other 2 lines of the second serie at the second y axis... but sadly it is not like i expected - i get the same result as with 4 series.is it really necessary to create a custom xml for that?
here is my example: [URL] as you can see the blue and the red line are on different axis.the query of one serie looks like that (it gives the values of a day and the day before), the queries are ok.
select null link, to_char(a.ts,'DD.MM HH24:MI') label, sum(a.kw*100/12) over (
partition by trunc(a.ts) order by a.ts rows between
unbounded preceding and current row
) "KWh",
sum(b.kw*100/12) over (
partition by trunc(b.ts) order by b.ts rows between
[code]....
View 0 Replies
View Related
Apr 28, 2010
I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.
Code looks something like this:
/*
Custom Extract
Project: Plan Data Extract
Product: EOWin 4.02 - Oracle db
Use: Script to create above extract and spool results to text file
Input Parameters: &1 Path and name of output file
*/
[code]....
and the output looks like this:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
but the client and I want the output to look like this with no blank lines in between the queries:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
View 3 Replies
View Related
Aug 5, 2010
I have requirement of loading a part of the flat file that contains many headers and lines info. The program has to load the lines whose header recrd_type is 05 using SQL*LOADER.
eg of flat file.
Header
trans_code comp date rec_type
------------------------------------------------------------
8 12800002 0729201005
transcode_line acct date refrence
------------------------------------------------------------
4424604001002738307272010 24427330207710017569675
4424604001002738307272010 24427330207710017569675
View 7 Replies
View Related
Apr 18, 2013
I´m monitoring a 11g database with OEM. I have a couple of questions regarding the Average Active Sessions chart:
- What does the line 'CPU core' means? The DB is running in a virtual server with 8 CPUs. However, the line 'CPU core' is in 1. Does it mean that Oracle is just using 1 CPU?
- What represents the '99th percentile' line? The chart shows several sessions above that line, something is not working well, isn't it?
View 4 Replies
View Related
Feb 13, 2013
Customer sales order lines are fulfilled by IR-ISO. When line progresses, it is set to have Internal Req Requested status. A scheduled program runs amd IR are created. However, sales order line status does not change to Internal Req Open. It remains in Internal Req Requested status.
As a work around, workflow is rewound to 'Check ATR Qty' which is customized activity and then status changes to Internal Req Open. What could be the reason that workflow is not setting the status to Internal Req Open?
View 1 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
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
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
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
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
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
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
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
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
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
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
