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*
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
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.
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.
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) ?
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.
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.
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.
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.
I have an PL/SQL query which gives the data between the date interval submitted by the user.The problem is that i want all the date irrespective of it has data or not for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012
Now, in the database the available dates are:
1-Jan-2012 2-Jan-2012 5-Jan-2012
So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.
I have an athletics participation table that only has the relevant emplid and effective date field. There is no term field on the table. I'm trying to only select those emplid's where the max( effdt) for the emplid is between the begin and end date of the current term. I only want to select current athletes. I would much rather it be for the current academic year but it seems impossible. Why can't I use max(effdt) here?
sql Code
AND h.emplid IN(SELECT b.emplid FROM PS_ATHL_PART_STAT b where max(b.effdt) between (SELECT term_begin_date AND term_end_date from PS_TERM_TBL
I want to find the dates which have a date plus with in 2 days after this date. I mean group by 3 days each even the date i missing between two days. Actualy I want to find the start date where the employ was missing on job.
Basic concept is employes have allowed to use 10 personal leaves of a year. Each leave can be use for maximum 3 days.
If employ did not come on the job for one day or two days or three days, it shoul be count as ONE personal leave. And If employ is missing at job for four or five days, it should be count as 2 personal leaves.
After finding these days I want to select the starting date of 5th personal leave. (which is 16.01.10).
I am not a expert of using SQL, but I think it could be possible with using partitioning a table on the givin reslult and further partition the reslut on rownum() as rn and the using case statement where rn = 5.
Split a date into new dates according to black out dates!
Here is my tables:
CREATE TABLE travel ( start_date, end_date ) AS SELECT DATE '0000-01-01', DATE '9999-12-31' FROM DUAL;
[code]....
I have lets say a "travel date" and black out dates. I will split the travel date into pieces according to the black out dates.
Note: Travel Date can be between 0000-01-01 - 9999 12 31
Sample:
Travel Date:
Travel | START DATE | END DATE T | 2011 01 04 | 2011 12 11
Black Out Dates:
BO | START DATE | END DATE A | 2010 11 01 | 2011 02 11 B | 2011 01 20 | 2011 02 15 C | 2011 03 13 | 2011 04 10 D | 2011 03 20 | 2011 06 29
Excepted Result:
New Travel | START DATE | END DATE X1 | 2011 02 16 | 2011 03 12 X2 | 2011 06 30 | 2011 12 11
Visually:
Travel Date : -----[--------------------------]--
A : --[------]------------------------- B : ------[---]------------------------ C : --------------[---]---------------- D : ----------------[------]-----------
Travel Date : -[--------------------------------]--
BO Date A : ----[------]------------------------- BO Date B : -------------------------[---]------- BO Date C : ----------------[---]---------------- BO Date D : ------------------[------]-----------
Result X1 : -[-]------------------------------- Result X2 : -----------[--]-------------------- Result X3 : -----------------------------[--]--
How can I select all of the dates between two dates? For example, given the start date 12/2/2003 and the end date 12/5/2003 I want to return:
12/2/2003 12/3/2003 12/4/2003 12/5/2003
Is there a built in function for this? Is there a way for a function to return multiple rows? It has to be a function because I need to use it within other SQL statements.
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.
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.
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.
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
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 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.
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:
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.
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.
