SQL & PL/SQL :: Retrieve Data By Checking Any Part Of The String?

Jul 8, 2012

I have a table

select * from testing
Quote:
ID NAME REDATA
-------------------- -------------------- --------------------
1 ABC,DEF,GHI,LKJ 000001
2 MNC,GHI,CTF 000002

2 rows selected

select * from testing where name like ('GHI,TCF');

now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.

View 9 Replies


ADVERTISEMENT

SQL & PL/SQL :: Selecting Only Part Of A String

Feb 25, 2010

I am selecting a column from a table and placing it into a cursor. The column contains backup job names that are formatted like the following:

SERVER_DATABASE_BACKUP_BACKUPTYPE_JOBID

However, I only need the DATABASE piece selected in my FOR loop below.

CURSOR c1 IS
SELECT COLUMN_NAME
FROM TABLE_NAME;

[Code]...

View 2 Replies View Related

SQL & PL/SQL :: Extracting Part Of A String

Jan 9, 2013

To make SQL query to before and after specific character.

Create table test(flist not null VARCHAR2(200));

First field content with below record:

FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP

to filter the above record from FLIST column thorugh sql script as below:

FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP

means remove first 5 charator and after APXXXXXXXXX.

Output of SQL query should come like below:

CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION

View 24 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

Select Query Matches Part Of The String

Mar 11, 2010

Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.

Here xxx.a will contain values like 123 or 456 like that .

View 6 Replies View Related

Reports & Discoverer :: How To Set Part Of String In Bold

Apr 30, 2013

I work with Oracle Forms and Report Builder since several weeks, but now i have something that i want to do with Report Builder.

I get string from DB (It's a paragraph) But i would like to set bold weight on a part of the string

for now, i set bold text i want to and i have created a fonction that make text bold like this

l_chaine := (SUBSTR(MyStrFromDB, InSTR(UPPER(MyStrFromDB), '[B]'),InSTR(UPPER(MyStrFromDB), '[/B]') ));
if(length(l_chaine) > 0) then
SRW.SET_FONT_WEIGHT(SRW.BOLD_WEIGHT);
end if;

but it doesnt look to works..

how can i do that? And where i can do that?

View 2 Replies View Related

SQL & PL/SQL :: Query Of Functions - Display Half Part Of String

Jun 8, 2011

I want to write a plsql program to display half part of a string .Example there is a string like gillmadden@myharbour.org.uk

I want to display only the later part of the string

myharbour.org.uk as output.

Can we write using string functions in plslql?

View 19 Replies View Related

Application Express :: Computation To Trim Off First Part Of String Within List Manager Item Values

Jul 1, 2013

A computation after submit pl/sql function process to trim off the first part of the string (CQ..) within the list manager values. Support for example the list manager contains values such as

 CQ..SAMPLE1..TEST1CQ..SAMPLE2..TEST2CQ..SAMPLE1..TEST2 

The computation process should trim off the first part(CQ..) and should return the list manager value as SAMPLE1..TEST1SAMPLE2..TEST2SAMPLE1..TEST2 Oracle APEX 4.0.2 is the version and Oracle 10g r2 is the database. 

View 7 Replies View Related

Application Express :: Get Part Of URL As Part Of Dynamic Action When Clicking A Button?

Nov 3, 2013

I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?

View 6 Replies View Related

SQL & PL/SQL :: How To Retrieve Unique Values From A String

Feb 5, 2013

Objective: I need to compile a final string by concatinating the unique values from different strings.

Here is the script to create tables and data.

Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');

COMMIT;

select * from temp_acronyms;

ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO

I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.

my final string should have the values as below

ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY

View 6 Replies View Related

SQL & PL/SQL :: How To Retrieve First Letter Of Every Word From String

Oct 4, 2013

I want to retrieve first letter of every word from the string.

For ex. if string is "Computer Science Engineering (New)" then I want "CSE" or if string is "Mechanical Engineering" then "ME".

how to do this..

View 18 Replies View Related

Text :: Make Part After AND Not Match Already Matched First Part?

Nov 2, 2012

I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.

The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:

Select * From AddressSearch
Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;

But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?

View 2 Replies View Related

Reports & Discoverer :: REP-0002 Unable To Retrieve String From Report Builder

Nov 25, 2010

My own created software is running since last 4 years but now when I am trying to run a report then I got a message "REP-0002 unable to retrieve a string from the report builder" and as a result my report don't run. Please note that some other reports are working properly, this problem is happens only with some of them reports.

View 3 Replies View Related

PL/SQL :: How To Delete Records Checking Data And Time

Oct 28, 2013

I want to delete the records which are less than or equal to this date 

MIN(TRUNC(sale_week_date)-(1/(24*60*60)))  

For that I have created the following procedure.

 CREATE OR REPLACE PROCEDURE DELETE_DATA_WEEKISMIN_DATE DATE;SELECT MIN(TRUNC(sale_week_date)-(1/(24*60*60))) INTO MIN_DATE
FROM sales_week; DBMS_OUTPUT.PUT_LINE(MIN_DATE); 
DELETE FROM sales WHERE sale_date <= MIN_DATEAND sales_creation_date <= MIN_DATEAND sales_update_date <= MIN_DATE; END;

 If declare as MIN_DATE Date;

it's not taking time. If declare as MIN_DATE Timestamp; It is taking milli seconds also. 18-OCT-13 11.59.59.000000 PM SALE_DATE ,SALES_CREATION_DATE and SALES_UPDATE_DATE in this  format  18-OCT-2013 11.59.59 AM. How  to delete the records which are less than or equal to this date  MIN(TRUNC(sale_week_date)-(1/(24*60*60))) ..

View 9 Replies View Related

PL/SQL :: Checking Correct Data Format Using Sql Query?

Aug 30, 2013

1) I got column date of joining which accepts date in below format DD-MON-YYYYDD-MON-YYMON-DD-YYYYMON-DD-YYMonth DD,YYYY Question:-

