SQL & PL/SQL :: Record With User_id / Name And Oldest Timestamps?
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
ADVERTISEMENT
Sep 6, 2012
I want to fetch oldest record in database table based on llast updated date ; I tried following queries..it gave me different results
SELECT EVENT_ID, RETENTION_TS
FROM ( SELECT EVENT_ID, RETENTION_TS, RANK() OVER (ORDER BY RETENTION_TS) RETENTION_TS_RANK
FROM EVENT )
WHERE RETENTION_TS_RANK <= 1;
select * from EVENT where rowid=(select min(rowid) from EVENT);
SELECT *
FROM (select * from EVENT ORDER BY RETENTION_TS) EVENT2
WHERE rownum <= 1
ORDER BY rownum DESC;
Above query takes like 10 to 20 mins to return me the record.
View 7 Replies
View Related
Jul 16, 2010
I would like to retrieve user_id when I give username. Could I know which tables I need to be using for that.Example
SQL>@get_User_id Greg
b7dhgs
igh45g
where 'Greg' is a part of the name for 2 users.
View 11 Replies
View Related
Jul 6, 2012
Is the identifier functions UID always correlated with the column dba_users.user_id ?
View 4 Replies
View Related
Dec 21, 2010
I have done it through SUBQUERY AND MIN FUNCTION
SQL> SELECT LAST_NAME ,HIRE_DATE FROM EMPLOYEES
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEES) ;
but i want a smaller, simple code .
View 7 Replies
View Related
Sep 3, 2008
select
a.first_name,
a.agent_id,
a.birth_date
from
agents a
[code]........
from the above i cannot get the youngest one! this is giving me some mid age.
View 14 Replies
View Related
Mar 22, 2011
I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.
Here is my table
CREATE TABLE TM_060_SMS_TEST
(
SMS_SEQ VARCHAR2(18 BYTE),
SMS_RCV_CLC VARCHAR2(14 BYTE),
CUST_NUM VARCHAR2(12 BYTE),
[code]...
View 12 Replies
View Related
Sep 5, 2008
Do u want to get the youngest & oldest member at each location?
View 1 Replies
View Related
Mar 24, 2011
I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?
delete from
TM_060_SFS_TEST
WHERE
rowid in (
SELECT
[code]......
View 1 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
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
View Related
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
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
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
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
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
Jul 26, 2012
My procedure proc_ex is in when_validate_item trigger
I have one Multi Record data block in my form with values in its items
I need to Populate multi record block values to one Record Group using
add_group_row,
add_group_column,
set_group_char_cell to populate values to record group
Let us suppose my multi record data block looks like
item1 item2 item3 item4
10 20 50 70
25 15 30 45
45 90 47 38
75 25 85 90
30 56 78 80
how to populate these multi record datablock values to Record Group ???..Eagerly waiting for your Replies
View 3 Replies
View Related
May 16, 2012
I am loading data file using SQL Loader in TOAD 10.5.1.3 in Oracle 10g using the control file below and loading data into 2 tables post1.thead and post1.tdetl. THEAD contains item level transaction and TDETL is detail level when a transaction has a discount or promo attached to for that item. When the
LOAD DATA
APPEND
INTO TABLE post1.thead
WHEN (1:5) = 'THEAD'
TRAILING NULLCOLS
(file_type POSITION(1:5)CHAR,
[code]....
A particular THEAD value may have 0, 1 or many TDETL corresponding values.below is a sample data file. When the position 21 in the TTAIL has a value of 1 or 2, then we know that there is a promo or discount applicable to the ITEM (THEAD).
THEAD0000000002201109142011091400000000000002091 1 0 -1
TTAIL0000000003000000
THEAD0000000012201109142011091400000000000002091 1 0 -1 20110914-1
TDETL0000000013CMPSPL1004 0 000000010000P0000000000000019990000000000000000200000
TTAIL0000000014000001
THEAD0000000021201109142011091400000000000002091 1 0 -1
TDETL0000000022EMPDSC1005 0 000
TDETL0000000023SCOUP 1006 0 973452 000
TTAIL0000000024000002
What I want to acheive is to accurately reflect a TDETL to its corresponding THEAD, as both THEAD and TDETL are loaded into separate tables. How can we have the 2 records correlated?
View 11 Replies
View Related
Aug 26, 2013
I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name
Null Type ------ ---- ------------ ID_P NUMBER NAME_P VARCHAR2(12) DATE_P TIMESTAMP(6) Records---------------------1 loosi 22-AUG-13 01.27.48.000000000 PM1 nammi 26-AUG-13 01.28.10.000000000 PM2 kk 22-AUG-13 01.28.26.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
now i have to select below 2 rows how can write select qurie for this?
1 loosi 26-AUG-13 01.27.48.000000000 PM2 thej 26-AUG-13 01.28.42.000000000 PM
View 4 Replies
View Related
Sep 13, 2011
I have a fairly standard Purchase Order form which contains pre-loaded data (been uploaded from an XML file).When the Purchase Order is processed, the form updates a Price History table only if the Price on the PO_Details changes.The code for updating the price history table is contained in a PRE_UPDATE trigger on the PO_Details Data Block.
No other data changes on the PO_Details table.I now want to change this so that the Price History table is updated even if the price does not change i.e I want to create a history record for each record on the PO_Details irrespective of whether it was updated or not.
Is there an alternative trigger that I can move my code to (ie move it from PRE_UPDATE) to some other trigger that is fired for each PO_Details record even if there is no change.
View 4 Replies
View Related
Apr 21, 2010
I have the following case to solve:
Example Table:
Nr_ordPos1Pos2Itemqty
O4018510000107 170,00
O4018520000107 30,00
O40651010000107 500,00
O40651020000107 50,00
O4114510000107 300,00
O31141010000107 50,00
O3114520000107 50,00
I need to create a query that returns record by record a field qty_progr with the cumulate qty considering previous records. The result should be the following:
Nr_ordPos1Pos2Itemqty qty_progr
O4018510000107 170,00 170,00
O4018520000107 30,00 200,00
O40651010000107 500,00 700,00
O40651020000107 50,00 750,00
O4114510000107 300,00 1050,00
O31141010000107 50,00 1100,00
O3114520000107 50,00 1150,00
View 8 Replies
View Related
Jun 29, 2013
I create a data block on a table when I am inserting new record only one record have been saved. Last record no longer exist.
View 4 Replies
View Related
