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


ADVERTISEMENT

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

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

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 :: 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 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 :: What Part Of Query That Is Being Run Now From Procedure

Apr 14, 2010

I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.

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

Application Express :: How To Create A Display Image Item With Apex Functions

Jul 28, 2012

I'm working on Apex 4.1.1.00.23 in a 11g XE Data Base, I want to display a Image in a Report, I can do it using the "+BLOB Download Format Mask+", but the images displays at original size and I want to display them smaller.

I've read that you have to create a procedure, a SYNONYM and call them in the src of the img tag. as Zsuzsi Vadasz mentions here [URL] But I remember that I was able to create some items in reports with Apex functions, I think they were from APEX_ITEM Package, so I want to know if there is a way to create the "+Display Image+" item with one function of APEX.

View 2 Replies View Related

Forms :: 10g - Using Form Only For Query-functions?

Aug 17, 2011

I want to use my form only for query-functions.

When one enters the form, he can look up records either on Companyname or on Projectname. Therefor I have provided 2 buttons who pop-up a LOV. After eitherone is selected, the query has to be executed. There is a Master-Detail relationship between Company and Project.

My plsql for the company button:

declare
v_show_lov boolean ;
begin
enter_query;
v_show_lov := show_lov('LOVFIRMA');
if not v_show_lov then

[code]....

Plsql for the project button:

declare
v_show_lov boolean;
v_get_value number;
begin
v_show_lov := show_lov('LOVPROJECT');
if not v_show_lov then

[code]....

The first button only works when I go manually in Querymode first (by pressing F11). So I reckon my enter_query doesn't work? The property 'Fire in Enter-Query Mode' is Yes.

When I press it in non-query mode. It just fills in the LOV-values and the CCODE from company. It doesn't execute the query (probably because there is no enter_query).

When I enter query mode, the focus changes automatically to Company. And the LOV doesn't appear.

I have tried placing the enter_query on different places, just as the go_block and clear_block things, but there is always something wrong.

View 8 Replies View Related

SQL & PL/SQL :: Code That Display First 10 Words From A String

Oct 19, 2011

wrote a code that display the first 10 words from a string.

View 15 Replies View Related

SQL & PL/SQL :: If Exist / Then Display As Lines As String Between /

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

Way To Display Proper Case Of A String

Jan 31, 2012

The Ename SMITH should appear as Smith.

View 1 Replies View Related

Reports & Discoverer :: How To Create Half Size Report

Nov 29, 2011

I am using the Report 6i. By using the same tool i created a report. It printing portion is working correctly (that is printing only Half of the page but the next page is move to after scrolled to balance of A4 size). But i want to print with in A4 two pages.

I created using the following property setting....

Layout model-main section-property

Section
width 8.5
height 5.9

Character Mode
Report width 80
Report Height 35

But it utilizing to print half page and balance is scrolled then new page is start(new A4 next paper).

View 1 Replies View Related

SQL & PL/SQL :: Query Using Analytical Functions-date Range

Nov 30, 2010

I have a table which has the attached data.

Sample data is here

LOGON_DATE NUMBER_OF_LOGINS
11/28/2010 02:00:001
11/28/2010 03:00:001
11/28/2010 04:00:002
11/28/2010 06:00:004
11/28/2010 07:00:002
11/28/2010 08:00:003
11/28/2010 09:00:006
[Code] ........

I am trying to do a report like this.

Date PeakUsersBetween6AMand6PM AVGUsersBetween6AMand6PM PeakUsersBetween6PMand6AM AVGUsersBetween6PMand6AM

Output should be
11/28/2010 25 11 49 27
11/29/2010...

I am using analytical function to do this, It throws an error range cannot be used for dates.

View 5 Replies View Related

SQL & PL/SQL :: Rewrite Query With NOT EXISTS Using Analytic Functions

Aug 25, 2011

How to re-write sql with NOT EXISTS using analytic functions?I have the following sql:

SELECT f1, f2
FROM t1 A2
WHERE NOT EXISTS (SELECT 1 FROM t1 A3 WHERE A3.f1=A2f1 AND A3.f2=A2.f2
AND A3.f3=A2.f3 AND A3.f4 = 0
)

View 6 Replies View Related

Display String Using Select Statement In Oracle 10g

Jul 27, 2010

i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.

example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'