how do i check whether all dates in Date of joining column are in above format or not using sql query? 2) I got one more date column which accepts date in below formatMMDDYYYYYYYYMMDDMM/DD/YYYYMM/DD/YYYYYY/DD/MM Question:-

how do i check whether all dates in date column are in above format or not using sql query?

View 26 Replies View Related

JDeveloper, Java & XML :: Parsing With Special Character As Part Of Data

Sep 10, 2013

I'm using following select to parse XML as table ( Oracle 11.2.0.3.0 EE Linux and Windows)

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

However, when DESCRIPTION contains "<" as part of it - I'm receiving an error:

SELECT a.id, a.name, a.description
FROM ( XMLTABLE('//ROW'
PASSING (SELECT xmltype('<ROWSET>
<ROW><ID>1111</ID><Name>Name1</Name><DESCRIPTION>AAA</DESCRIPTION></ROW>
[code]........

Quote:ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00231: Invalid character 61 ("=") found in Name or Nmtoken

I'm receiving a XML from some external source and can not change it.

View 8 Replies View Related

Application Express :: Include Data Files As Part Of Export Process?

Jan 14, 2013

if it is possible to include 'data' files as part of the Application Export process. Up till now I've only been able to include supporting object files which will recreate a table structure but not the data records - I've been creating the latter outside of APEX.

View 2 Replies View Related

PL/SQL :: How To Retrieve The Data In Where Clause 

Dec 28, 2012

SELECT mkct_group_code FROM ID_MKT_CONTTYPE_GROUP_MASTER where mkct_group_code=20'

Note mkct_group_code column is varchar2(10) type.

It has values as
20
10
30
20'
30'

View 3 Replies View Related

Retrieve Data From A Table For Certain IDs

Apr 7, 2013

I am writing a SQL inside an application to retrieve data from a table for certain IDs or for All employees based on the user's input. I create a variable :SELECTION for the where clause. When I run the SQL, I receive the error of missing right parenthesis. There is no error if I hard code the IDs.

If the input is 'All' -> the variable :SELECTION is blank so if the SQL :

select name, address, ID from employee :SELECTION

will be

select name, address, ID from employee

If the input of users ID as '1234','9999' - > the :SELECTION = ' where ID in (:VARIABLEID)'
:VARIABLEID = '1234','9999'

so the SQL:
select name, address, ID from employee :SELECTION

should be

select name, address, ID from employee where ID in ('1234','9999')

However, I got the missing right parenthesis error. I suspect it is something with the single quote, so I tried different ways, but it is still not working.

View 1 Replies View Related

Fast Way To Retrieve All Data Blocks

Apr 19, 2012

I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:

select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one

my question is can retrieve all the data in one block more quick than this query.

View 5 Replies View Related

SQL & PL/SQL :: Retrieve Hierarchical Data From Same Table

Nov 10, 2011

Look into the below table:

TABLE :- EMPLOYEE
________________________
| ID | SUPERVISOR |
|_______________________|
| A101 | B102 |
|________|______________|
| | |
| B102 | C104 |
|________|______________|
| | |
| C104 | D108 |
|________|______________|
| | |
| D108 | E104 |
|________|______________|

Here B102 is supervisor of A101 and C104 is supervisor of B102 and so on. I want to get this data into new table in below format

TABLE :- Hierarchy
________________________________________________________________
| ID |SUPERVISOR_1 |SUPERVISOR_2 |SUPERVISOR_3 |SUPERVISOR_4|
|______________________|_____________|_____________|____________|
| A101 | B102 | C104 | D108 | E104 |
|________|_____________|_____________|_____________|____________|
| | | | | |
| B102 | C104 | D108 | E104 | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| C104 | D108 | E104 | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | | | | |
| D108 | E104 | NULL | NULL | NULL |
|________|_____________|_____________|_____________|____________|
| | |
| E104 | NULL | NULL NULL NULL
|________|_____________|_____________|_____________|____________|

I want to insert 1st two rows into Hierarchy table, then I would like to update Supervisor_2 to Supervisor_4. Here I don't want to use 'CONNECT BY PRIOR', as it take more time to execute (there are millions of records).SQL code for same.

View 7 Replies View Related

SQL & PL/SQL :: How To Retrieve Data That Does Not Exists In A Table

Dec 13, 2011

I have the following table with the some sample data:

|| Filename || receiving_site || originating_site ||
| fileA | siteA | HQ |
| fileA | siteA | siteA |
| fileB | siteA | HQ |
| fileA | siteB | HQ |
| fileA | siteB | siteB |
| fileB | siteB | HQ |
| fileB | siteB | siteB |

The table works in such way:

1. For each of the Filename, if originating_site is not the same as receiving_site, it means that the file has been sent to receiving_site but has not been acknowledged received yet.

2. For each of the Filename, if originating_site is the same as receiving_site, it means the file has been sent and received by the receiving_site.

My task is to list out all the Filename per receiving_site that has been sent, but not received yet by the receiving_site. For example from the sample data above, I am expecting to see that siteA, fileB has not been received yet.

How can I do that? I had tried MINUS and NOT EXISTS command, but I am just not able to get the result that I want.

View 2 Replies View Related

SQL & PL/SQL :: How To Retrieve 6 Decimal Place Of Data

Oct 14, 2011

IF I have Salary Table,In that Salary Table,How to retrieve 6 decimal place of data .

View 16 Replies View Related

SQL & PL/SQL :: Query To Retrieve The Data Without Iteration?

Dec 27, 2010

I have one table as shown below,

CREATE TABLE MAT
(matrl varchar2(100),
date_man date,
weight number(10)
);

INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat1','12-DEC-10',100);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat2','13-DEC-10',200);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat3','21-DEC-10',300);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat4','26-DEC-10',400);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat5','22-DEC-10',500);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat6','02-DEC-10',600);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat7','23-DEC-10',700);
INSERT INTO MAT(Matrl,DATE_MAN,WEIGHT) VALUES ('mat8','07-DEC-10',800);

The data will look like this .

matrldate_manWeight
mat112-Dec-10100
mat213-Dec-10200
mat321-Dec-10300
mat426-Dec-10400
mat522-Dec-10500
mat602-Dec-10600
mat723-Dec-10700
mat807-Dec-10800

I need to order by this data in terms of date and need to get the data as shown below. Acc_wt is accumulated weight till the material.

Date Weightacc_wt
mat602-Dec-106000
mat807-Dec-10800600
mat112-Dec-101001400
mat213-Dec-102001500
mat321-Dec-103001700
mat522-Dec-105002000
mat723-Dec-107002500
mat426-Dec-104003200

Can we make use of oracle lag and lead functions here?Or some other methods?

View 6 Replies View Related

SQL & PL/SQL :: Retrieve Data From Blob Column?

Sep 30, 2011

example for retrieve data from blob column using pl sql procedure in oracle?

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Subset Of Data From Table

Jan 20, 2012

How to retrieve first hundred records from a table ?

FYI
---

The table size is 5 GB
The table count is 127922653
Table has 14 columns

Table is partitioned as well.

The table has 10 partitions.

View 17 Replies View Related

Forms :: Using LOV On Some Field To Retrieve Data

Jun 8, 2011

I am using LOV on some field to retrieve data, but sometimes it does not retrieve anything cause of unavailability of date its fine but i want to put N/A in that field when such condition is occurring .

View 2 Replies View Related

PL/SQL :: Retrieve XML Data In Clob Type?

Dec 5, 2012

I have a table created as below

create table tab123( x clob);

Inserted a row as below

insert into t values ('"<ProcessBatchRequest xmlns:inp1="http://services.abc.com/" ServiceVersionNumber="" xmlns="http://services.abc.com/GlobalBatchServiceV1.0">
<inp1:BatchDetail>
<inp1:ApplicationID>test.123</inp1:ApplicationID>
<inp1:ApplicationBatchID>test.123</inp1:ApplicationBatchID>
<inp1:MessageTimeStamp>2012-11-28T11:05:32.000-05:00</inp1:MessageTimeStamp>

[code]....

Now i want to retrieve the value present under a given xpath.

View 7 Replies View Related

Forms :: How To Retrieve Only That Data Which Is Based On Given Condition

Jun 22, 2011

,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.

View 2 Replies View Related

SQL & PL/SQL :: Retrieve Data With Multiple Select Statements

Jun 3, 2012

I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.

select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",

[code].....

View 3 Replies View Related







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