PL/SQL :: Search Rows Between Timestamps
Apr 24, 2013
I ran this following query and somehow i feel the results are wrong.
SQL> select to_char(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');
TO_CHAR(STARTTIME,'
-------------------
23-04-2013 22:43:59
23-04-2013 13:43:37
SQL> select to_timestamp_tz(starttime,'dd-mm-YYYY hh24:mi:ss') from report where dateofmonth between to_timestamp_tz('22-Apr-2013 12:00:00','dd-mm-YYYY hh24:mi:ss') and to_timestamp_tz ('23-Apr-2013 14:00:00','dd-mm-YYYY hh24:mi:ss');
TO_TIMESTAMP_TZ(STARTTIME,'DD-MM-YYYYHH24:MI:SS')
---------------------------------------------------------------------------
23-APR-13 10.43.59 PM -07:00
23-APR-13 01.43.37 PM -07:00
I am not sure why the 10 PM time is coming up in the result.
View 1 Replies
ADVERTISEMENT
Oct 5, 2012
My control file
LOAD DATA
Infile code_val_new.dat
BADFILE sample.bad
DISCARDFILE sample.dsc
[code]...
18 7BLM AGS ELEM BUILDING MATHEMATICAL EXTRACT Oct 4 2012 3:19:26:000AM
But all rows are getting rejected. What modification should i make?
View 26 Replies
View Related
Jan 4, 2013
Have a table which has 3 columns id,name,time where time is of datatype timestamp and it stores the time when the row was inserted. Need an query which accepts 2 parameters as input Ex: Start_Time,End_Time and all the rows in between the above mentioned timestamps must be deleted.
View 8 Replies
View Related
Apr 29, 2011
have a performance problem with a simple query, for example:
SELECT *
FROM CA_ADDRESS
WHERE address_k1 = 'L163'
I have an index created in column address_k1. CA_ADDRESS is a 3-4 Million Record table.
I need to improve the performance of the query. How I can improve it?
View 3 Replies
View Related
Jul 15, 2012
I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?
if not, that means I have to :
1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.
but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.
how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.
View 7 Replies
View Related
Jul 25, 2013
I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by.
View 4 Replies
View Related
Mar 5, 2008
How to select the transactions out of the database that occurred within 70 seconds of each other. The toll_date field is a TIMESTAMP field.
Problem is, I seem to only get transactions that occurred within 70 minutes of each other. On the timestamp field I break the math down into the seconds in a day and I add 70. I then subtract that value and add that value to the timestamp and I should get anything between those values right?
SELECT Acct_ID, Ln, Tag_Rd, COUNT(*)
FROM (
SELECT T1.Account_ID Acct_ID, T1.Tag_Read Tag_Rd, T1.Revenue_Date Rev_Date, T1.Toll_Date, T1.Lane_ID, T1.Plaza, T1.Lane Ln, T2.Toll_Date, T2.Plaza, T2.Lane
FROM Toll T1
JOIN Toll T2
[code]......
View 6 Replies
View Related
Jul 8, 2013
view the below select statement..why it's adding extra zero's...
select to_timestamp('2001-05-22 12:00:18.600','YYYY-MM-DD HH:MI:SS.ff3AM') from dual
output: 5/22/2001 12:00:18.600000000 PM ---why it's adding extra zeors's
my output should be as " 5/22/2001 12:00:18.600 PM"
View 4 Replies
View Related
Nov 28, 2010
I have a requirement to create the table monthly.The tablename will be the o/p of the following select query.
select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual
I am trying to create the table for creating the backup of the existing table TBR_AND_SECURITY_DEPOSIT.
create table (select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual) select * from TBR_AND_SECURITY_DEPOSIT
but it is throwing error as invalid table name.
View 2 Replies
View Related
Jan 2, 2013
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade.
The field trade_date belongs to the moment of the trade.
The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE').
The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600.
-> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800.
-> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
Example Table
TRADE_DATE --PRICE --TREND --permanently
02.01.13 11:21:42,720000000--11--UP--9801
02.01.13 12:44:03,236000000--12--DOWN--97346
02.01.13 14:05:03,845000000--11--DOWN--92485
[Code]....
View 16 Replies
View Related
Jun 29, 2012
i m creating the dynamic table every month to maintain the particular month data seperately .when the records getting inserted in the table,trigger will automatically insert the records in the dynamic table. only date alone(without timestamp) getting inserted in the dynamic table from staging. so by default ,00:00:00 is getting appended with date instead of actual timestamp. tried select to_date(to_char(:new.ACTN_DATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') INTO v_temp_actn_date from dual; but i am getting only date alone . in my table and dynmaic table datatype for date column is date
View 2 Replies
View Related
May 26, 2010
Check the following
SQL> ALTER SESSION SET TIME_ZONE = '-01:00';
Session altered.
SQL> SELECT SysTimeStamp
2 FROM dual;
SYSTIMESTAMP
[code]...
My doubt is that why Extract(HOURFROMCURRENT_TIMESTAMP) is showing 11 rather than 10?
View 4 Replies
View Related
Mar 8, 2013
When i run the below query the time zone changes from -06:00 to +06:00 How do i retain the same time zone -06:00? Im in Oracle 10GR2.
select TO_TIMESTAMP_TZ('2013-03-08'||'03:00:00.000-06:00','YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') from dual;
Output: 3/8/2013 3:00:00.000000000 AM +06:00
View 5 Replies
View Related
Mar 23, 2011
SQL statement:
SELECT user_id, jc_name, upd_time
FROM user_jc
WHERE user_id IN ( SELECT user_id
FROM user_jc
WHERE JC_NAME LIKE 'PPF\_S\_%' ESCAPE ''
[code]...
Howto have for every User_ID and the jc_name with the oldest upd_time? One record for each user_id with the oldest timestamp and the jc_name?
the output should be like this
UII00061PPF_S_SD_1st21.09.2010 19:23:46
UII00012PPF_S_Munich22.03.2011 15:44:20
View 3 Replies
View Related
Jan 11, 2007
I'm trying to generate count of the number of entries in a table for each day.The problem is the date column is of datatype timestamp and looks like this "2006-12-30 18:42:03.0"
How would I generate a report of number of entries in the table for each date (I'm not intrested in the "time" only the "date" i.e YYYY-MM-DD)?
SELECT COUNT(*) FROM my_table_name
WHERE my_date_column LIKE '2006-12-30%'
GO
It returns zero rows ( and I kno there are rows in the table) I'm using Oracle 10g.
View 2 Replies
View Related
Sep 25, 2012
how to caluclate days between two dates of single timestamp filed and with this
query
Select * from m_activity_transaction where actn_opp_id in (
Select actn_opp_id from m_activity_transaction where ACTN_ACTV_ID = 218
Group by actn_opp_id
[code]...
and i nedd to caluclate no.of days between two dates like 27-JAN-12 11.06.20.000000 AM and 08-FEB-12 05.32.54.000000 PM where actn_id is unique AND ACTN_OPP_ID IS NOT UNIQUE.
View 6 Replies
View Related
Apr 28, 2008
Is there a way to query an oracle database in an automated fashion by a timestamp field based on current timestamp, like: 04/29/08 00:00:00 - 72 hours?
View 3 Replies
View Related
Apr 12, 2011
calculating values of A & B
(both these fields a_std and a_time are coming as varchar from the parent table in a cursor.(basically they are time period and actual arrival time respectively)
i was juggling with the attempt to make varchar to timestamp or date..but caught with Round up /Round down)
Formula ->
A = Round down [A_TIME - A_STD]
B = Round up [A_TIME) - 10 minute + A_STD]
where
A_TIME VARCHAR2(8) N Time (Format" HH:MM AM/PM") eg "3:50 PM"
A_STD VARCHAR2(5) N Standard time (Format" HH:MM") eg "1:00"
Allowed values for A & B after round up/down = multiple of 10 ( 11:00,11:10,11:20 etc.)
View 10 Replies
View Related
Jun 11, 2010
I have 3 tables, user_login_event, person and resource_viewed_event. What I want to do have a report for each month, users logged in our application and then show for each month, how many records were created in table person and how many resource views events were logged in resource_viewed_event.
Lets only worry about the timestamp fields in these tables now as I want to use them to join the tables together or at least build correlated subqueries along the months. I have tried several options, all not leading to a desired result:
Left outer join. Works but its incredibly slow:
SELECT
distinct to_char(ule.TIMESTAMP,'YYYY-MM') as "YYYY-MM",
count(distinct ule.id) as "User Logins",
count(distinct ule.user_id) as "Users logged on",
count(distinct p2.id) as "Existing Users",
count(distinct p1.id) as "New Users",
count(distinct r1.id) as "Resources created"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
Tried the same with left outer joins of temporary tables created through select statements:
select
distinct ule.month as "Month",
count(distinct p1.user_id) as "Users created",
count (ule.id) as "Logins",
count (distinct ule.user_id) as "Users logged in",
count(rv.id) as "Resource Views",
count(distinct rv.resource_id) as "Resources Viewed"
[code]....
another approach is to create my own temporary tables using select statements and create fixed Month values which I can use to directly link the sets together.
select
distinct ule.loginday as "Month",
count(distinct ule.id) as "Logins",
count(distinct ule.user_id) as "Users logged in",
count(distinct p1.user_id) as "Users created",
count(distinct p2.user_id) as "Existing users1"
[code]....
performance is OK with 2 tables but the example above takes forever to execute.
Tried an approach with union but this creates new rows for each table
SELECT DISTINCT p1.MONTH AS "Month",
COUNT(DISTINCT p1.user_id) AS "Users created",
NULL AS "Logins",
NULL AS "Users Logged in",
NULL AS "Resource views",
NULL AS "Resources viewed"
FROM (SELECT To_char(person.created_on_date, 'YYYY-MM') AS MONTH,
[code]....
View 4 Replies
View Related
Dec 27, 2009
Which is better to use of Views and Materialized views so as to have good performance ? Also I need to search for a particular value in the view. Any function in sql to implement this ?
View 3 Replies
View Related
Mar 19, 2010
I'm wanting a query that will look up a named field and return all of the tables that it is held in. I've tried looking through manually but the database has hundreds of tables
View 1 Replies
View Related
Jul 5, 2010
Can we search for a column name if we have some value with us, and we are not sure which column it belongs to.
View 3 Replies
View Related
Jan 23, 2012
I am using oracle 10g 10.1.0.2.0 . I want to set lov search value means when we open lov then in search text box it show % but i want to set 1% .
View 5 Replies
View Related
Oct 12, 2012
i have a text field search_part1 in which i put the item for search another text field i send_by which contains items a single item comes in list many times.i have to find the item one by one.i have make this statement for seraching items
Declare
typ varchar2(200);
L_NAME VARCHAR2(50) := :BLOCK3.SEARCH_PART1;
begin
go_block('BLOCK3');
first_record;
loop
if :BLOCK3.SEND_BY = L_NAME then
[code]....
bt the problem is that if a single items comes more than one time then it search only one time and next time it exit.
View 3 Replies
View Related
Dec 27, 2006
I would need a SQL script or a command for PLSQL that would search for a keyword %keyword% in all tables of a database instance and in a sepcified schema of a user.
how to do this interactively, without preparing a SQL script with all exisiting tables?
View 2 Replies
View Related
Jan 15, 2009
I have an application which deploys the data to Oracle database. It has more than 25 tables and many columns. It does not have any document explaining the deployment, so I am kind of doing reverse engineering here.
I need a script which will fetch the column name or at least table names which will match with either some string or number? I found few examples on net to find out number. But I am struggling to make it work for string.
I can not work on stored proc, as I do not have access to create that on server. So, any script will work.
View 3 Replies
View Related
Sep 27, 2010
I have a table emp where empid,employee name,job,salary are three columns.I need to retrieve empid,count of emp,emp name,emp salary of job = manager and total salary of all employees with grouping by job in one query.
View 13 Replies
View Related
Mar 11, 2012
i hav a table in database with name TBH.in that table there are 25 to 30 columns.its a material details table.by using add material form i insert all the details of the material in the table.2 colums have primary key(material id and material code).
now iam making search and update form.on material code item i have WHEN-MOUSE-CLICK-TRIGGER and calling LOV and displaying record.
but the problem is when iam making changes in some fields its giving error ...UNABLE TO INSERT....FRM-40508:ORACLE error:unable to insert record.
when i check display error its showing
ORA-00001: unique constraint (DETA.SYS_C006356) violated..
View 2 Replies
View Related
Nov 8, 2010
Assuming I have the following table with its data:
Quote:
| code | description |
| A | $abc + xyz = $cba - 2 |
| B | ($12345 + $12345)/3 |
| C | $poke + $abc = $abc + 456 - 789 |
How can I do a selective search on "description" column for any word that starts with the "$" sign? In the end, this is the resultset that I am expecting:
Quote:
| $ | count of $ |
| $abc | 3 |
| $cba | 1 |
| $12345 | 2 |
| $poke | 1 |
View 5 Replies
View Related
Nov 11, 2010
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
View 9 Replies
View Related
