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