PL/SQL :: Retrieving Periodical Data

Mar 7, 2013

I have a requirement like to retrieve past 6 months data and i have used below query.

select count(1) from event where trunc(start_datae) between trunc(sydate)-180 and trunc(sysdate);it is giving the results but am not sure whether it is giving correct data or not.If ran the above query it is taking more time to execute.

Is the above approach is correct? Is there any difference between trunc(sysdate)-180 and trunc(sysdate-180) ?

View 12 Replies


ADVERTISEMENT

Retrieving Data From 3 Or More Tables

Apr 12, 2008

I have a problem with my Oracle 9i SQL Query and I'm struggling to get it done.

I have three tables namely Student, Lease and Room and want to retrieve data from these three tables.

I want the Student name, the Lease details and the Room No from these tables.

The problem with my SQL query is that I get all the information from the tables except from the Room table, where in the column it show Room_No but the values are not displayed, the query is given below.

SELECT STUDENT.STU_FNAME, STUDENT.STU_LNAME, LEASE.LSE_NO, LEASE.LSE_DURATION, LEASE.LSE_STARTS, LEASE.LSE_ENDS, ROOM.ROOM_NO

FROM STUDENT

LEFT OUTER JOIN LEASE ON LEASE.STU_ID = STUDENT.STU_ID
LEFT OUTER JOIN ROOM ON ROOM.PLACE_NO = LEASE.PLACE_NO;

View 3 Replies View Related

SQL & PL/SQL :: Retrieving Data From Table

Mar 21, 2013

i have requirement, that is to retrieve the data from pl/sql table.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
[code]....

PL/SQL procedure successfully completed.now i want receive the data from particular x record to y record

View 8 Replies View Related

SQL & PL/SQL :: Retrieving Data Between Two Dates

Nov 3, 2011

I have one table when I am querying like below

select * from timeoffreqitem
where timeoffreqitemid=134

getting data like below

134 144 07-OCT-11 13-OCT-11 134

I need to see this result as below.

134 144 07-OCT-11 13-OCT-11 134
134 144 08-OCT-11 13-OCT-11 134
134 144 09-OCT-11 13-OCT-11 134
134 144 10-OCT-11 13-OCT-11 134
134 144 11-OCT-11 13-OCT-11 134
134 144 12-OCT-11 13-OCT-11 134
134 144 13-OCT-11 13-OCT-11 134

I am looking at multilple optons.

View 1 Replies View Related

SQL & PL/SQL :: Retrieving The Data Based On Count?

Apr 2, 2010

I want to retrieve the data based on the count.

Sample data
AccountSubAccountDate
11.12-Mar-10
11.23-Mar-10
11.34-Mar-10
11.42-Mar-10
11.56-Mar-10
21.67-Mar-10
21.78-Mar-10
21.82-Mar-10
3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10
42.616-Mar-10
42.717-Mar-10

What i want is I want the data which account is having more than 5 subaccounts within the last one month. Also need data with other date criteria like last 100 days if more than10 sub accounts. need single query.

Output is :

3210-Mar-10
32.111-Mar-10
32.22-Mar-10
32.313-Mar-10
32.414-Mar-10
32.52-Mar-10

Having more than 8lacks in my database. i wrote the query but it is taking much time and didnt give the data even after 14 hours.

My query is :
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-35
group by b.account having count(b.subaccount)>5)
union
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-100
group by b.account having count(b.subaccount)>10)

how to retrieve the Subaccounts which satisfies my requirements.

View 33 Replies View Related

Forms :: Retrieving The Data According To Where Clause?

Jul 30, 2010

I want if the user write for example in text box 'AM TK' the query display the resualt which has am alone tk alone, and that has both. I know that i should use the Like with % but i do not know how to write it in the set property. I have wrote

