if the same name repeating it should to append with _1 and _2 until same name reached.
select 'fname' name from dual
union all
select 'lname' name from dual
union all
select 'email' name from dual
union all
select 'fname' name from dual
union all
select 'fname' name from dualmy output should be like below...
fname
lname
email
fname_1
fname_2
I have a result set with the following structure
Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
A1 B1 20 50
A1 B2 30 40
But i want to restructure this result like below,
Column 1 Column 2 Column 3 Column 4
--------- --------- --------- ---------
A1 B1 10 50
20
B2 30 40
I am just trying to change the repeating values on a certain combination as blank.
some sample data in my point geometry table.
every POLYID has two rows with NAME value, i need to select the two rows if NAME is same for a given POLYID.
example: POLYID 4351 has same name N, then i need to select two rows with PILYID 4351.
POLYID POINTID NAME
-----------------------------------------------------------------
4348 5763 N
4348 5764 F
4351 5741 N
4351 5756 N
4367 5721 M7
[Code]....
I have these records in a table:
CODE AMOUNT DESCRIPTION
AAA 5 five dollars for pizza
AAA 2 two dollars for tips
AAA 1 one dollar for dogsitting
BBB 6 six dollars for babysitting
BBB 1 one dollar for tips
My goal is to list all records, "grouping" by code, with sum(amount), but the final display has to show all descriptions, one for row, avoiding to repeat the "CODE" column and "sum(AMOUNT)" column.The result should be like this:
CODE SUM(AMOUNT) DESCRIPTION
AAA 8 five dollars for pizza
two dollars for tips
one dollar for dogsitting
BBB 7 six dollars for babysitting
one dollar for tips
That is, the "CODE" is displayed only the first row, with its sum of "amount".I think I have to use the analytics functions, but I was a little stuck.
I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.
with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all
[Code]...
NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas
The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.
I need the sql syntax that would return this result set.
I am looking for oracle query to replace repeating description with -DO-. Sample Data is:
CREATE TABLE SCOTT.ITAX
(
VDATE DATE,
[Code].....
VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 CANOLA OIL 3 2500
10-DEC-12 CANOLA OIL 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 CANOLA SEES 3 5500
01-DEC-12 CANOLA SEES 3 5400
6 rows selected.
Required Output is:
VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 -DO- 3 2500
10-DEC-12 -DO- 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 -DO- 3 5500
01-DEC-12 -DO- 3 5400
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
How to Insert the data in Repeating Tables. I mean Suppose One Student has many Addresses like home,office,permanent etc.
There is one column in this table sequence_no. while Inserting a record how to insert this sequence no I don't know It maintains unique sequence no for each student.
If student has 3 addresses then Its seq no is 3.
I am inserting through a procedure where multiple students data is to be inserted.
how to take care of this sequence no.
I want to insert same sequence number for repeated data else new sequence.
my data is as below
EMP_NAMEEMP_INV
PrasCTI
PrasCTI
ShariqWitness
KateCTI
ShariqAssisted
ShariqWitness
the first 2 record are same Pras CTI and Pras CTI so i want my sequence generate value once as below
1 Pras CTI
1 Pras CTI
2 Shariq Witness
3 Kate CTI
4 Shariq Assisted
2 Shariq Witness
Shariq and witness are repeating so same sequence number but Kate CTI occured once so nextval of sequence
How can i acivee it
CREATE TABLE EMP_INV
(
EMP_NAME VARCHAR2(100 BYTE),
EMP_INV VARCHAR2(100 BYTE)
)
Insert into EMP_INV
(EMP_NAME, EMP_INV)
Values
('Shariq', 'Witness');
[Code]...
How to find whether any character in a string is repeating or not
Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating
Perhaps this is a common request : I have 2 tables:
Table A
-------
ID Value
1 a
2 b
3 c
Table B
-------
ID AnotherValue
1 x
2 y
I am hoping to append a column from Table B to Table A based on a simple sql join (e.g:
Table A
ID Value AnotherValue
1 a x
2 b y
3 c (null)
)
I would rather stay away from the standard update statement since it takes far to long and I'd prefer not to use create table as I don't want to duplicate any data...is this possible to do ? (e.g: just insert the columns into this table ?) - or if it's possible the performance overhead just wouldn't make it worth it ?
Declare
Cursor c1...;
Cursor c2...;
begin
open c1 ;
fecth c1 bulk collect into v1;
close c1;
[Code]...
Is there any way by which if condition gets true then v1 gets appended rather than being overwritten?
declare
type lst_deptno is table of dept.deptno%type index by binary_integer;
type lst_deptno_emp is table of emp.deptno%type index by binary_integer;
v_deptno lst_deptno;
v_deptno_emp lst_deptno_emp;
cursor c1 is select deptno from dept;
[Code]...
I am just looking to my control file that I have. I have managed to successfully get my control file to load data into the required tables but just for my learning I was trying to amend the when clause with an and condition but keep getting an error.
My code:
OPTIONS (DIRECT=TRUE, ERRORS=0, SKIP=1)
load data
infile 'A:/My Files/Feeds/20130211/data_summary_1_20131214.txt' "STR '
'"
APPEND
into table I_DATA_1
WHEN (RID <> 'RID')
fields terminated by "~|~" optionally enclosed by '"'
TRAILING NULLCOLS
(
RID,
S_TIMESTAMP timestamp 'DD-MON-RR HH:MI:SS.FF3 PM'
)
the above code works perfectly but if i change the following line:
WHEN (RID <> 'RID' AND 'S_TIMESTAMP' IS NOT NULL)than this gives me an error.
I have a requirement when I need to append and show the contents of a CLOB (rich text) column into the Open office report.
The use case is as below.
User(s) can enter the details into a CLOB column.
This data entered by each user need to be concatenated onto another CLOB column which will hold all the history of changes. Mainly i wanted to perform a concatenation of these two columns.
|
| User Entered | Stored |
| Data Stored in Clob Column 1| Data Stored in Clob Column 2 |
| v_clob_1 | v_clob_target |
-------------------------------------------------------------------------------
|"Text 1" and a image |"Text 1" and a image |
-------------------------------------------------------------------------------
Any method to achieve this case. I had tried the following method to achieve this. But no success.
v_merge clob;
v_clob_target clob;
v_clob_1 clob;
1)
DBMS_LOB.APPEND(v_merge, v_clob_target);
DBMS_LOB.APPEND(v_merge, v_clob_1);
2)
DBMS_LOB.APPEND(v_clob_target, v_clob_1);
3)
v_merge:= v_clob_target || ''|| v_clob_1;
v_clob_target := v_merge;
Have got ddl of index,but it is not append ';' end of each index,how to append ';' by auto.
Select
Dbms_Metadata.Get_Dependent_Ddl(Object_Type => 'INDEX',
Base_Object_Name => 'TB_HXL_USER')
From Dual
result:
[Code].....
Version: 11.2.0.3
Platform : RHEL 5.8 (But I am looking for platform independant solution)
I want to append the timestamp to spooled log file name in SQL*Plus.The spooled log filename should look like
WMS_APP_23-March-2013.logI tried the following 3 methods found in the google. But none of them worked !
I tried this
col sysdt noprint new_value sysdt_var
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt_var.Logas suggested in
[URL]
and this
spool filename with timestamp
col sysdt noprint new_value sysdt
SELECT TO_CHAR(SYSDATE, 'yyyymmdd_hh24miss') sysdt FROM DUAL;
spool run_filename_&sysdt..Logas suggested in
[URL]
and this
column tm new_value file_time noprint
select to_char(sysdate, 'YYYYMMDD') tm from dual ;
prompt &file_time
spool logfile_id&file_time..logas suggested in
Creating a spool file with date/time appended to file name
None of the above worked in RHEL or MS DOS.
I am trying to delete duplicate or repeating words in a string using regular expressions (regex) and an Oracle database.I have Googled quite a bit on this topic and was unable to track down something that fit the bill or remotely came close to paying the tip.
A pattern would be: word word start with newThe final string should look like this: word start with new Basically all the strings begin with the two words repeating.
how can i control the sequence to return same value for repeating data.
inter_no
10
10
11
12
12
13
14
what is required is as below
inter_no seq_val
10 1
10 1
11 2
12 3
12 3
13 4
14 5
is is on select statement will will insert the data once i get the sequence val is same for repeating one
I have a Requirement in the Report.
During the Report Runtime the Each Page Contain 55 Lines. Their as Lot of Item Groups are available in the Report Each Items Group have Maximum 10 Lines in the Report with Summary Contain in the Repeating Frame
My Requirement is During the Report Preview if the Report Not Fitted in the Page then Whole Item Group is Require to Skip to Next Page.
I have a graph inside a repeating frame, but every time a run the report, it doesn't bring me all de graph, generally skip the last one, for example i show one graph per location but never show me the last ones. What can i do? If I ask 4 location, it bring me 3, if I ask 5 it bring me 4.
View 4 Replies View RelatedINSERT /*+ APPEND */
INTO sales_fact
SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
COMMIT;
[URL]
Suppose in the above case
SELECT *
FROM customer_sales_fact_staging
ORDER BY time_wh_id;
returns a huge number say more than 10 million or 50 million
will the commit work(one single transaction)
I've got SQL code generated by an HP tool to make an update of itself. This code is not modifiable because was execute 'in background' from the tool updater.
The macro operations made by the updater (through sql code) are:
--create a copy of actual table and rename with '_old' suffix
--create new table, with indexes and constraints
--insert data into new table from old tables
Now, only in one table, when the data were inserted, an ORA-00001: unique constraint (ASSET_SVIL.CFG_CFGSECTIONCFGE) violated, appear.
To insert data is used an insert/select statement with an HINT /* APPEND */. We verify all the data existing in the old table (that is the data to 'migrate') but there aren't duplicate record!
BUT IF WE REMOVE THE HINT /* APPEND */, THE INSERT/SELECT STATEMENT WORKS WITHOUT GENERATE ERRORS!!
I have following table: tab1
id configured_size current_size percentage
1 500 100 20
2 300 100 33
... ... ...
When I launch select * from tab1 I get :
id configured_size current_size percentage
1 500 100 20
2 300 100 33
... ... ...
My question is is there any way how to edit the abovementioned select to get something like :
id configured_size current_size percentage
java_eml 500 100 20%
java_hdl 300 100 33%
... ... ...
I want to append two things:
sign of % in row percentage
when id =1 then in output have java_eml
when id =2 then in output have java_hdl
The problem is I cant update the table (not sufficient rights) so is there any way how to adapt that output?
I want to know which is the most efficient insert method among the followiing
1)using the hint append
or
2)bulk collect. Preferably I'd like to know which method of inserting for say 2-5 millions rows is the best.
Am extracting data from a database and putting them in 2 different utl_files. In the end i want to append or merge the 2 utl_files to one file.
View 10 Replies View RelatedI have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
Im trying to replicate a set of rows multiple times to create large volume. I am trying by For Loop, but got confused how to pass the parameters in any cursor i declare.
View 8 Replies View RelatedIm having mail content column of type long Raw in a table.i just want concat or append a value in that column when i tried it shows error "illegal use of long type".how to append value to it. value will be string type
View 15 Replies View RelatedI have a query which returns nearly 20k rows, as per the requiremnet we need to append all these rows in specific format and insert into single clob column.in the below procedure test_clob.textt is clob field.
CREATE OR REPLACE PROCEDURE pro_test
v_mas_seq NUMBER (9);
v_gov_total NUMBER (20, 2);
v_emp_total NUMBER (20, 2);
v_text_exp CLOB;
v_pageaccess VARCHAR2 (15);
v_dto NUMBER (7) := 4011486;
v_batchno NUMBER (20) :=
[code]....
I've created a materialized view log on table with the following statement:
CREATE MATERIALIZED VIEW LOG ON table_a
WITH ROWID, SEQUENCE (column_a, column_b, column_c)
INCLUDING NEW VALUES;
The insert is done with the following statement:
INSERT
/*+ APPEND PARALLEL("table_a") */
INTO
"table_a"
("column_a",
"column_b",
"column_c",
"column_d_sum")
(select
column_a",
"column_b",
"column_c",
"column_d_sum"
from table_B)
But the Log is empty when the insert is finished. When I insert rows without the APPEND hint, rows are created in the log table. So, doesn't the log record bulk loads?