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 )
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
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
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?
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!
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.
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');
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
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.
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.
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).
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.
