i have an application for a medical laboratory, i insert patients every day , what i want is toget the number of patients for the past month ?2- for the past week ?3- the past day , and so on.
I have an requirement to load past and future data from different source table to one tgt table.Say now we are in July
Past Data Apr May Jun
Future Data Aug Sep Oct
I HAVE actual sales for past month and present month which is in Table A & expected sales in table B.for every month i have to load 7 months data TARGRT TABLE..
In future the requirement may change to 6 months also. in that case the procedure has to load past 6 month + current month + 6month future so totally 13 month should be loaded.
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 trying to get the month number from this date format '19-OCT-12' . when i try to run the below statement i am getting an error like "not a valid month"
We have a requirement where we need to pay allowance for the employees based on their number of working days. Say for example if an employee worked from 03/Mar/2012 to 05/Apr/2012.
We have a fixed value for per month 300 Dirhams. But the Number of Days on March s 31 and Number of days in April is 30. So per day allowance for March day would be 300/31 and April would be 300/30.
We are looking for logic opr query which calculates first eh number of days in each month ( across months) and then calculate as below
Number of Working days in March is 31 - 3 + 1 = 29
Allowance A1 = (300 * 29 )/31
Number of Working days in April is 5 ( this also needs to find logical I am guess ) Allowance A2 = (300 * 5 )/30
Then A1 + A2.
The A(n) would be the total allowance where provided the number of month across.
I have a requirement to list the data month wise dynamically where month data is also in the same table, hopefully the below posts should bring more clarity to my requirements.
I am trying following way but giving me same date 11 june
SELECT dt from ( select CASE when to_char(To_Date(To_Char('20100613'),'YYYYMMDD'),'fmDy') in ('Sat','Sun') then next_day(To_Date(To_Char('20100613'),'YYYYMMDD')-7,'Friday') else To_Date(To_Char('20100613'),'YYYYMMDD')end as dt
I am working in development environment which has only 150 gb db size but suddenly it is raised to 250gb within 10 days i want to see which user did what in my database.How we can see what task made what in past 10 days.
Using 11gR2, windows 7 client machine. I need to update the table missing_volume (below), where I need to calculate the estimated_missing column. The calculation of estimated_missing column for current month needs previous month numbers (as commented inside the code below). I want the output like the first table. Notice the records start from January, hence estimated_missing for January can't be calculated, but for the the rest of the months it can be done by simply changing 'yr' and 'mnth' (commented inside the code towards the end).
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 24 2013 March loc1 55007 78 57 28 2013 April loc1 54345 72 58 77The code:
UPDATE missing_volume g
[Code]....
The code does calculate correct number for 'estimated_missing' as I run the code for each month, but the problem is while updating the current month it also erases the record for previous month. E.g. as can be seen below, after I updated April the column only has the record for April, previous month record is gone, similarly updating March removed February, etc. I can't understand why it's happening!! Here is the output I get:
yr mnth location volume actual_missing expected_missing estimated_missing --------------------------------------------------------------------------------------------------------------------------------- 2013 January loc1 48037 24 57 2013 February loc1 47960 3660 53 2013 March loc1 55007 78 57 2013 April loc1 54345 72 58 77
why it's happening (I mean where is the flaw in the code) and how to get the desired output (first table).
I have two table APPLICATION_1 and APPLICATION_2, where user's login date is captured.There will be multiple records for each user.Same user may or may not be present in both tables.
The requirement is to find those users who have not logged-in in the last 3 years.
-- Test Case
DROP TABLE application_1; DROP TABLE application_2; CREATE TABLE application_1 ( seq NUMBER , user_id VARCHAR2(30),
[code].....
Using the below query I'm able to get the desired output.
SELECT user_id, MAX(login_date) last_login_date FROM ( SELECT user_id, MAX(login_date) login_date FROM application_1 GROUP BY user_id UNION ALL SELECT user_id, MAX(login_date) login_date FROM application_2 GROUP BY user_id ) GROUP BY user_id
How can I find out the particular oracle session which was consuming high memory in the past?
I can't get the data in v$sessstat Unable to get the information in AWR
dba_hist_active_session_history do not have field which indicate memory related information Shall I concetrate on EVENT in dba_hist_active_session_history which continuosly had sort, direct path read Or Locate sql_id from dba_hist_sqlstat with high SORTS_DELTA for snapshots belonging to problematic time period and then using the sql_id query dba_hist_active_session_history
which approach I shall take to find out the session which consumed most memory in the past?
resolve issue while modified the user function code for returns the values as timestamps
---function code
create or replace function fun_test_timestamp(P_HOUR varchar2) return varchar2 is sql_stmt varchar2(1000); begin
[Code].....
Input:- select fun_test_timestamp('5') from dual;
Output:-
SELECT CURRENT_TIMESTAMP - INTERVAL '5' HOUR FROM DUAL;
Modified Fun Code:
create or replace function fun_test_timestamp(P_HOUR varchar2) return timestamp is sql_stmt varchar2(1000);
[Code]...
Input:- select fun_test_timestamp('5') from dual;
Output:-
ORA-00911: invalid character ORA-06512: at "NETVERTEXTRUNK.FUN_TEST_TIMESTAMP", line 8 00911. 00000 - "invalid character"
*Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.
I need for each date sum the values from the begin of the year to present date. In January I will have the value of this month, on February I must sum the value of this month and the value of the month before, and so on, at the end of the year.
Date input
SELECT ID_CLIENT, DT_REG, VAL FROM ( SELECT 1 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 200 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100401', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 1 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100101', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100301', 'YYYYMMDD') as DT_REG, 220 as VAL FROM DUAL UNION SELECT 2 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 500 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100201', 'YYYYMMDD') as DT_REG, 150 as VAL FROM DUAL UNION SELECT 3 as ID_CLIENT, TO_DATE('20100501', 'YYYYMMDD') as DT_REG, 100 as VAL FROM DUAL);
I want to ask if I restore a control file from backup of say day2 and I recover till day1 using RMAN script, then what will be the state of my database? I mean day2 control file may be having SCN-Day2, and I am instructing RMAN to recover till past point in time that is SCN-Day1.
A) extract subscriber numbers from TCME153.SUB then B) bounce those subscriber numbers off of a disconnect date i.e. DISCO_DTE_SBB then C) bounce A) and B) off of an item table i.e. ITV_ITEM_VALUE
My hope is that the first query works as intended. Second and third queries throw error 'ORA-01847: DAY OF MONTH MUST BE BETWEEN 1 AND LAST DAY OF MONTH'.
1) SELECT DISTINCT SUB_ACCT_NO_ITV FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB WHERE EXISTS (SELECT SBB_BASE.DISCO_DTE_SBB FROM (SELECT SUB FROM TCME153.SUB));
2) SELECT DISTINCT SUB_ACCT_NO_ITV FROM OPS$MDC.ITV_ITEM_VALUE, OPS$MDC.SBB_BASE, TCME153.SUB
I am trying to query on the dates in the table that are the last day of each month. The last day of the month may not be in the table. Example 07/30/2010 is the last day in the table for July 2010, but 07/31/2010 is the last day of the month. My query should give me 07/30/2010 data since it is the last day of the month in the table.
I just need a where clause that will only give me the data for the last day of each in the table.
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
I have a text field and if the text field has 5 consecutive numbers then I have to extract the number and the previous character from where the 5digit number starting
For example i/p asdfasfsdS251432dasdasd o/p should be S251432
I have table :TABLE_X and want to select some data locate into specific range of Day/Month. But so far i couldn't find out the way to.
For example, i want to select people born within specific range of date(range : sysdate to (sysdate+7months ahead) Year here should not be consider, only the day and month.
e.g. a range could be from today:Sept,20 to Apr,18.
so what i was trying is to select doing the following.
select TABLE_X_ID, TABLE_X_BIRTH_DATE from TABLE_X where to_date(TABLE_X_BIRTH_DATE, 'DD/MM') between to_date (to_char(SYSDATE, 'DD/MM'), 'DD/MM') and to_date (to_char(SYSDATE+210, 'DD/MM'), 'DD/MM')
first am not sure if BETWEEN & AND will work for this case, bt it was the most logical way i could think about to get such range.