I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
I want update col1 whis is null to max(col1) ++ in a row, order by cr_date like 1,1,20110102 2,2,20110101 3,null,20110105 => 3,5,20110105 because this row is after 20110103 4,3,20110104 5,null,20110103 => 5,4,20110103 because this row is before 20110105
update test_table set col1 = (select max(col1) from test_table) + rownum where col1 is null;
I need a query to whome i will pass month in number and that should return name of month. If i input 1 it should return Jan,2,Feb but don't use decode etc.
Is there any query with Julian format or any other date function?
I am oracle clinical 4.6. In Oracle clinical when i am trying to submit "Print DCF" it should run and generate the report in PDF format. But I am getting the following error message.
MSG-00010: 328900: SRW.RUN_REPORT failed. Updating failure text. REP-1825: Before Report trigger returned FALSE.
I want to show or hide some items on my form by using the SET_ITEM_PROPERTY to enable or disable VISIBLE. i have a button NEW, my need is when i click on this NEW button it should be hide and some other items should be show. but when i click on this its only showing other items but not hiding the NEW button. my NEW button is like this:
oracle version 11gr2.In the below sample data column a,b if there is Two (2) consecutive numbers are transposed in column B from A it should return string 'true'.
with t as ( select '123456789' a ,'123476581' b from dual union all select '123456789' ,'123465789' from dual union all select '332211' ,'332121' from dual union all [code]....
For Just learning purpose This is an example found in text book but while i try to execute it fails..I am trying to set Boolean flag to true if the hire_date is greater than 5 years otherwise boolean flag to false
DELARE v_Hire_date date :='12-Dec-2005'; v_five_years BOOLEAN; BEGIN IF [code].....
i have a block of multiple records where the user can edit the shipment totals of products being sent to branches.
some branches can't receive certain products and usually we just default the shipment value to zero but the user can still edit that value.
What i want to do is iterate through that block and check the branch and product and set the update property to false for only that record. is that possible.
i have a form having 4 database data blocks and one non database data block(for Push Buttons like SAVE,CLEAR,EXIT..).
In the control Block i have 5 push buttons out of which one is used to Save the entries and one to clear the form. In the coding of SAVE button after writing "COMMIT_FORM;" i want to disable all other Data block on my form excluding the one containing Push buttons so that the data entered by end user stay appearing on the form till he Clears the form.
Now the problem is that when i set enabled property of one particular text_item to false , the form if closing down along with the application, it is totally disappearing from screen.This is happening for one particular text item.
what could be the possible reason for this and what needs to be done to overcome this problem.
For one of my OLTP table, we have skewed distribution on STATUS column and hence we created frequency histograms. NDV=7 for this column.
My question is : Do we need to have "_optim_peek_user_binds" set to TRUE in order to utilize Adaptive Cursor Sharing ?I have gone through different OTN forum and blogs and it appears that in order to generate different plans, Oracle has to Peek the value of bind variables each time. Without Peeking, how would CBO knows whether to generate new plans or not ?
For xyz reasons, optimpeek_user_binds is set to FALSE in my database and i could not find any sql_id having V$SQL.IS_BIND_AWARE='Y'.
In the example the client 123 appears from 2010/10/04 to 2010/10//08 (5 consecutive days), so this client must appear in the output. In the example customer 456 does not appear at least 4 consecutive days, so should not appear in the output.
I want to do group ranking in desired col3 in such a way that it checks for different values across consecutive rows under col2 and assigns a number to each group. Just when two consecutive rows in col2 have same value then the group ends and the next group starts.
So my desired output is:
Col1 Col2 Col3 1 A 1 2 B 1 3 C 1 4 D 1 5 D 2 6 A 2
[code]...
Here you can see that the first four rows under col2 are unique i.e A,B,C,D so col3 assigns this as group number 1. It ends at row 4 becuase row 5 also has value D under column 2. So in other words, each group must have all unique values and there should not be any repetition. For example, see group 3 (under col3) in above desired output; it starts from row 9 and ends at row 11 because row 12 also has value 'C' and the value 'C' has already occurred in group 3 in row 9.
I want to achieve this SQL. I tried using Dense rank but couldn't go through. I want the shortest possible query to acheive this.
im working with apex 4.2.1 and when i try to define a Dynamic action on any item, if the action type is "Set Value" or execute SQL/Javascript code it should appear a text field that let me define the action taking place. However this wont do..
this is what happens:
And this is what should happen:
I know that patch 4.2.2 solves some issues regarding dynamic actions but in the bug report there's no reference to such a problem.
i want to restrict the user if he/she enters any 3 consecutive sequence of numbers,characters,alphanumerics and special characters for example aaa, aAa, @@@, ---- , 111, 123 are not valid.
valid sequences are a1w,?1A,aa1,WW2,78a,-#a
i want to show the invalid sequence in a single query using regular expression function. suppose for example if user enters aaa,$$$,123 then the query output is aaa,$$$,123.
i have written two different queries for that but i want a single query
SELECT REGEXP_SUBSTR('EEE','([a-z])\1\1',1,1,'i') FROM DUAL; SELECT REGEXP_SUBSTR('111','([0-9])\1\1',1,1,'i') FROM DUAL; SELECT REGEXP_SUBSTR('@@@','([^-$])\1\1',1,1,'i') FROM DUAL; -it is not checking for -(hypen) characters
$x being a range of non-consecutive values like so: 1,3,5-9,13,18,21 and so on...
I realize I can query using an array of operands and such, but these ranges will be in upwards of 100 or more items. I want to minimize the number of queries I have to do and the length of them. Is there any resource you can point me to that can optimize something like this?
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
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL' to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method WITH T
I have a problem with a query. I have a table employee with data as
emp_id date day working_ind 1 01-Jan-2011 Mon Y 1 02-Jan-2011 Tue Y 1 03-Jan-2011 Wed Y 1 04-Jan-2011 Thu Y 1 05-Jan-2011 Fri Y 1 06-Jan-2011 Sat N 1 07-Jan-2011 Sun N 1 09-Jan-2011 Tue Y
Sundays/ Monday/ any public holiday the working_ind will be N. If the emp is absent on one day then there will be no record entered in the table (e.g. 8th jan there is no record). Each table has only one year data.
I need to retrieve for all employees when they worked for 30 consecutive days without being absent which does not include sat/ Sunday / holidays.
Its like: -- i need to order by emp_id and date -- get oly the data with working_ind as Y -- make sure that i get 30 consecutive days (from what ever i get above) where no days data is missing
I tried using lag and inner join but it does not seem to be working.
how to query a list to see if a person had events in consectutive months within the past year. We call a person a LongTermResident if they had a review in any two consectutive months within a reporting period. I wrote a function isResidentLongTerm, passing in FacilityID, ResidentID, ReportPeriodStart, and ReportPeriodStop and returning a 'Y' or 'N'. It works, but the performance is slow.
So if I have a list of reviewers, facilities, reviewees I want to select only those SNF/NF residents who have had routine reviews in any two consectutive months at the same facility.
This is my query:
select ConsultantID, ResidentID from ( select distinct ConsultantID, FacilityID, ResidentID from Reviews where BedType = 17820 -- SNF/NF bed and ReviewType = 17474 -- routine review ) where isResidentLongTerm( FacilityID, ResidentID, :startDate, :stopDate ) = 'Y'
I want to find out consecutive non-increasing sequences of value (2nd column) order by sr. no (first column) in ascending order.
For example, in the 2nd column, 17 is followed by 0 and 0 and then 38 so it means 3 consecutive values (i.e starting from 17 are 0 and 0) are non-increasing and they are ranked by '1' in my desired in third column as shown below. similarly, the 2nd non-increasing sequence starts with 38,32,24 and 12 and this is ranked as '2' in the third column. same is the case with rank '3' for the third non increasing sequence. so bascially i want the third column with "ranks" starting and ending as per above logic. i tried using LEAD function but doesn't get what I want. I need the shortest possible query to get that third column since i have other columns in the original table in a multiple group by query.
create table T (student_id number, class_id number, quiz_id number, marks number)
some sample rows like
INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); INSERT INTO T VALUES (1,1, 4, 10); INSERT INTO T VALUES (1,1, 5, 30); INSERT INTO T VALUES (1,1, 6, 29); INSERT INTO T VALUES (3,2, 7, 34); INSERT INTO T VALUES (3,2, 8, 33); INSERT INTO T VALUES (3,2, 9, 56); INSERT INTO T VALUES (1,1, 7, 90); INSERT INTO T VALUES (2,2, 8, 0,); INSERT INTO T VALUES (1,1, 8, 80); INSERT INTO T VALUES (2,2, 8, 65); INSERT INTO T VALUES (1,1, 9, 34); INSERT INTO T VALUES (2,2, 9, 11);
each student belongs to one class_id. each student participates in many quizes. each quiz has its unique id. each student can appear once in a quiz_id
I am doing the below analysis and query:
1. with below query I am finding which student_id had most marks in any 3 successive quizes (see the 3-1 part below) in the query..
With above query, I can play around and find for any 'n' number of consecutive quizes, like marks in 2 consecutives quizes, 3, 4 and so on but for each 'n' value I've to run a seperate query mentioning (2-1) or (3-1) or (4-1) and so on..
since my table is big and there are about 400 quizes so what I want to find out is for each 'n' consecutive quiz (from 1 to 400) which student had most marks for each consecutie 'n' quiz. Like in 1 (consecutive) quiz which student had the highest marks and then 2 conseuctive quiz who had most marks and then in 3 consecutive quiz who had most marks and so on till 400 consecutive quiz who had most marks... rather than running query for each 'n' value seperately i want a single query that can give me a summary of most marks in each n consecutive quizes...
Is this possible to get the above output from one single query? If there are two or more students with equal most marks for any 'n' conseutive quizes then both should come in the summary.
The code should identify that the 'Bonus' project missed updates on 02/08/2013 and 06/08/2013. Should be a simple enough piece of code for an experienced developer i'm sure