i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.

View 14 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

Server Administration :: Server Down Automatically After Every Half An Hour Or 15 Min

Feb 4, 2010

the reason why this happen with our server.

View 4 Replies View Related

SQL & PL/SQL :: Can Display Value From A Query Into New Row

Apr 13, 2013

I have a requirement where I want to display amount what I have billed to my client and then how I received my amounts from that Bill. For eg.lets say there is bill no 001 amounting 10000 and after some days I received a part payment cheque/cash from client worth 2000 then he gave second part 2000 and then finally I received 2000. Still 4000 is remaining to be received.So in tabular format I want to display data which will have 3 records of 2000 and one record of balance 4000.

In attached file i have given few examples, where highlighted area is what i want to derive. There are formula's in Collection date, delay by and Int calc. Basically highlighted lines are the ones which should be shown in query result. And data shown above that is what i have right now.

View 1 Replies View Related

SQL & PL/SQL :: Query To Display Next Ten Dates

Apr 28, 2010

When i had a interview last week they ask me to write a query to display the next ten dates using sql query ( without using PL/SQL).

View 11 Replies View Related

Query To Display Records In Particular Order

Feb 8, 2007

I need to write a query to sort the records in a particular order,

Say if I group the records by Dept number
Dept no Name
10A
10G
10f

20B
20K

30I
30M
30R
30Y

I need to write a query that will make this records listed like

Dept No Name
10A
20B
30I

10G
20K
30M

10F
20null
30R

10null
20null
30Y

View 1 Replies View Related

Pagewise Display In Oracle Query

Aug 14, 2010

the scott.emp table has 14 employee records. I want to display like this in a select query .

ename ename ename
-------- -------- --------
adams abc def
scott xyz pal
smith hij efg
------ -------- -------
page1 page2 page3

and another 5 records
ename ename
-------- --------
asd rty
afg uio
wrt no name
------- ---------
page4 page5

View 1 Replies View Related

Getting Query To Display Only 150 And 162 As They Have Purchased Only One Product?

Jun 24, 2011

Is there a way to find customers purchased only single product from the following table?

cusno Product Date
----- ------ ----
121 ES 03/12
121 NT 30/12
131 ST 03/12
13 WT 04/12
150 ES 05/12
150 ES 06/12
150 ES 07/12
160 MN 05/12
160 ES 06/12
160 ES 07/12
162 NT 08/12

I need a query to display only 150 and 162 as they have purchased only one product.

View 2 Replies View Related

Forms :: Executing Query When Display Box Value Changes

Aug 24, 2010

In my form i have 2 datablocks, the first contains only a display item which is populated from a lov when a user presses a button. The second datablock shows a list of items which should be queried dependant on the value of the above LOV. What i thought i could do is on a post-change trigger for the display item is:

go_block('block_name');
do_key('execute_query');

However, i am told i cannot do these in a post-change trigger, how to acheive what I am looking. Would i perhaps have to create my own trigger somehow or is their a simplier way.

View 2 Replies View Related

SQL & PL/SQL :: Query To Display The Office And Count(*)?

Nov 1, 2012

I need a query to display the office and count(*)

where rownum > 6 i.e

here is the data

office
------
hq
hq
hq
hq
hq
hq1
hq1

[code]....

I need a query to display the top 3 counts and the 4 row should be sum of count of other offices.

my result should look like this

office count(*)
--------- --------
hq 5
hq1 4
hq2 3
other 10

View 4 Replies View Related

SQL & PL/SQL :: Dynamic Sql Query String - Getting Error?

Aug 22, 2010

FUNCTION get_attributed_sedol( p_ref_number IN VARCHAR2,
p_field IN VARCHAR2 )
RETURN VARCHAR2
IS
l_query VARCHAR2(1000);

[code]...

It gives me an error on the line where the execute immediate statement is.
Quote:
ORA-00905
missing keyword

Cause: A required keyword is missing.

Action: Correct the syntax.

But when I check my stack trace table to see what the actual query string looks like, I see this

Quote:
SELECT sedol INTO l_attributed_sedol_code FROM integration.tmp_attributed_sedol WHERE CLIENT_LEDGER_REF='LEAE057090' AND ROWNUM=1

There's nothing wrong with that, is there? It executes fine if I try it manually.

View 3 Replies View Related







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