Displaying Records Between Specific Times On Specific Dates?
Apr 11, 2011
I have a database which consists of various orders and various field.
I have a variable called createddatetime . I want that whenever i should run the database it should display records from
Yesterday 06:00:00 am to Current Date 05:59:59 am
Now to implement this i tried to put this syntax
and to_char(Createddatetime,'dd/mm/yyyy HH24:mi:ss') between 'sysdate-1 06:00:00' and 'sysdate 05:59:59'
But nothing comes up
where as definitely there are records between times because when i do and Createddatetime between sysdate-1 and sysdate I see valid records coming up.
View 3 Replies
ADVERTISEMENT
Feb 14, 2012
I have for example two text items(number of records to display is set to 9). What I want is when i go to studentprofile block, it should automatically shows his subjects that are already taken up. the first text item is for 1st semester subjects and the other for 2nd semester.
here is the structure of my subjsec table:
ID number(1)
subj_code varchar2(10)
day varchar2(10)
and for subjects table:
subj_code varchar2(10)
description varchar2(50)
units number(1)
semester varchar2(10)
I restricted subjects that are in 1st sem. So i did something like this in my
DECLARE
CURSOR studgrade_cur IS
SELECT g.stud_id, g.grade, g.subj_code, s.description, s.units, s.semester
FROM studgrades g , subjects s
WHERE g.subj_code = s.subj_code
AND g.stud_id = :Studentprofile.student_id;
[Code]....
View 32 Replies
View Related
May 29, 2012
I'd like to know if it is possible to track DML actions issued on a specific table by a specific user, for example , i tried :
AUDIT SELECT on SCOTT.DEPT by HR by ACCESS;
I get an error, where is my syntax error ?
i want to know if it's possible to do it without trigger ?
View 7 Replies
View Related
Apr 5, 2008
i have columns called data_received(data received from server) , URL (sites visited)
now i want to display top 50 sites based on highest data_received.URL column will have some sites repeated.
View 1 Replies
View Related
Feb 12, 2007
i'm creating a website where i can search between 2 specific dates which user will enter in the format of DD-MM-YYYY. the 2 dates will be inserted into 2 fields which is $input1 and $input2.
extract($_POST);
//to set the date format
//***************************
$input1 = date('DD-MM-YYYY h:i:s');
$input2 = date('DD-MM-YYYY h:i:s');
[code]...
but i get the error message saying...Warning: odbc_exec() [function.odbc-exec]: SQL error: [Oracle][ODBC]Option value changed., SQL state 01S02 in SQLExecDirect in C:phpwwwaaas_bbb.php on line 86...Error in SQL statement
View 2 Replies
View Related
May 9, 2013
I want to start by saying I am brand new to SQL. I have an access database linked to my oracle and am trying to query a very specific set of data and I can't seem to narroe it down. I have 244,000 lines in the DB and I'm trying to find items on a specific trype of vendor agreement. I may have the same item on multiple agreements. ex 1-, 1a-, 2-,2a-,3-,3a-,4-,4a-,5-,5a-,6-,6a-,7-,7a-,8-,8a-.
each item by agreement is on it's own line.
ex.
item ven_agrmt_ref
233 1a-xxx
233 4-xxx
233 4a-xxx
255 4a-xxx
I need to find a way to select just items that appear on a 4- or 4a- and no other agreement reference. The query I did so far pulls all of the 4- and 4a- agreements but will also pull items,like #233 in the example above, but not showing the 1a- agreement. I need it to overlook that item eventhough it does appear on the agreement I am looking for but also has an agreement I am not. the statement I am using right now is:
SELECT item, ven_item, ven_agrmt_ref, base_cost, vendor
FROM "all items by agreement"
WHERE ven_agrmt_ref >= '4'
ORDER BY item
View 3 Replies
View Related
Apr 11, 2011
Is there any tool available for audit records from a specific Machine/terminal Only?
View 1 Replies
View Related
Mar 14, 2012
I have 1M Records coming from an External Data source as a Flat File (using ETL). Now I need only Yesterday's data only to load in my Database Table.
this can be done using Bulk Load and Filter.
write the CODE.
Second Part:-
Hint: if I need to update only those records been updated Say the Address1 field is updated. So this records need to update in my Master Customer Table.
If I have many fields in table and any records that are modified (coming to me from External Datasource as a Flat file) how to identify and update that record in my Master Customer Table?
View 5 Replies
View Related
Dec 20, 2012
I have some data that I need to group to the Month, Day, Year, Hour and minute in a subquery. Then I need to summarize it to the Month and year in the parent query.If I try to group by the field itself, it is not taking it down to hour and minutes - just the day, so I am losing records.if I do a TO_char (visitdate, 'DD-MON-YY HH:MI AM') in the subquery, then the main query no longer sees it as a date, so cannot do my TO_CHAR(VISITDATE,'MON-YYYY') in the parent. I could parse out the pieces using string manipulation, but that seems rather silly.Is there a way to keep as a date in my sub query and then convert to a string?
it looks a little like this, with some other fields that I have to max, sum ...
visit provider person visitdate
1 2 1 12/20/2012 10:30
2 2 2 12/20/2012 10:30
3 2 5 12/20/2012 11:30
4 3 3 12/21/2012 11:30
5 3 4 12/21/2012 11:30
I need to boil this down to
provider visitdate
2 12/20/2012 10:30
3 12/21/2012 11:30
2 12/20/2012 11:30
Then I use that in a subquery where I use just the month and year TO_CHAR(VISITDATE,'MON-YYYY') AS APPT_MO_YR right now if I do a group by visitdate on the subquery it returns
provider visitdate
2 12/20/2012
3 12/21/2012
even if I do a group by to_date(visitdate, 'DD-MON-YY HH:MI AM') it is still returning :
provider visitdate
2 12/20/2012
3 12/21/2012
View 4 Replies
View Related
Jun 9, 2010
I want to insert only specific number of columns into a table by using Bulk collect and Forall.
SQL> create or replace procedure bifa_proc
2 is
3 type etab is table of emp%rowtype index by binary_integer;
4 erec etab;
5 cursor c is select * from emp;
6 begin
7 open c;
[code]...
Warning: Procedure created with compilation errors.
PLS-00382: expression is of wrong type
PLS-00436: implementation restriction: cannot reference fields of
BULK In-BIND table of records
How to insert specific number of columns without declaring multiple table type definitions for each column by using bulk collect and forall.
View 5 Replies
View Related
Sep 3, 2010
My problem is that I am working with a balance (Infor/WorkBrain - Time & Attendance) I have a balance called Bank Holiday(bal_id 10044). Every holiday that an employee works, he is allowed to take another day off in lieu of the day he worked. The employee has up to twelve months to take that holiday.
My job is to figure out when he worked that holiday (not a problem) and send an email/note at the 11 month mark and notify the employee and supervisors that said employee has 1 month to use his banked holiday.
Here is what the employee_balance log looks like.
EBLOG_IDEMP_IDBAL_IDEBLOG_DELTAWRKS_WORK_DATEWRKS_IDEBLOG_MESSAGE
51200128537100441 10/15/2010 5281229TCODE_NAME:FR_HWS
51150828537100441 7/1/2010 5136881TCODE_NAME:FR_HWS
51150928537100441 7/27/2010 5136942TCODE_NAME:FR_HWS
51150528537100441 8/1/2010 5137137TCODE_NAME:FR_HWS
5294372853710044-1 8/31/2010 5137173TCODE_NAME:FR_HWO
52947828537100441 6/3/2010 5001014TCODE_NAME:FR_HWS
My problem is the data/constraints I am working with. I can tell what day the holiday was based on a time code (EBLOG_MESSAGE) and EBLOG_DELTA shows a positive 1. I can also tell when the employee has used a bank holiday (EBLOG_DELTA is -1) and EBLOG_MESSAGE = FR_HWO.
But how to I perform the deduction so that I get four most current dates. Meaning since my first date that the employee gets for a holiday wokred is 6/3/2010 but the employee used a bank holiday on 8/31/2010. So I want to display the dates 7/1/2010, 7/27/2010, 8/1/2010 and 10/15/2010.
My preference would be not to add anything new to this table as it is a core table for Infor/WorkBrain.
View 2 Replies
View Related
Jan 24, 2011
Can we do a specfic word count in sql. Requirement is there is a string "I am very very poor in sql" the word "very" occurs two times, so the result should be 2.
View 16 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the below condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 8 Replies
View Related
Dec 3, 2010
I am using oracle11g. I want to truncate subpartition on specific partion.
I have partition on statewise. Each state partion has 7 day sub partition.
For intance,
Partion TX
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion CA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Partion IA
Sub partition MON, TUE, WED, THU, FRI, SAT, SUN
Now i want to perform following tasks.
1. Need to truncate TUE sub partiion on TX partition.
2. Need to truncate WED sub partiion on CA partition.
3. Need to truncate SUN sub partiion on IA partition.
How do we do this?
The below statment truncate all TUE partition on all the partitions.
ALTER TABLE TRX_TABLE
TRUNCATE SUBPARTITION TUE;
How do i tuncate specfic sub partition on specific partition?
View 4 Replies
View Related
May 6, 2010
query only specific date only. example: '06-MAY-2010'.
1.from the statement below, it will display out more than 06-mAY-2010.
2.if i want more the date from 03-MAY-2010, 04-MAY-2010 and 05-MAY-2010.
select * from PNG_ORA_SERVER_PERF where SERVER_NAME = 'MLYDESPINTF1' and DATE_TIME >= TO_DATE('06-MAY-2010','DD-MM-YYYY');
View 3 Replies
View Related
Oct 18, 2011
I need to check if a small collection contains a specific value. I know I can use the .exist method to let me know if the nth element exists. But is there a similar syntax for checking if an actual value exists?
So the below collection has 6 values (300,301,310,320,400,410) after the cursor values are fetched. I want to use something similar to the below exists syntax in order to search the collection for a value (rather than position) within an IF statement.
The below code shows the kind of thing I want to do. Currently, unless my test value (310) is in the range 1 to 6 the test will always return FALSE. But is there a similar syntax for testing against the value?
The example I have given is a simplification of what I need. In reality there will be more than one test value... returned by another cursor.
DECLARE
CURSOR c_type_id_usg
IS
[Code]....
-- get the list of sms type id usg values
OPEN c_type_id_usg;
FETCH c_type_id_usg bulk collect into l_type_ids;
CLOSE c_type_id_usg;
-- the above returns value 300,301,310,320,400,410
IF l_type_ids.exists(310)
then
dbms_output.put_line('I have found an entry ');
else
dbms_output.put_line('I have NOT found an entry ' );
end if;
END;
View 2 Replies
View Related
Oct 14, 2012
Say, a table has existing columns of A, B, D, and E.Now I want to add a new column C. But I want this column appear between B and D. I found that the newly added column always goes to the end of the table, i.e., A, B, D, E, C, instead of, A, B, C, D, E.
View 6 Replies
View Related
Jun 11, 2012
How to add a new column to specified position in a existing table.I have using the oracle database 10g.This below code is not working in oracle 10 g
example:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER FIRST:
ALTER TABLE EMPLOYEE ADD DEPT NUMBER AFTER JOB:
View 11 Replies
View Related
Aug 17, 2012
I have a requirement with source table has duplicates so i need to select a row based upon the a condition.
Source table
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
1 JOHN 2000 A 20
1 SAM 3000 A 30
2 TOM 500 1 40
2 SUNG 1500 1 50
Desired Output
EMP_NO ENAME SALARY CODE EMP_ID
1 TOM 1000 1 10
2 SUNG 1500 1 50
I tried with MAX and DENSE_RANK but its not getting me the first row.
View 6 Replies
View Related
May 14, 2013
I'am using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit with the following:
db_flashback_retention_target 1440
undo_management AUTO
undo_retention 900
Is it possible to enable flashback only to some specific tables?
For example, we are only interested in the capability of flashback tables scott.emp and scott.dept, no more.
View 2 Replies
View Related
Oct 10, 2008
i cant find any oracle specific methods for chunking data -if i had returned rows of numbers, how could i chunk them into ranges?
View 2 Replies
View Related
Mar 20, 2013
cookiemonster wrote on Wed, 20 March 2013 22:01Or if the user does anything to wipe the modified data - clear block, enter query etc - forms will also raise the alert.
i've uploaded a new RTF for Spanish but wen i login with the Spanish creds and run the program. Its not showing the changes i've chosen the right language and territory code.
View 1 Replies
View Related
Oct 11, 2012
i am using one stored procedure where in one variable which is declare as date value is coming like that '10-OCT-12 11.30.54 AM' and i am inserting this value in one table which has one column vdate with date datatype but it is not inserting there.
View 16 Replies
View Related
Jul 20, 2012
I want to recompile a single procedure that is part of a package, without re-compiling other procedure/functions present in that package, is it possible?
View 1 Replies
View Related
Mar 18, 2013
I have a table with data in below format:
Table A
Id Value
1 R
2 S1
2 S2
3 T
the o/p in below format:
Col
R|S1|T
R|S2|T
The general is that multiple value for any given id must be aggregated separately.
View 1 Replies
View Related
Jan 11, 2013
I need a query to find list all tables in a schema which does not have 'ADDRESS', 'CITY', 'STATE' columns.
View 8 Replies
View Related
Aug 12, 2010
I need to get the number of rows based on one condition for certain time period. For example, writing a query for 3(any) hrs , i need to get the output as count of rows as hourly basis. Like these:
Date Time Count
12/08/2010 16:00 234
12/08/2010 17:00 345
12/08/2010 18:00 645
View 12 Replies
View Related
Oct 17, 2012
This is what I've been trying to do.
I have the following table:
FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc
I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.
View 4 Replies
View Related
Apr 15, 2010
I develop a form using Devloper9i AS, oracle 9i, Winxp
I need to use items like "ActiveX Control, OLE Container, Sound " but they are marked as obsolete by Developer9i AS;
so how can I use like this type of items when I establish a form design ".fmb" .
View 2 Replies
View Related
Nov 13, 2013
I have "After header" process (with PL/SQL code) in "Page Rendering" section and want to navigate to some page after this process has been completed. I created a branch (On Submit: After Processing) with Target Type: URL. But this branch is executed only after "Page Processing", not after "Page Rendering". Maybe , there is any APEX API functions for pages navigating..
View 2 Replies
View Related