set_block_property('Employee_Other',default_where,'Upper(name) like '''||UPPER(:key_search.person_name)||'''') ;

This will bring the resualt just if the user write am tk. How i can modify it to return value as i explained above.

View 6 Replies View Related

Windows :: Retrieving Data From Corrupt XP?

Oct 9, 2011

i am using oracle 9i database.i want to retrieve data from corrupt windows xp.but i do not know what i do for that.

View 2 Replies View Related

PL/SQL :: Retrieving Data Between two Dates?

Apr 3, 2013

I am using Oracle 11g version

create table ORG(Name char(20),Datetime char(45),val1 number);

insert into ORGvalues('abc','10/29/2012 13:00','1.5')
insert into ORGvalues('abc','10/29/2012 13:05','1.5')
insert into ORGvalues('abc','10/29/2012 13:10','1.5')
insert into ORGvalues('abc','10/29/2012 13:15','1.5')
insert into ORGvalues('abc','10/29/2012 13:20','0.00')
insert into ORGvalues('abc','10/29/2012 13:25','0.00')

[code]....

while I am retrieving data between two dates. from table ORG. select from ORG where datetime between '29/03/2013' and '30/03/2013' order by datetime asc*

the output is coming like this,

O/P:

abc 29/03/2012 13:00 1.5
abc 29/03/2012 13:05 1.5
'abc 29/03/2012 13:10 1.5
'abc 30/03/2012 13:15 1.5'
'abc 30/03/2012 13:20 0.00
'abc 30/03/2012 13:25 0.00

[code]....

My problem in above out put is Here I am getting previous year data also at same day and month I want data only between the dates which are specified in query

But here needed output is

*'abc 29/03/2013 13:35 0.00*
*'abc 29/03/2013 13:40 2.1*
*'abc 30/03/2013 13:45 2.3*
*'abc 30/03/2013 13:50 2.1*
*'abc 31/03/2013 13:55 2.1*
*'abc 31/03/2013 14:00 2.2*

Note:my datetime datatype is char

My dear friends actually I am getting data like this date as a string(CHAR datatype) from third party tool.Due to this reason only I treat datetime attribute as string.

View 10 Replies View Related

Taking Periodical Backup Of Database Server

Sep 18, 2012

I am new to DBA activities.I am working on 11.2.0.1.0 version in Oracle.

My database keeps on growing so as a security measure, I am asked to take backup of the database periodically without shutting down this database. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups... Now sure how to proceed on the activity,

View 4 Replies View Related

SQL & PL/SQL :: Retrieving Data From Multiple Tables Using Procedure

Nov 7, 2011

how to write this query using procedure and how it can display.

SELECT iusr_id,
so_curr_assign_to,
comp_id,

[Code]....

View 11 Replies View Related

Performance Tuning :: Retrieving From Data Is Slow

Jan 12, 2011

I'm extracting/retrieving the data from the oracle database using Java application it's bit slow. However, when I retrieve from the SQL server it's faster than oracle.

View 6 Replies View Related

Windows :: Taking Periodical Backup Of Database Server?

Sep 18, 2012

I am working on 11.2.0.1.0 version in Oracle.

My database keeps on growing so as a security measure, I am asked to take backup of the databse periodically without shuttingdown this databse. Since this task has to be done periodically, I have to schedule the job for this task.? I know how to take export of the database.. but not sure about the periodical backups..

View 1 Replies View Related

Retrieving Extra Rows

Dec 14, 2011

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.

View 2 Replies View Related

Forms :: Retrieving More Than One Row In Form

Dec 20, 2012

i have created two data blocks

First data data block contains a list item and a text item based on the selection of the list item and the value in the text item i need to retrieve more than one row in another data block whose NUMBER OF RECORDS DISPLAYED property are set to 10.

I have a Button in the first data block. So in the WHEN BUTTON PRESSED trigger i wrote SELECT INTO clause which is raising "exact fetch returns more than requested number of rows".Then i used a cursor in the WHEN-BUTTON-PRESSED trigger in the first block to fetch row by row and assign it to the items in second block. But i am able to retrieve only one record in the second block.

View 5 Replies View Related

SQL & PL/SQL :: Retrieving Wanted Records

Dec 2, 2010

My table XXX has following records with data

ID AMOUNT ID_TYPE APPROVE_FLAG
A1 2 A N
B1 100 B N
A2 3 A N
A3 100 A Y

The Select Query should be..All the records should be considered and if the (amount is greater than 50 and approve_flag is N )then except that records all records should be considered.The output should be 3 rows; 2nd row (B1) should not come. writing a select statement for the above conditions.

View 4 Replies View Related

SQL & PL/SQL :: Hierarchical Retrieving With Cycles

Sep 29, 2011

first of all sample data;

create table test_circular_data(c1 varchar2(10),c2 varchar2(10));
insert into test_circular_data values ('c1','l2');
insert into test_circular_data values ('c1','l3');
insert into test_circular_data values ('c3','l3');
insert into test_circular_data values ('c4','l3');

commit;

There is a circular relation between columns c1 and c2, so what I'm trying to retrieve is something like that :

c1--> l2 --> l3 --> c3 --> c4

The steps to get that result is :

1.- c1 related to l2 : c1-->l2
2.- c1 related to l3 : c1-->l2-->l3
3.- l3 in the list and related to c3 : c1-->l2-->l3-->c3
4.- l3 in the list and related to c4 : c1-->l2-->l3-->c3-->c4

View -1 Replies View Related

SQL & PL/SQL :: Retrieving Extra Rows

Dec 14, 2011

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.

View 6 Replies View Related

Forms :: Retrieving Record

Sep 6, 2012

I have two blocks on which I am having relation based on booking number and employee code. As I am doing a query, booking number is getting fetched in employee code's field and employee code is getting fetched in booking number's feild. Because of this, I am not able to update the form. It is giving me error unable to update record unique contraint voilated for primary key which is quite obivious. But why the data could get shuffled in each others field though during inserting its going properly.

View 7 Replies View Related

SQL & PL/SQL :: Retrieving Records From Tables

Mar 27, 2010

I want to retrieve the data from ex.tables. How to get this.

table1:

Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key

table2:

Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.

Out put:

I want the data like Account_no who is having more than 3 Account_sub_no values.

But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.

View 5 Replies View Related

SQL & PL/SQL :: Retrieving Related Records As Groups

Mar 12, 2008

I have a table of N records with: Name SeqNo ID Col4 ... ColX

where Name and ID are non-unique, and SeqNo is a monotonic non-consecutive sequence 0 .. N that is unique within ID..I'd like to generate the following 'groups': For each record where SeqNo = 0, sorted by Name, create the 'group where ID is the same, ordered by SeqNo irrespective of the

values of any of the other columns. For instance, if the table contained:

NameSeqNoIDCol4 ... ColX
RPL2975...
TLM0444...
AAB2801...
AZZ0801...
ABA3444...
KTT4975...
ABA1801...
YHG0975...
DEF1444...

I'd like to generate:

NameSeqNoIDCol4 ... ColX
AZZ0801...
ABA1801...
AAB2801...
TLM0444...
DEF1444...
ABA3444...
YHG0975...
RPL2975...
KTT4975...

I got my desired results by brute-forcing via four sub-queries:

Sub-query 1 - Generate the sorted Names with SeqNo = 0
Sub-query 2 - Expand above with the additional columns,
maintaining original order
Sub-query 3 - For each of the records from sub-query 2,
generate the 'dependents' having the same ID
and SeqNo != 0
Sub-query 4 - Expand above with the additional columns,
maintaining original order of sub-query 1
Main query - Create UNION of 2 and 4, sorting by original
order and SeqNo

if there were not a simpler approach - after all, this must be a fairly common issue when generating BOMs.

View 4 Replies View Related

SQL & PL/SQL :: Retrieving Column Names And Storing Them

Apr 2, 2010

I'm writing a procedure that takes a table name as a parameter and I would like to print out the column name with the supporting row entry for each row. I know the logic I'd like to use, but how do you query the metadata to return the column names and store them.

View 3 Replies View Related

SQL & PL/SQL :: Clause Retrieving Information From Varray

Apr 12, 2012

select *
from
table(it_count_tc('bbs_swe_tve_db'))

gives this output:

COLUMN_VALUE
------------
0
2
37
8
0
1
0
0
1
3
0
0

12 rows selected

BUT: what I what is like this

Jan Feb Mars April May ...
0 2 37 8 0 ...

it_count_tc is a function returning a varray of 12 integers input is a varchar2 string

View 2 Replies View Related

PL/SQL :: Retrieving Columns With Non-english Characters?

Nov 26, 2012

I'm running a query using SQL Developer on a table which contains several companies names from many different countries, and one of the checks I need to make to ensure data consistency is to search for all rows which the name of company contains special or non-english characters (like ç, ã, ä as example).

I don't know what can I use to do this. I tried to collate using NLS_SORT but it didn't work.

Is there someway to select only the rows that contain these special or non-english characters, excluding from the results the rows that only have english characters? We have many languages in this table.

The field I would like to make the conditions on is VARCHAR2.

View 21 Replies View Related

SQL & PL/SQL :: Retrieving Records Where All The Rows Satisfies The Condition

Apr 12, 2010

I have to write a query which will get records only if all the rows satisfies the condition.

Output should retrieve only 'A' because all the values are '1' Where as for 'B' only two rows having value '1'.

create table table1(field1 varchar2(100), field2 varchar2(10));

insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('A','1');
insert into table1(field1,field2)values('B','2');
insert into table1(field1,field2)values('B','2');
insert into table1(field1,field2)values('B','1');
insert into table1(field1,field2)values('B','1');
SELECT field1
FROM table1
WHERE field2=all(select '1' from dual)
FIELD1

A
A
A
B
B

View 2 Replies View Related

Forms :: Retrieving Images From File System

Sep 15, 2010

I am trying to retrieve images from the file system, but the open dialog box is not popping up for me to choose which file i want. It does nothing gives no error. Just says record 1/1 on the message bar at runtime.

I have two blocks on the module. One is the lobs_table block and the other is the control block. On the control block i have a when-button-pressed trigger on an item. And here is the code below.

Declare
jpg_image_dir VARCHAR2(100) := 'C:UsersajayiaDesktopMe_pics';
photo_filename VARCHAR2(100);
BEGIN
:System.Message_Level := '25';
[code]......

I don't know the code to use to get forms to bring up the open dialog box.

View 1 Replies View Related

Application Express :: Retrieving Column Of Datatype RAW(16)

Oct 26, 2012

I have a table defined where some columns are of the datatype RAW(16). In fact these are foreign keys to other tables. Our company has a policy that all primary keys are of the datatype RAW(16). Not much I can do about that.

Now I want to retrieve all columns of a certain row in that table and just visualize the foreign keys in hex format. All values are filled out nicely, except the columns with datatype RAW(16). Is there something special I have to do to retreive and display these? I defined an item as text-field for these, but this doesn't seem to be working...

I'm using APEX 4.1.1.00.23 at this moment...

View 4 Replies View Related

PL/SQL :: Retrieving Not Full Result / Regexp Substr

Nov 5, 2012

when I try to run this code

SQL> select

2 regexp_substr('But , soft! What light through yonder window breaks?',
3 'l[[:alpha:]] {4}') AS result
4 from dual;

I get just this ( R
-)

where the result should be like the full sub string which is ( light) not just R.

View 2 Replies View Related

Retrieving Info From Database And List Out In HTML Table?

Feb 21, 2011

I am having a little trouble retrieving info from my mock database.

here are my definitions:
response.setHeader("pragma", "no-cache");
response.setHeader("Cache-control", "no-cache, no-store, must-revalidate");
response.setHeader("Expires", "01 Apr 1995 01:10:10 GMT");
response.addHeader("Cache-Control", "post-check=0, pre-check=0");

[code]...

and here is my select statement.
ds = (DataSource) context.lookup(dSource);
conn = ds.getConnection();
stmt = conn.prepareStatement("select add_date, campus, name, leader from TABLENAME where id = ? order by xbbo_add_date");
stmt.setString(1, colleagueID);
result = stmt.executeQuery();

***basically I am trying to retrieve this info from my db, and list it out in a HTML table. I am writing this on a JSP page. Also, where i want the retrieved data listed, I am using for ex: <%=name%>

View 5 Replies View Related

Forms :: Inserting / Retrieving Images In Oracle Database 10g

Mar 12, 2010

how can i insert a image by selecting an image from any drives of my system to oracle database and retriving that image for displaying purpose after inserting that image. if possible give me a sample form. like vb 6.0 or vb.net in textbox's change trigger fires instantly when we start typeing in the text boxes.

View 6 Replies View Related

Forms :: Retrieving Previous Record Value Of Same Item In A Block

Jul 17, 2012

I am creating a record with the items - S_No, Product_Name, Selling_Qty, Price.

Checking the total qty (Tot_Qty) from the purchase stock - table1 and already sold out qty (Sold_Qty) of the product from table2.

Consider the following values: Tot_Qty = 200; Sold_Qty = 50;

Now i create a first record of the form as follows: here on selecting the Product_name from LOV, will display the Selling_Qty item - which can be edited (Selling_Qty = Tot_Qty - Sold_Qty) and its price.

S_No Product_Name Selling_Qty Price
1. KeyBoard 50(150) 200
2. KeyBoard 30(150) 200
3. KeyBoard 50(150) 200

Note:
[value given within () are displayed automatically after selecting the prod]

My prob is, when i am entering next record, it displays the same 150 as qty, which have to be 100. After selecting the product from LOV, it should calculate & validate as shown below: which means, previous record value of Selling_Qty also have to be subtracted and displayed.

S_No Product_Name Selling_Qty Price
1. KeyBoard 50(150) 200
2. KeyBoard 30(100) 200
3. KeyBoard 50(70) 200

Is there any built-in functions to retrieve previous record value?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved