SQL & PL/SQL :: String Together All Term IDs For ATM

Aug 30, 2010

I want to create a strung together list of ATM IDs for each ATM Location (as one ATM Location(City) can have many ATMs(term ids) this is to allow transaction facts to be not broken down on several lines depending on how many term ids there are for that ATM Location (whenever a new ATM is set-up, a new row is created in the ATM table).

I know I can string it together using a function but I do not have rights to do it so I created SQL in which I feed in the ATM Location as a parameter. I want to do this for ALL ATMs but that is taking forever - is there any way to optimize the below code.

Select max(term_id),atm_location from (Select
(SYS_CONNECT_BY_PATH(TERM_ID,' ' ) ) term_id,atm_location
from
(select term_id , atm_location
from ATM_TABLE
order by term_id asc
)
Start with TERM_ID IN (Select max(Term_ID) from ATM_TABLE group by
ATM_LOCATION )

View 8 Replies


ADVERTISEMENT

SQL & PL/SQL :: Term Breakdown

Feb 1, 2011

Currently I have this in the employee table:

Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
220050562500

Is it possible to display the above information like this:

Employee_idStart Date(YYYMM)Term(months)Monthly Salary
1200801122000
1200802122000
1200803122000
1200804122000

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Rank / DECODE Most Popular / Most Used Term

Mar 26, 2012

I have this code which simply gives me the count of term.

SELECT
OUA_ID

,COALESCE (MAX( DECODE( TERM, 0603, TERM_COUNT, NULL ) ), 0 ) "TERM_0603_CNT"
,COALESCE (MAX( DECODE( TERM, 0702, TERM_COUNT, NULL) ), 0 ) "TERM_0702_CNT"
,COALESCE (MAX( DECODE( TERM, 0705, TERM_COUNT, NULL ) ), 0 ) "TERM_0705_CNT"
,COALESCE (MAX( DECODE( TERM, 0707, TERM_COUNT, NULL ) ), 0 ) "TERM_0707_CNT"

FROM(

SELECT DISTINCT
OUA_ID, TERM_COUNT, TERM
FROM(
SELECT OUA_ID, TERM ,PROVIDER_CDE, COUNT(*) TERM_COUNT
FROM TABLE
WHERE PROVIDER_CDE = 'BILL'
GROUP BY OUA_ID, TERM, PROVIDER_CDE)) GROUP BY OUA_ID

Gives:

OUA_IDTERM_0603_CNTTERM_0702_CNTTERM_0705_CNTTERM_0707_CNT
32710 0 3 7 0
45726 2 0 1 0

Is there a way i could used the rank function (with decode) to only display the 1ST (First) and 2nd(Second) most used term per OUA_ID?

So i get this:

OUA_ID1st MOST USED2nd MOST USED
3271 705 702
45726 603 705

View 4 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

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

SQL & PL/SQL :: Remove String Before And After Pipeline To Get String Between Pipeline?

May 10, 2010

I have a string like below:

string = 'HEADER||MEAL||15'

How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?

View 9 Replies View Related

String To Array

May 22, 2009

I have a comma seperated string say (tr,er,pr) and i have to convert it to ('tr','er','pr'). if there is function coded to do so.

View 1 Replies View Related

String Of Numbers

Oct 12, 2007

I have the following set of numbers that i am passing in as one input into a stored procedure.

234,456,234,456,567

Now i want to take this list of numbers and use it in an IN statement:

select * from table where column_a in (P_INPUT);

however, when i try this, it give me an invalid error. I have tried inserting single quote around each value and get the same invalid error. I tried a To_char around my column, which solved the error, but it never finds a match!

View 6 Replies View Related

Count For A Particular String?

Oct 9, 2008

say there is astring "mumbojumbo "i need the count of given string in it

ex:when o is given count shuld be 2 when m is given count shuld be 3

is there any pre defined function for counting a given string ...

View 2 Replies View Related

SQL & PL/SQL :: How To Get First Word From String

Aug 22, 2013

how to get first word from string.example i have string like:-

Jack Bore American
Mark D'suz Australian
Raj
Deniel indian
Some

i am expecting first word as out put like :-

jack
mark
Raj
Deniel
Some

in the same maner if i want should get two words also.

View 36 Replies View Related

SQL & PL/SQL :: How To Get Last 4 Characters In A String

Dec 12, 2010

how to get last 4 characters in a string. But i don't know the length , for example the string is

abcdefghij

i want only ghij.

View 5 Replies View Related

SQL & PL/SQL :: How To Add Some Records Between String

Apr 6, 2012

i have a one table with name as sms_tbl having one field name as sms_text and it contains text messages like

sample_text:Welcome to I-Care, your TPA for your<Insurer> health policy Your ID no is <I-Care ID>

in above text i need to insert records from two tables in the place of <Insurer> and <I-Care ID>.

where <Insurer> and <I-Care ID> records are in two different tables

where i have mapping for this records and there is no mapping for sms_tbl and how to insert these two records in above sample_text

View 3 Replies View Related

SQL & PL/SQL :: How To Split A String

Dec 21, 2011

I have strings like

1) ICE_10001 ICE_10002 ICE_10003

2) ICE_10005 ICE_10006

i want to split above strings like

1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006

If it is possible in oracle sql

View 15 Replies View Related

SQL & PL/SQL :: String To Date

Oct 31, 2013

I want to convert the follow string to date: 2013-12-04 11:35:54.89

View 7 Replies View Related

SQL & PL/SQL :: String Separation

Jul 12, 2010

I have the following test case:

create table t(
col1 varchar2(10),
col2 varchar2(100))

[Code]...

I need to spit col2 like this

P0001 aaa
P0001 ddsd
P0001 sds
.
.
.
.
.

delimeter between individual may be #, ' ',',' or';'

How can I achieve this using PL/SQL

View 13 Replies View Related

SQL & PL/SQL :: Replace String In 9i?

Jun 26, 2012

I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.

Eg.

Input String :

Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .

Output string :

Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown

View 6 Replies View Related

Randomize A String?

Feb 7, 2007

if it is possible to randomize a string

for example if i have string value of ('ABC') how could i get Oracle to randomize the output say to 'BCA' for example

View 1 Replies View Related

SQL & PL/SQL :: String Matching?

Feb 22, 2011

check out following scripts

CREATE TABLE email (
ids VARCHAR2(500))
INSERT INTO email VALUES('1233@gmail.com;3456@gmail.com;0954@gmail.com')
INSERT INTO email VALUES('7658@gmail.com;346@gmail.com;6346@gmail.com')
CREATE OR REPLACE FUNCTION is_same(emailid VARCHAR2) RETURN BOOLEAN

[code]...

how to write --emailid is present in rec.ids THEN this logic in function

View 2 Replies View Related

SQL & PL/SQL :: String Between Two Strings?

Apr 4, 2012

I have to fetch a string which is between to constant strings in a column.

Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.

In this i need to fetch only ID from each row.

create table testtable ( TestCol varchar2(4000));

INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');

View 17 Replies View Related

PL/SQL :: How To Do String Concatenation

Nov 22, 2012

I am trying
  
IF LENGTH(v_final_string) < 3800 THEN
        SELECT nvl2(v_final_string,v_final_string
          ||',' ,v_final_string)
          || temp.temp_string
        INTO v_final_string
        FROM DUAL;
        DBMS_OUTPUT.put_line ('v_final_string=' || v_final_string );
      ELSE
        EXIT;
      END IF;inside a loop.

But it's not concatenating. I am alwas getting empty v_final_string

View 9 Replies View Related

PL/SQL :: Replace A String

Sep 26, 2013

I have a string. For example "I have too many files. There are 1000 files. I have to delete them." Sometimes the string can be "I have too many files. There are 115003 files. I have to delete them." Whatever the srting is, I need to change the string to "I have too many files. There are 10 files. I have to delete them." replace the "1000" or "115003" to "10". This portion of the string is always an integer. I use Oracle 11G2. 

View 6 Replies View Related

SQL & PL/SQL :: String Parsing

Aug 28, 2012

I have the following table A that contain one column "MYREC"

MYREC
1253 69889897 89884 891254
568989 89897891 321 698751232
1239892 123358798 7899 58123457

I need to parse the variable column, the issue is that the number of spaces.

The string might start with 2 or more white spaces, that I can get rid of with the LTRIM function..I'm having difficulties with the rest ):

View 5 Replies View Related

How To Convert String To Date

Apr 15, 2007

I have a date saved in varchar2 colomn.

how can I convert the vaules into date, so I can use the date to compare data.

Ex: I have the value '20-03-2007 05:31:29', but this value is saved as varchar2.

how can I take from this value the date '20-03-2007' as date format?

View 6 Replies View Related

Split A String In Oracle

Sep 27, 2010

I have a string like '9999999;A' one field as numeric & other as char.Now i want to split this string into two values removing the ; delimiter in oracle using for loop.

View 2 Replies View Related

Split A String Up If It Has Space?

Jun 19, 2008

i have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!

SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;

but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..

View 2 Replies View Related

Difference Between Number And String?

Oct 20, 2012

I can't understand >>

1. Number 2 is less than number 100,

2. string '2' is greater than string '100'.

>> Numbers are ordered by numerical value; strings are ordered by alphabetical value. >>

View 1 Replies View Related

How To Concatenate A String With Variable

Mar 5, 2008

I am trying to replace the string_name in the following execute immediate statement with a concatenated

string_||v_variable.

execute immediate
'select
table_name
from
user_tables
where table_name = :a'
into v_table
using 'string_name';

I tried different declaration and concatenations but nothing worked so far.

View 2 Replies View Related

SQL Tuning - String Of One-to-one Joins

Oct 1, 2013

I have a query I am trying to tune. It presently takes anywhere from 15 minutes to two hours to run, depending on how many records the client has. But it needs to run several hundred times, and finish over the course of a weekend. When it runs over, we have problems.

Here's the basic structure of the query:

CODESELECT ...
  FROM main_tab,
       tab_a,
       tab_b,
       tab_c,
       ...
       tab_z
WHERE main_tab.client_id = :1
   AND main_tab.unique_id = tab_a.unique_id(+)
   AND main_tab.unique_id = tab_b.unique_id(+)
   AND main_tab.unique_id = tab_c.unique_id(+)
   ...
   AND main_tab.unique_id = tab_z.unique_id(+);

All of the tables are indexed (and statistics are gathered) on the field unique_idMain_tab has an index on client_id.There is a one-to-one join (sometimes one-to-zero, thus the outer join) from the main_tab table to all the other tables.These are static tables, they're wiped and recreated - no changes, inserts, deletes.

By default, the optimizer does a full table scan and then a hash join on every single of the 26 tab_a through tab_z tables, only using the index on main_tab.

By the way I can add indexes, possibly even to the point of adding an index on some tables that would include all the fields found in the select clause on that table. But I cannot change the table structure (by, say, combining these tables together).

View 2 Replies View Related

SQL & PL/SQL :: Concate String Into Tablename?

Jan 24, 2012

i have one query regarding string concation into table name.

select * from abc_20102011;

Here i post one sql in which tablename is abc_20102011, i want to derive last 8 charectors dynamically by using function. function getFinYear returns financial year in varchar2, i tried bellow query but its not working.

select * from 'abc_'||getFinYear(sysdate);

View 2 Replies View Related







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