SQL & PL/SQL :: Find Given Month Already Exists In Database
Apr 3, 2011
if i have data like this
case 1
code start month end month
winter march march
summer april january
spring feburary feburary
case 2
winter January January
summer february september
spring october december
and if i am trying to enter again start month and end month . i should validate if the month i am giving is already present in the table for any of the season
i want a do the validate with a query so that i will not enter duplicates.
View 5 Replies
ADVERTISEMENT
Sep 25, 2012
I am facing a problem to find the last day of the month which is stored in a variable. i tried the following query which gave me an appropriate answer
select to_char(last_day(sysdate),'DD-MON-YY')from dual
ans : 30-SEP-2012
But I am having only the month in my hand through which I want to find its last day. I cant use sysdate for my query.
View 2 Replies
View Related
Feb 13, 2013
How to find out first thursday for each month in year.
View 7 Replies
View Related
Sep 17, 2013
find the last nth month name [Single month].
View 5 Replies
View Related
Aug 23, 2010
SELECT HISTORY_ID ,SUM(MISSED_SCHOOL) AS MISSED_SCHOOL,SUM(MISSED_SCHOOL_LAST) AS MISSED_SCHOOL_LAST
FROM EMRASTHAMAHISTORYDETAILS
WHERE ------
GROUP BY HISTORY_ID
There is no date column in table using sysdate alone need to retrieve last 6 month records
how to use in where condition
View 13 Replies
View Related
Jul 8, 2010
i want to find out difference between two dates in day-month-year. How can i do it?
For example how many days,months are years are between '01-jul-1979' and '08-jul-2010'
View 4 Replies
View Related
Oct 1, 2012
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.
View 10 Replies
View Related
Oct 7, 2012
I tried to find out recently updated table list. I couldn't find anything.
CODE>> JUST SAMPLE TRANSACTIONS >>
SQL> conn sam
Enter password:
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$y1TXhcjGApvgQAB/AQBOpA==$0 TABLE
TAB1 TABLE
[code]........
View 1 Replies
View Related
Sep 26, 2012
I need query to find the missing dates between two columns in given month.
CREATE TABLE emp_shift (
empno NUMBER(4),
fr_date DATE,
TO_DATE DATE,
shift VARCHAR2(1));
CREATE TABLE emp (
empno NUMBER(4)
);
[code].....
Required output is
MISSING_DATES EMPNO
---------------------- ----------
09-SEP-12 TO 11-SEP-12 7499
23-SEP-12 TO 26-SEP-12 7499
01-sep-12 TO 30-SEP-12 7521
01-sep-12 TO 30-SEP-12 7788
View 8 Replies
View Related
Mar 21, 2013
I want to find the row with invalid day, month which are not matching with calendar day and month. Also the program should capture the data if the year <1900
with xx as
(select 101 as ID, '24/05/1899' as create_date from dual
union all
select 101, '32/03/2012' from dual
union all
select 102 ,'30/02/2012' from dual
union all
select 101 , '29/02/2013' from dual
[code]...
View 16 Replies
View Related
Nov 18, 2011
Need to find out what the maximum number of concurrent logins are over a period of 1 month. We need to do this as we may need to buy more licences.
View 4 Replies
View Related
Jun 4, 2013
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.
1. Table creation:
Create table T1 (account_no varchar2(15), area_code varchar2(2), bill_month date, consumption number);
2. List table content:
select * from T1;
account_no area_code bill_month consumption
Q00001Q31-Jan-12125
Q00002Q31-Jan-1265
Q00003Q28-Feb-12219
Q00004Q28-Feb-12805
Q00005Q28-Feb-1254
Q00001Q31-Mar-12234
Q00002Q31-Mar-12454
Q00003Q31-Mar-12232
Q00004Q30-Apr-1221
Q00005Q30-Apr-12218
Q00001Q30-Apr-1254
Q00002Q31-May-1219
Q00003Q31-May-1287
Q00004Q30-Jun-12187
Q00005Q30-Jun-1278
so on......so on......so on......so on......
3. Expected output:
account_no area_code Jan-12 Feb-12 Mar-12 Apr-12 May-12Jun-12Jul-12Aug-12Sep-12Oct-12Nov-12Dec-12
Q00001 Q 125 548 2345487423154821518738721512
Q00002 Q 65 127 45487819357831585683152878
Q00003 Q 545 219 2328738735188745897313
Q00004 Q 78 805 1221218187885718387389787138
Q00005 Q 541 54 2621878778386538698182
With the conventional query I hope this is impossible,
View 2 Replies
View Related
Feb 24, 2013
how to find out growth of database month wise?
according to me, v$datafile can be use to know growth in database in terms of datafiles added in past months as per creation_time. but hows about those existing datafiles whose size has been re size more .
Whats your inputs on this ??
View 21 Replies
View Related
May 16, 2013
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).
View 5 Replies
View Related
Nov 25, 2010
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);
Result
ID_CLIENTDT_REGVAL
101/01/2010200
101/02/2010300
101/03/2010500
101/04/2010650
101/05/2010750
201/01/2010100
201/03/2010320
201/05/2010820
301/02/2010150
301/05/2010250
View 17 Replies
View Related
Jul 1, 2013
How to find in the oracle10g database if there is any column which is not being used at all.If the data is there in the column,how to check when was it last populated.
View 3 Replies
View Related
Jan 15, 2013
I am trying to find out database name for the unusable indexes thru the query; I am using dba_indexes for the index name but not sure which view I need to join with to find the db name.
View 11 Replies
View Related
Apr 30, 2013
We have a 2 node RAC runing on 11.2.0.2 and last night the database was totally un-responsive. when i checked the ADDM I noticed the following:
Waiting for event "cursor: pin S wait on X" in wait class "Concurrency" accounted for 98% of the database time spent in processing the SQL statement with SQL_ID "4b2epo0eaqol9".I am wondering what option do i have here? i am looking to do the following:
1) find the root cause why the database was un-responsive
2) ADDM is listing the query, what options do we have further?
View 2 Replies
View Related
Feb 6, 2007
How do you find the youngest user in a certain table
select a.illness_desc
from uma.pc_illness a, uma.pc_patient b, uma.pc_diagnosis c
where b.patient_no = c.patient_no and c.illness_code = a.illness_code
and b.age = '21';
i currently have this (it also needs to display their illness which i have done) but i can do only if i cheat and see what the youngest user is, so instead of b.age = 21 i need something that will search the database and find that youngest age
we are using SQL+
View 5 Replies
View Related
May 19, 2010
I need the column name and table name which is having the particular value in the whole database.
E.g. :We have 'Scott' value in emp table emp_name column. and we have lot of tables. here we need emp_name and emp as emp table is having scott value in emP_name column.
View 14 Replies
View Related
Jun 8, 2010
I need to find all the tables present in DB or Schema.
View 3 Replies
View Related
Jun 20, 2011
There is one DBMSJOB i created schedule to run every day at 22:00 hrs. The jobname is IT_TO_DUM_LOADING and the job type is Stored procedure.
I queried all_source and found stored procedure does not exist in database.
SQL> select text from all_source
2 where type = 'PROCEDURE'
3 and name ='IT_TO_DUM_LOADING';
no rows selected
But i could see the job details in dba_scheduler_jobs view. I cannot use WHERE clause in dba_scheduler_jobs to restrict and find only the job row as i receive time zone error. How it is possible to view the job in dba_scheduler job view whereas i could not find the stored procedure of its in all_source?
View 7 Replies
View Related
Aug 29, 2012
I have a question i wanted to know that " Is it possible to write the Following Query Using NOT EXISTS
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT UNIQUE DEPTNO FROM EMP);
And one more doubt is there, can we use join to get same result.
View 9 Replies
View Related
Sep 22, 2011
query to find the current user accessing the database
View 5 Replies
View Related
Feb 16, 2010
As a part of requirement need to clean up some of database objects from database. Before cleaning up need to check for dependencies with in database and also on remote database.
Is there any data dictionary in oracle which proivides information about remote dependencies.
Any other way to get a list of remote dependencies other then manual checking.
View 3 Replies
View Related
Jul 26, 2011
I am having database A and database B and I am inserting into database b by selecting some records from database a using db link.Is there any way to find whether database link is being used?
View 6 Replies
View Related
Jul 10, 2010
How can we find the size of a view or synonym or table in a database. What is the code.
View 4 Replies
View Related
Jul 15, 2011
I am trying to patch an Oracle 11g database. I can see from the logs that is was patched after it was created by a consultant but I cannot get Opatch to work.
C:\app\Oracle\product\11.2.0\dbhome_1>java -version
java version "1.5.0_17"
Java™ 2 Runtime Environment, Standard Edition (build 1.5.0_17-b03)
Java HotSpot™ Client VM (build 1.5.0_17-b03, mixed mode)
C:\app\Oracle\product\11.2.0\dbhome_1>opatch>set ORACLE_HOME=C:\app\Oracle\product\11.2.0\dbhome_1
C:\app\Oracle\product\11.2.0\dbhome_1>opatch lsinventory -all
ORACLE_HOME is NOT set at OPatch invocation
The Oracle Home C:\app\Oracle\product is not OUI based home. Please give proper Oracle Home.
OPatch returns with error code = 1
OPatch failed with error code = 1
Java is set in the environment path but the Oracle home seems to be the issue. I checked the LOC parameter in the Oracle/Inventory folder and it is set correctly to C:\app\Oracle\product\11.2.0\dbhome_1.
View 3 Replies
View Related
Nov 8, 2011
Is there a way to find out the user access the database?
View 1 Replies
View Related
Aug 5, 2013
find the Execution time frequency of the database jobs?i need the Execution time like this..
Job A is Running on every monday 10:30 AM OR
Job A is Running on every MONTH OR
Job A is Running on every DAY 10:30 AM
View 4 Replies
View Related
