WHERE TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTD,'DD-MON-YYYY:HH24MI') > TO_DATE(TO_CHAR(RR.PEROFOPFROM,'DD-MON-YYYY')||RR.AIRCRAFTSTA,'DD-MON-YYYY:HH24MI')
I have data like this:
PEROFOPFROMAIRCRAFTSTD
29/03/20102150 NULL NULL NULL NULL 30/03/20102150
When I execute the query it always gives me the error "literal does not match format string".
I'm trying to do data mining on a web log which recorded one day web access information from a busy web server. I imported the data into Oracle Data miner, and created a table (WEBLOG). The idea is to create a new field, i.e. session, for the users so that each session could be thought as a representative of a user-intent (aka topic). Now based on this, data mining models would be used to cluster(group) the users based on their similarity. The first step is to prepare the data which involves using SQL queries. So first, all I did was to create a function for date and time. This is the following code I used,
create or replace function ssndate(p_date in varchar2 default '03-01-18', p_time in varchar2) return number $if dbms_db_version.ver_le_10 $then deterministic $elsif dbms_db_version.ver_le_11 $then result_cache $end as begin return trunc((to_date(p_date||' '||p_time, 'dd-mm-yy hh24:mi:ss') - to_date('01-01-90','dd-mm-yy')) * (86400/2400)); end ssndate; /
The function ssndate compiled successfully.The next step I took was to create a view through the following query,
create or replace view WEBLOG_VIEWS as select (select ssndate(LOG_DATE, LOG_TIME) from dual) as "SESSION_DT", C_IP, CS_USER_AGENT, (CS_URI_STEM||'?'||CS_URI_QUERY) as WEB_LINK from WEBLOG;
This was successful as well. The problem is in the next step where I try to do data grouping.
create table FINAL_WEBLOG as select SESSION_DT, C_IP, CS_USER_AGENT, listagg(WEB_LINK, ' ') within group(order by C_IP, CS_USER_AGENT) "WEB_LINKS" from WEBLOG_VIEWS group by C_IP, CS_USER_AGENT, SESSION_DT order by SESSION_DT [code]....
the to_date function should be fine. In the data that I possess, the date and time are in no format. Example: 30118 and 0:00:09 respectively.
insert into iostat_mac values(to_date('2012/03/28 08:00:00 AM'),'653.6','20.7','15392.0','451.8','0.0','5.5','0.0','8.2','0','64','/vol/sun_dc_u2');
Receive ora-01861 error when trying to insert.Suspected it had to do with the date format, I changed the nls parameter for my session to include the time format, but to no avail.
i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows
first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.
Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.
Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.
We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.
CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2 (30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30)); INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO'); insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO'); insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL); INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL); SQL> SELECT * FROM OM_MC_MASTER; [code]....
UPDATE event_request SET REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi') WHERE eventID=123
Oracle returns this error:
ERROR at line 4:ORA-01861: literal does not match format string
where line 4 is REQ_EVENT_DATE = TO_CHAR(REQ_EVENT_DATE - INTERVAL '1' HOUR, 'yyyy-mm-dd hh24:mi').The REQ_EVENT_DATE field was originally populated with this stripped down query:
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as (select 101 as ID, '24/05/1899' as create_date from dual union all select 101, '32/03/2012' from dual union all select 102 ,'30/02/2012' from dual union all select 101 , '29/02/2013' from dual
I have a problem with Between clause used in where statement to compare two string variable.
Query is like this,
select item_code, item_deacrption from itm_master, invoce_det where im_code = item_code AND invd_item_number BETWEEN (:startNum) AND (:endNum)
Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.
now invd_item_number has one value '001003002001' if we give :startNum = '001003001002' and :endNum = '001003004006'
:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.
it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).
But how can i get around this as i don't need that value to be fetched.
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
I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
I have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss Mississ Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2) Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss'); insert into names values('Mississ'); insert into names values('Mississipp'); commit;
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
Suppose I have a table in which I have first_name, last_name, dob. Now I have to fetch on the basis of first_name=some_value, last_name=some_value and dob=some_date. I want to sort it on the basis of exactly fetched values. Let me take an example-
test table contains-
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Sharma 01-Jul-1987 Avinash Pandey 03-Feb-1988 Ankit Gupta 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Sikha Batham 17-Apr-1988
first_name last_name dob ---------- --------- ---- Manu Batham 02-Feb-1988 Manu Aggrawal 02-Feb-1988 Manu Batham 20-Jan-1985 Manu Sharma 01-Jul-1987 Ankit Gupta 02-Feb-1988 Sikha Batham 17-Apr-1988
My result is based on the approach- if matched first_name, last_name, dob --> 1st prefrence in order if matched first_name, dob --> 2nd prefrence in order if matched first_name, last_name --> 3rd prefrence in order if matched last_name, dob --> 4th prefrence in order if matched first_name --> 5th prefrence in order if matched last_name --> 6th prefrence in order if matched dob --> 7th prefrence in order
I designed the following query for the same-
Select first_name,last_name,dob,1 "Order" from test Where first_name='Manu' and last_name='Batham' and dob=to_date('02/02/1988','dd/mm/yyyy') union Select a,b,c,2 from test Where [code]......
I know that this is not the best possible solution as the table is very big and doing so many hits on that table will certainly decrease the performance.
Seemingly a very simple problem, but I can't seem to figure it out. Table below is a table that holds UserId' s and LinkId's.
CREATE TABLE UserLink (UserId NUMBER, LinkId NUMBER ); ALTER TABLE UserLink ADD PRIMARY KEY (UserId, LinkId); INSERT INTO UserLink VALUES (1, 1); INSERT INTO UserLink VALUES (1, 2); INSERT INTO UserLink VALUES (3, 1); INSERT INTO UserLink VALUES (4, 1);
I'm looking for a query in which I can plug a List of LinkId's that outputs only users that have a relationship with ALL LinkId's provided. In this example you may assume that the List is equal to (1, 2), but you can't make any assumptions about the size of the list, other than size != 0. The query to find users that match ANY of the provided LinkId's is quite simple:
SELECT UserId FROM UserLink WHERE LinkId IN (1,2)
The correct query should output:
USERID ------ 1
And it should work for any size > 0 list of values.
I have a problem with a query I'm trying to run. I need to match two columns containing names, first column (NAME1) contains only the surname and the second column (NAME2) contains a surname and initials, with the initials turning up on either side of the surname.
create table sample1 ( i number , j date, k number)
insert into sample1 values (1,'23-Apr-2010',11) insert into sample1 values (2,'22-Apr-2010',12) insert into sample1 values (3,'21-Apr-2010',13) insert into sample1 values (4,'19-Apr-2010',14) insert into sample1 values (5,'18-Apr-2010',15) insert into sample1 values (6,'17-Apr-2010',16)
I would like to get nulls , if there is no data for a date. As we can see , here i am missing the data for '20-Apr-2010'.
I did it through "UNIX" , but it's not efficient.
The data might be missing for the complete week also. I need to test in this way only for the last 7 days. I tried something like this:
select i, j , sum(k) from sample1 where j in (select to_date(sysdate - rownum) from dual connect by rownum < = 7) group by i, j