SQL & PL/SQL :: Select Max Date Between Columns In A Row?
May 3, 2012
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
"CORE10.2.0.5.0Production"
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
See attached file for creation script and data load.Each staff member is required to complete at least one task every three years. The source table contains an EID (aka User ID) and a date column for each task with a date of when the task was completed. If a task has never been started/completed the date value is "NULL".
The result set will show the EID, date of latest task completed and if the task was completed within the last 3 years from given date (for example June 30, 2012).
I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
I have a table containing hundreds of columns and I would like to be able to qualify my select statements so that only those columns containing a value are returned. Something like:
Select (non null columns) from tablename where columnX = 'whatever'
Quote: select a.contact, b.db_name from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, where a.DB_ID=b.DB_ID and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1) FROM mgmt_targets t JOIN mgmt_current_severity s ON s.target_guid = t.target_guid WHERE t.target_type = 'oracle_database' and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote: select a.contact, b.db_name, COLUM1, COLUMN2 from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s where a.DB_ID=b.DB_ID and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1) FROM mgmt_targets t JOIN mgmt_current_severity s ON s.target_guid = t.target_guid WHERE t.target_type = 'oracle_database' and UPPER(t.target_name) like '%11GDB%');
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob from patients join schedule using (key_patien) join adtmirro using (key_patien) where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
I created a user and granted connect, resource to the user. but trying to select columns from the view USER_HISTORY$,it's showing no value, i tried to login the user several times, but it's displaying no value, any particular reason or any minor mistake?
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
Our DBA set up a Heterogeneous Service to one of our MS SQL Servers and we can use a DB Link to SELECT a table from the SQL Database. This works just fine but the problem is any nText column on the SQL Database Table doesn't appear. All the columns are shown EXCEPT the nText columns.
how to correct this? We need the ability to select the nText columns also.
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE v_mydept1 TEST1.DEPT_CD%TYPE; v_mydept2 TEST2.DEPT_CD%TYPE; CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR; [code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF" I am not sure what is the exact problem when we want to update multiple columns of different tables.
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order" FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id LEFT JOIN order_details ON orders.order_id=order_details.order_id LEFT JOIN items ON order_details.item_id=items.item_id ORDER BY Customer_name
As we know that date datatype can store both date part and time part. If I specify the Date format for my database as 'DD-MM-YYYY HH@$:MI:SS' can i ensure i anyways for a particular columns in the database containing date values the format is 'DD-MM-YYYY' i.e without the time part.Can we specify seperate date formats for specific columsn in database during table creation?
The below sql is giving different number of result sets while adding further columns in select clause.i.e After adding the columns 4,5,6 in the below query its giving different number of result set.In this case the result set count would be 5.
Before adding the columns 4,5,6,the result set count was 11.
select max(PERIOD_DUE_DATE) , form_submission_id from form_submission group by form_submission_id
but this returns all the records, I need only the max date along with its form_submission_id.In reality Its a complex query but to explain my problem I putting this simple query, how to select max(column) and column2 from table.
How do I select only last date for each contragentid? So for contragentid = 111270 it should be only '14.05.2010'.
select dd.contragentid, decode(dd.ratingvalue,'PK1',1,'PK2',2,'PK3',2,'PSR',2,'UN4',2,'VVL',2,'BK',4,3) as ratingvalue from (select 36 as contragentid, 'UN1' as ratingvalue, '25.02.2010' as ratingstartdate from dual union all select 111270 as contragentid, 'PK1' as ratingvalue, '26.11.2009' as ratingstartdate from dual union all select 111270 as contragentid, 'PK3' as ratingvalue, '14.05.2010' as ratingstartdate from dual union all select 111270 as contragentid, 'BK' as ratingvalue, '14.06.2011' as ratingstartdate from dual ) dd where dd.ratingstartdate <= to_date('31.05.2010', 'DD.MM.YYYY')
Also I need to select all rows from second test case for those contragentid which absent in first case, it should be one statement for both cases.
select * from (select 5 as contragentid, 2 as ratingvalue from dual union all select 111270 as contragentid, 1 as ratingvalue from dual ) hh
I have a sql query where I need to select only records with an 18 month gap between max(date) and previous date( no dates between max(date)and 18 month gap date), when I run the below query it should only select supid 130, not 120 (even though 120 does contain an 18 month gap date it also has a date that is less then the 18 month gap( '25-NOV-2012','DD-MON-YYYY'). how would get the query to look back 18 months for the next date and evaluate the month_between.
. example:
create table supply(supID number(8), supply varchar2(20), supdate Date,supamount number(13,2)); insert into supply values(100,'Tapes',to_date('01-AUG-2013','DD-MON-YYYY'),50.00); insert into supply values(100,'TV',to_date('01-APR-2013','DD-MON-YYYY'),250.00); insert into supply values(100,'Discs',to_date('25-DEC-2012','DD-MON-YYYY'),25.00); insert into supply values(120,'Tablets',to_date('25-AUG-2013','DD-MON-YYYY'),15.00);
[Code]....
and p.supid in(select s.supid from supply s where months_between
I have data such as 'hours', 'date' when and employee worked on the project. What I need is to select the total amount of hours per month of March, April, May, etc...
I know how to select data per single date but wonder how to do it per multiple dates. How does one select total amount of hours per multiple date ranges (March, April...)?
Is there a way I can find what the last date/time and index was used for a select...
I have a table with several indexes on them, which I beleive are not being accessed.
I use the following the query to find indexes that where not accessed in a while but this I believe is limited my my workload repository retention, which is set to 90 days.
select index_name from dba_indexes where table_name='<table name>' and index_name not in (select c1 from( select p.object_name c1, p.operation c2, p.options c3, count(1) c4 from dba_hist_sql_plan p, dba_hist_sqlstat s where p.object_owner = 'MTAS' and p.operation like '%INDEX%' and p.sql_id = s.sql_id group by p.object_name, p.operation, p.options order by 1,2,3))
Without increasing my repository retention is there a way I can get the last date/time, which an index was used instead of just saying it has not been used in 90 days (retention setting). Is this information kept in the SQL plan?
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
