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
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.
SELECT DISTINCT a.emp_id, a.cal_id, TO_CHAR(a.ts_date, 'DD/MM/YYYY') tsdate, a.ts_date, 1 as days FROM tmsh_timesheet a INNER JOIN project b ON TO_CHAR(b.proj_id) = a.proj_id INNER JOIN tmsh_ts_calendar c ON c.cal_id = a.cal_id INNER JOIN (SELECT a.cal_id, a.emp_id, MAX(a.status) as status, a.create_dt, a.create_by FROM tmsh_stat_hist a
In a table I have a column update_date and its type is DATE. Sample values from this column are as follows. I am using the following query to select all update_date lie between sysdate and sysdate-90.
select update_date from table1 where update_date between sysdate and sysdate-90
The above query retrun no data even data is there in the table for this range.
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 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 : -----------------------------[--]--
Insert into test values ('2/03/2011') Insert into test values ('02/03/2011') Insert into test values ('12/33/2011') Insert into test values ('xxx') Insert into test values ('33/33/2011') Insert into test values ('03/03/11')
Table Name PURCHASE exists in all above three schema with different data(according to date)
Like User09 ----> purchase ---- data date Jan09 to Dec09 User10 ----> purchase ---- data date Jan10 to Dec10 User11 ----> purchase ---- data date Jan11 to Dec11
I want to select * from purchase, from above all three user with select statement.I can select data one by one with union all
select * from user09.pruchase Union all select * from user10.pruchase Union all select * from user11.pruchase
after creating this query I add another User User12 and that User has same purchase table so I will have to add one another line IN Union all Query. when I see this query. It gives me the all owner name of User09,User10,User11......
SELECT * FROM ALL_ALL_TABLES WHERE OWNER LIKE 'USER%' and table_name ='PURCHASE'
I have a table with multiple column. A column named address have value of multiple lines. Now i want to select the address only of the first line. How does it possible.
how to get the output in below format. Count how many times each file is selected in a month.
Output format should be like below.. ============================================== File_Name Jan Feb Mar Apr ---------- Dec ============================================== file1 2 1 3 0 ---------- 2 file2 1 0 2 1 ---------- 3 file-n 8 2 3 0 ---------- 2
The description field in the item table has the single quote used as the symbol for feet. I have the same issue pulling from a last name field in other tables. (Like O'Connor)
select descrip into v_result from c_ship_hist where shipment_dtl_id = :SDID; exception when others then null;
The error I get is "Missing right quote". How do I code around this issue without having to change the data?
how to display REFS from within a REF. I've not used my exact code here as its quite a big file so i've made a similar scenario to get me point across. Here is the code first:
1 CREATE OR REPLACE TYPE object1 AS OBJECT ( 2 object_id NUMBER(5), 3 object_name varchar2(10), 4 object_added DATE); 5 / 6 CREATE TABLE object1_tab OF object1 (object_id PRIMARY KEY); 7 / 8 INSERT INTO object1_tab 9 VALUES (1,'Daniel',sysdate); 10 / 11 show errors; 12 / 13 CREATE OR REPLACE TYPE object2 AS OBJECT ( 14 object_id NUMBER(5), 15 object_job varchar2(10), 16 object1_ref REF object1 ); 17 / 18 CREATE TABLE object2_tab OF object2(object_id PRIMARY KEY); 19 / 20 INSERT INTO object2_tab 21 VALUES (1,'Developer',(SELECT REF(p) FROM object1_tab P 22 WHERE VALUE(p).object_id = &object_id)); 23 / 24 select DEREF(object1_ref) 25 FROM object2_tab; 26 / 27 CREATE OR REPLACE TYPE object3 AS OBJECT ( 28 object_id NUMBER(5), 29 object_location VARCHAR2(20), 30 object2_ref REF object2); 31 / 32 CREATE TABLE object3_tab OF object3 (object_id PRIMARY KEY); 33 / 34 INSERT INTO object3_tab 35 VALUES (1,'New York',(SELECT REF(p) FROM object2_tab P 36 WHERE VALUE(p).object_id = &object_id)); 37 / 38 show errors; 39 / 40 select object_id,object_location,DEREF(object2_ref) 41 FROM object3_tab; 42 /
As yot can see in the code each object refernces from the previous. When I view the DEREF in the third table (object3_tab) i get the following output:
OBJECT_ID OBJECT_LOCATION DEREF(OBJECT2_REF) --------- -------------------- ---------------------------------- 1 New York [SANTA.OBJECT2]
I wanted to select data which is inserted on the same time.
Is that possible? I am trying with below query but facing isses.
SQL> SELECT a,b,c,d FROM tb;
no rows selected
SQL> SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1)); SELECT a,b,c,d FROM (INSERT INTO tb VALUES(1,1,1,1)) * ERROR at line 1: ORA-00903: invalid table name
I am attempting to use the following select to get a specific emplid. However, the ps_names table contains some alphabetic characters. I want to only focus on the emplid's that contains numbers. Is there a way to modify the following select to do this?
bubbagumpshrimp "ORA-01722: invalid number" SELECT x.y from (select PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY to_number(emplid)) over () y from PS_NAMES where emplid > '000000000' and emplid < '999999999') x where rownum = 1;
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows: Lvl KeyParKey hasCh 1k101 2k34k10 2k22k11 3k56k220 3k109k221 4k61 k1090 3k67k220 2k24k10 1k200 1k301 2k13k30 2k52k30 2k35k30 2k13k30 1k401 2k11k40
