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"
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:\NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I use Oracle 10.
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]......
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location,
A.location_date, A.downloaded_date, A.downloaded_id, A.file_size,
A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded
from SDT_LOG A Inner Join ACTIVITY_LOG B
On A.file_name = B.file_name
and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
Example of
Name Type 1st record.
---- ------- --------------
FILE_NAME VARCHAR2(50) STLMK.txt
FILE_DESC VARCHAR2(50) NON-RESIDENT
FILE_LOCATION VARCHAR2(50) L:NonResFiles
YEAR NUMBER(4) 2008
LOCATION_DATE DATE 10/10/2007
DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM
DOWNLOADED_ID VARCHAR2(50) Cindy
FILE_SIZE CHAR(10) 16212
DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
Example of
Name Type 1st record
---- ------ -----------
FILE_NAME VARCHAR2(50) STLMK.txt
ACT_CODE CHAR(2) D
ACT_DATE DATE 10/10/2007
ACTIVITY_ID VARCHAR2(50) downloaded on
DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D
STLMK.txt Cindy S
STLMK.txt Jason D
STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
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.
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?
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 RelatedI 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.
I need to copy records from a working table to a history table. I have the following sql statement
insert into test.history
(equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status)
select (equip_ID, state, manufacturer, install_year, capacity,
group_ID, Test_status
from test.info_AP
Table test.history has one more field in it called test_year. I need to fill this field when I do the insert. Can't use an after update trigger as the field is currently set to not allow nulls.
Once I add an extra column to link the two tables the number of rows is reduced by one, however when I try to get this extra record I find none matching the same criteria that led to reducing the records.
SQL> SELECT count(*)
2 FROM N_CONTRACT NC, N_WITHDRAWAL_REQUEST NWR
3 WHERE NC.fk_temP_withdrawal_req_serial = NWR.SERIAL_NUMBER;
COUNT(*)
----------
2243
[code]...
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]....
Why cant oracle give an extra parameter for summary function like MAX and MIN...For example
select max(salary,2)
from empWill give the second max salary.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
i am trying to read the file, for which i m using the following:
l_utlfile_hdr := utl_file.fopen(p_dir,'header_evnt.txt', 'R');
utl_file.get_line(l_utlfile_hdr, l_hdr_evnt);
it gets these three characters at the start of the line
l_hdr_evnt = 
So i saved header_evnt.txt as UTF-8, and used utl_file.fopen_nchar, utl_file.get_line_nchar, which got rid of first two characters, but still has ¿. How to get rid of that?
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 RelatedCheck 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?
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
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
I am modifying a stored proc that i wrote a couple of years ago. The change is that i want certain inputs to accept strings of characters (comma delimited) so they can be parsed and processed in the proc. The problem is, when i pass very little data (about 50 characters) It fails saying the character sting buffer is to small.
I have setup the input as a type of a column that is a clob and i have also tried using just vahrchar2 (with no size set).
I do below alter command
alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'
I mark X for that space ...
The command is commit .
But there is problem when do backup .
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.
I would like to create a procedure in oracle to remove extra spaces coming as blank spaces(squared ones)in strings.
View 32 Replies View Relatedhow 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.
I am calling a function in front end, from front end i am passing space, now i want to trim space in parameter itself, it's psssible, assume the below is the function defination, i want to trim the p_region_name parameter like this trim(p_region_name), is this possible?
FUNCTION add_country_region_column (
p_s_country_code_iso_2 IN varchar2_table_type,
p_type IN d_country.c1_type%TYPE,
p_name IN d_country.c1_name%TYPE,
p_desc IN d_country.c1_desc%TYPE,
[Code]....
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 RelatedI do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M
But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...
The command is commit .
But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?
I've a query that sum up data from one table. But I've to convert them into rows.
Summary query result.
COL1 COL2 COL3 COL4
1000 2000 3000 4000
Test Table creation script.
CREATE TABLE TEST_TBL
(
COL1 NUMBER(14,2),
COL2 NUMBER(14,2),
COL3 NUMBER(14,2),
COL4 NUMBER(14,2)
) ;
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000
A2 2000
A3 3000
A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL
UNION ALL
SELECT 'A2', COL2 FROM TEST_TBL
UNION ALL
SELECT 'A3', COL3 FROM TEST_TBL
UNION ALL
SELECT 'A4', COL4 FROM TEST_TBL
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.)
I have one issue while loading the value through sql*loader the last column data is SG1 and when its loaded , it is length of this columns is showing 4 char. Unable to understand, how to find this extra space. Though used TRIM but does not work.
View 8 Replies View RelatedI 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]....
I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a
select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)
however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.