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'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.
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".
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:
- ProdPL/SQL Release 10.2.0.5.0 - ProductionCORE 10.2.0.5.0 ProductionTNS for Linux: Version 10.2.0.5.0 - ProductionNLSRTL Version 10.2.0.5.0 - Production.
I have a problem when creating a dynamic statement. The problem is in the bind variable ':OLD.CUST_NAME' ,..my question is is there an escape character to treat the bind variable as a literal string?
{code}v_str2 := '''CUST_NAMES='''||'||'|| ':OLD.CUST_NAME' ; EXECUTE IMMEDIATE 'create or replace trigger trg_' || SUBSTR (rec_cur.table_name, 1, 26) || ' before insert or update or delete on ' || rec_cur.owner || '.' || rec_cur.table_name || ' declare begin if UPDATING then FIFAPPS.ibug.log_errors('|| v_str2 ||' ); end if; end;';
{code}
I want the output in a trigger something like this:{code}
if UPDATING then FIFAPPS.ibug.log_errors('CUST_NAMES='||:OLD.CUST_NAME );{code}
If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.
select length('string with more than 4000 char) from dual;
i write a select statement in proc that contains 44 columns.
when i precompile it. it is showing the error: implicit conversion of string literal to "char *" is deprecated.when i compile the same select with 40 columns it is not showing any error.
but for more than 40 columns (41-44) it is showing the above error.
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]....
I have a string in this format '2011/06/01 00:00:00'. How do I convert this to date format.I tried to_date,to_char and they give errors invalid number & literal does not math format string. I don't have much control on the string since that is the way is comes from the application.
INSERT INTO JOB_EXECUTION (RUN_DATE ,RUN_ORDER ,RUN_DIV ,RUN_TYPE ,RUN_FMT ,PICKUP_STATUS ,START_TIME ,END_TIME ,REMARKS) VALUES (sysdate-1,1,'AE','FILE-HFM','NY_PLAN-2014','FINISHED',TRUNC(SYSDATE),SYSDATE,'"Load Process is INITIATed: with [BATCHID:= FILE-HFM_RUN_ORDER#_5 ]: as of @ 26-Sep-2013 10:26:50. AFM-Load END PROCESS: as of @ 26-Sep-2013 10:26:52. AUTO-LOAD RUNNING :as of @ 26-Sep-2013 10:26:57. FILEs-LOAD PROCESS: as of @ 26-Sep-2013 10:26:59. ALLOCATIONs PROCESS: as of @ 26-Sep-2013 10:27:52.
[code]....
The select I tried.
WITH reporting_info AS ( SELECT b.run_date,b.run_div,b.run_type,b.run_fmt,1234 as Batch_id, start_time as BATCH_START_TIME, end_time AS BATCH_END_TIME, REPLACE(substr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'FILEs-LOAD PROCESS: as of @ ')),chr(10))-2 ),'FILEs-LOAD PROCESS: as of @ ','') FILE_LOAD, REPLACE(substr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')), 0,instr(substr(remarks,instr(remarks,'ALLOCATIONs PROCESS: as of @ ')),chr(10))-2 ),'ALLOCATIONs PROCESS: as of @ ','') ALLOCATION,
[code]....
Since the date format does not contain AM or PM in it ... while subtracting time values crossing midnight the value goes in negative .... How to subtract two date strings with out AM or PM values to get a positive value ?
We store the date in String Type format of "MON-YYYY", we require to run the report base on that string contain date column, During the Query execute the Error Display of (ora-01858: a non-numeric character was found where a numeric was expected)
The Query is :
select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,XX from ( select period_name,doc_sequence_value,doc_sequence_id,date_created,name,JE_SOURCE,TO_DATE(XX,'DD/MM/RRRR') XX from ( select TO_CHAR(TO_DATE(('01-'||substr(gl.period_name,1,3)||'-'||substr(gl.period_name,5,2)),'DD/MM/RR'),'DD-MON-RRRR') xx,
I am trying to insert a row in a table and getting the below error.
SQL> insert into tbl_force_charging(ANI, date_time, durations,src, circleid) 2 values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR' ) 3 ; values ('9569333585','29-JUN-11 03.19.41.000000000 PM','1027','51010','BIR') * ERROR at line 2: ORA-01830: date format picture ends before converting entire input string
Table Structure is
Name Null? Type ----------------------------------------- -------- --------------------------- ANI VARCHAR2(10) DATE_TIME DATE DURATIONS VARCHAR2(10) SRC VARCHAR2(10) CIRCLEID VARCHAR2(10) SQL>
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?
when i run this query i am facing date format error.
select sbrueregister.UEIMSI,sbrueregister.fapid,sbrfapslid.slid,sbrfapslid.ACTIVATION_TS,sbrfapslid.DEACTIVATION_TS from SBRFAPSLID INNER JOIN sbrueregister ON sbrfapslid.fapid=sbrueregister.fapid where sbrfapslid.slid='1234567890' and sbrueregister.registeredat between TO_DATE('2013-02-1.12.0. 10. 123000000','YYYY-MM-DD HH.MI.SS.SSSSSS')and TO_DATE('2013-02-1.12.9.10.123000000', 'YYYY-MM-DD HH.MI.SS.SSSSSS');
ORA-01830: date format picture ends before converting entire input string
Is there a way to loop through a list of literal values.
For instance create table car( name varchar2(11), passengers int, price int );
insert into car values ('fiat',1,1000); insert into car values ('bmw',2,2500) insert into car values ('ford',2,1500) insert into car values ('ferrari',4,5000)
select max(price) from car where passengers=1
How can i in a single query do this for where passengers = 1 then passengers = 2 then passengers = 3 etc where i have a list of possible values for passengers.
Just to update I realise this can be done with
select name, max(price) from car where passengers in (1,2,3) group by name
but in just wanted to know if there is a way of iterating through a literal list in tsql
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
I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.
