SQL & PL/SQL :: Mark Repeating Values As Blank
Jun 1, 2010
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.
View 5 Replies
ADVERTISEMENT
Jan 4, 2013
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
View 11 Replies
View Related
Sep 7, 2012
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]....
View 6 Replies
View Related
Jan 31, 2013
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.
View 4 Replies
View Related
Mar 6, 2012
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.
View 6 Replies
View Related
Dec 16, 2011
I want to group the following records and mark them in a sequence order.
drop table test;
CREATE TABLE TEST
(
key0 NUMBER,
key1 VARCHAR2(1),
key2 NUMBER)
;
INSERT INTO TEST VALUES (4,'A',1);
INSERT INTO TEST VALUES (4,'A',2);
[code]......
key0 key1 key2
4A1
4A2
4A3
4A4
3A1
3A2
3A3
1A1
1A1
Now I want the records in the following way, where group_no would be in sequence when key2 starts with 1 again.
key0 key1 key2 group_no
4A1 1
4A2 1
4A3 1
4A4 1
3A1 2
3A2 2
3A3 2
1A1 3
1A1 4
Can I achieve this using SQL only and not by PL/SQL.
View 5 Replies
View Related
Nov 11, 2011
I have deleted lot of records in a table.Would oracle be able to insert in the empty blocks generated from deletion of records without bringing the high water mark down.
View 7 Replies
View Related
Nov 4, 2013
I am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
View 2 Replies
View Related
Sep 30, 2011
how to do water mark in oracle report 2.1
View 8 Replies
View Related
Jun 10, 2013
I have one tablespace called U01. This tablepspace contains 31 data files. Due to high water mark I was unable to most datafiles. Since my database running onair application they will not provide me downtime to move the tables. Is there anyway to fix the high water mark without getting downtime window? almost 700+g space unused. I need to reuse them asap because running out of space with in asm diskgroup.
SQL> SELECT A.TABLESPACE_NAME,round(SUM(A.TOTS)/1024/1024) "Tot size MB",
2 round(SUM(A.SUMB)/1024/1024) "Tot Free MB",
3 round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%FREE",
100-round(SUM(A.SUMB)*100/SUM(A.TOTS)) "%USED",
round(SUM(A.LARGEST)/1024/1024) MAX_FREE,SUM(A.CHUNKS) CHUNKS_FREE
4 5 6 FROM (
7 SELECT TABLESPACE_NAME,0 TOTS,SUM(BYTES) SUMB,
[code]...
View 5 Replies
View Related
Dec 19, 2012
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
View 2 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
Apr 19, 2011
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.
View 15 Replies
View Related
Feb 21, 2013
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]...
View 12 Replies
View Related
Sep 8, 2011
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
View 8 Replies
View Related
Apr 5, 2011
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.
View 7 Replies
View Related
Feb 13, 2013
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
View 5 Replies
View Related
Feb 23, 2011
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.
View 1 Replies
View Related
Jun 8, 2012
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 Related
Nov 15, 2010
I 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.
View 3 Replies
View Related
Jul 17, 2009
I have a table in oracle sql developer showing years, IDs and attendance etc I'm wanting to create a report based on this table but have encountered a problem with the years.
The years currently run from 2006 to 2009 and the problem is that some of the IDs are only present for say 2007 and 2008, thus leaving nothing for 2006 and 09. This in turn creates problems in the report.
What i would like to do is in the table, for each ID that is missing any years, is to insert a single row with just the ID and year so that in the report it will show a blank instead of nothing at all. So for the above example i would have numbers for 2007 and 2008 and for 2006 and 2009 there would just be a space.
View 6 Replies
View Related
May 25, 2010
I want to retrive record from tables which dont have person name
if i fire following query it gving 2 records one is correct and one is blank
select
distinct hp.party_name,
hppcv.PERSON_NAME
from hz_person_profiles_cpui_v hppcv,
AS_LEAD_CONTACTS_ALL ALCA,
hz_org_contacts_cpui_v hoccv,
[Code]..
what is problem?
View 8 Replies
View Related
Mar 31, 2012
One more query need to be resolved.
1) I am getting a mail with only the SUBJECT through SMTP.
********* ALERT ********* Tablespace Alert Mail Send from DBA Team. ******** ALERT *********
2) This is from a procedure called 'TBLSPACE_MON_EMAIL'.
CREATE OR replace PROCEDURE "Tblspace_mon_email"
IS
v_smtp_server VARCHAR2(100) := '10.80.23.24';
v_smtp_server_port NUMBER(2) := 25;
v_crlf VARCHAR2(2) := Chr(13)
|| Chr(10);
[code].......
3) It was scheduled as below
What is the problem in this code.
View 18 Replies
View Related
May 23, 2012
I'm presently creating a Graph on a repeating frame.
I made a query with a group that pointing to the repeating frame.
The problem : I want to change dynamically the Y and X axis Title.
The graph accept user parameter inside the XML file. But how to make it dynamically based on SQL query?
I try with formula, but can't make it work with a group. I have to put the formula at Report Level, but to make it dynamically for each group i have to make it work at the group Level.
View 1 Replies
View Related
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
Jun 2, 2010
I am trying to run Cash receipt report in reports builder. But the output is always blank. It is not even giving any fields created in the layout. This report was developed by some other developer, and there is no documentation available.
I am able to run the query in TOAD and I am getting genuine rows.
View 5 Replies
View Related
Jul 3, 2012
Is There any way to count how many space in the sentences..Like this the sentences : " I AM BOY".
There is 2 space in the sentences.How can i find out using oracle query.
View 7 Replies
View Related
Sep 23, 2011
I am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
View 12 Replies
View Related
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
Aug 2, 2013
If running the following query:
select s.effective_date
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;
There are no rows returned.However, if I am running the other query:
select MAX(s.effective_date)
from SECURITYDBO.DERIVATIVES s
where s.security_alias=100014320 and s.src_intfc_inst=0;
There is a BLANK row returned.
View 11 Replies
View Related
