i have a data something like
010030
110495
210960
311425
[code]...
and user enters the figure like 13025now i would like to find the closest next value to the number entered by user, which is 713285 .how can i get this result, i have search a lot on Internet and i found some analytic functions can work out. but don't know how ..
Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss
Mississ
Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;
I need to find a query to create a gap in continuous series. So that i can fill this gap with a new record.
Attached here is the test sql.
Sample Data is
test@orcl>select * from sales_mst;
NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
[code]...
10 rows selected.
test@orcl>select * from sales_dtl;
NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1249 12 55 450
[code]...
16 rows selected.
Required Out Put
test@orcl>select * from sales_mst;
NO VDATE T
---------- --------- -
1245 07-JAN-13 N
1246 07-JAN-13 N
1247 07-JAN-13 R
1248 07-JAN-13 N
1251 08-JAN-13 N
1250 08-JAN-13 R
1253 08-JAN-13 N
1252 09-JAN-13 R
1254 09-JAN-13 N
1255 09-JAN-13 N
10 rows selected.
test@orcl>select * from sales_dtl;
NO CODE QTY RATE
---------- ---------- ---------- ----------
1245 11 60 600
1246 12 55 450
1246 11 45 600
1247 13 50 250
1247 11 60 600
1248 11 45 600
1251 12 55 450
1251 11 45 600
[code]...
16 rows selected.
Update or move only those records which have Type 'N'. I have tried the following query but it is giving me an error.
test@orcl>ALTER TABLE
sales_dtl
DISABLE CONSTRAINT
fk_sales_dtl_no;
test@orcl>update sales_mst set no=no+1 where type='N' and vdate > '07-JAN-13';
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.PK_SALES_MST_NO) violated
I have a query that selects multiple series for a bar chart (I have defined only one chart series, and have 3 columns selected to represent 3 series). The series display on Bar Chart is fine. Now I want to create a separate link target depending on which series is clicked on the chart. I am not able to do this.
View 1 Replies View Relatedi am using oracle developer 6i report builder i required this type of query
example
if (:page number LIKE '1')
then
srw.set_text_color('darkred');
end if;
return (TRUE);
end;
but page number is not my table database item how can i use builtan page &<pagenumber> use for conditional format.
how do I count a list of number value eg 1,1,1,1,3,3,6,4 and find the one with maximum number which is 1
View 5 Replies View RelatedI am trying to develop an ongoing interview schedule.
interviews may be held on FRIDAY, SATURDAY, TUESDAY or any other day or Days in a every week selected by the user and also at some specific time.
we need to schedule the closest comming day based on Application date.
for example: today is WEDNESDAY and our schedule interview day is FRIDAY and SUNDAY. If one application is received today we need to schedule interview on FRIDAY because this is the nearest/closest day of WEDNESDAY. and if the application is received on Saturday then of course Sunday will be scheduled.
if Application is received on same day e.g. FRIDAY is scheduled and we receive Application on FRIDAY then comes the concept of time. interview is schedules on FRIDAY 11:30 AM.
if application is received on before 11:30AM, the same day will be scheduled. If application is received after 11:30 AM then of course next closest day will be scheduled.
I have a table named UFM with columns :
trans_ID,Plz_id,clas,trans_date
31106952318303- 005110-2- 6/24/2012 5:56:07.800000 AM
33706952318401- 005110- 2- 6/24/2012 5:56:23.873000 AM
34806952318304- 005110- 2- 6/24/2012 5:56:25.650000 AM
35406952318442- 005110- 2- 6/24/2012 5:59:20.260000 AM
[Code]...
I have another table named facility with columns:
plz_id, Eff_date
005110- 1/10/2012 10:00:00.000000 PM
005110- 1/11/2012 12:00:00.000000 AM
005110- 1/13/2012 12:00:00.000000 PM
005110- 6/24/2012 6:00:00.000000 AM
I want all and records from UFM joining to facility table, i need columns trans_ID,Plz_id,clas,trans_date,Eff_date in my output.
the output should look like ,
trans_ID,Plz_id,clas,trans_date,Eff_date
31106952318303- 005110- 2- 6/24/2012 5:56:07.800000 AM-1/13/2012 12:00:00.000000 PM
33706952318401- 005110- 2- 6/24/2012 5:56:23.873000 AM-1/13/2012 12:00:00.000000 PM
34806952318304- 005110- 2- 6/24/2012 5:56:25.650000 AM-1/13/2012 12:00:00.000000 PM
35406952318442- 005110- 2- 6/24/2012 5:59:20.260000 AM-1/13/2012 12:00:00.000000 PM
[Code]....
for a transaction that occured on 6/24/2012 after 6:00 AM the eff_date should be 6/24/2012 6:00:00.000000 AM and for a transaction before 6:00 AM on 6/24/2012 the eff_date should be 1/13/2012 12:00:00.000000 PM. and the transactions that occured before 1/13/2012 12:00:00.000000 PM the eff_date should be 1/11/2012 12:00:00.000000 AM and so on.
I have two tables e.g. test_bb and test_sub
I would like to select test_sub.price as per the below conditions. If test_bb.value_date, test_bb.vehicle matches to test_sub.value_date,test_sub.vehicle then display test_sub.price
If there is no match then as above find the closest test_sub.value_date which is less than test_bb.value_date and select corresponding test_sub.price for the same vehicle combination.
e.g.
select * from test_sub;
VEHICLE VALUE_DAT PRICE
---------- --------- ----------
10 12-APR-12 2
10 08-JAN-10 4
10 14-APR-14 6
10 06-AUG-47 8
20 24-JAN-14 10
20 20-FEB-06 12
20 18-FEB-04 14
20 28-FEB-06 16
20 22-DEC-07 19
8 rows selected.
select * from test_bb;
VEHICLE VALUE_DAT
---------- ---------
10 12-APR-12
10 10-FEB-10
20 24-JAN-14
20 22-FEB-08
Required output:
VEHICLE PRICE VALUE_DAT
---------- ---------- ---------
10 2 12-APR-12
10 4 08-JAN-10
20 10 24-JAN-14
20 19 22-DEC-07
create table test_bb(vehicle number, value_date date);
begin
insert into test_bb values(10,to_date('12-04-2012','dd-mm-yyyy'));
insert into test_bb values(10,to_date('10-02-2010','dd-mm-yyyy'));
insert into test_bb values(20,to_date('24-01-2014','dd-mm-yyyy'));
insert into test_bb values(20,to_date('22-02-2008','dd-mm-yyyy'));
end;
/
create table test_sub(vehicle number, value_date date,price number);
begin
insert into test_sub values(10,to_date('12-04-2012','dd-mm-yyyy'),2);
insert into test_sub values(10,to_date('08-01-2010','dd-mm-yyyy'),4);
insert into test_sub values(10,to_date('14-04-2014','dd-mm-yyyy'),6);
insert into test_sub values(10,to_date('06-08-1947','dd-mm-yyyy'),8);
insert into test_sub values(20,to_date('24-01-2014','dd-mm-yyyy'),10);
insert into test_sub values(20,to_date('20-02-2006','dd-mm-yyyy'),12);
insert into test_sub values(20,to_date('18-02-2004','dd-mm-yyyy'),14);
insert into test_sub values(20,to_date('28-02-2006','dd-mm-yyyy'),16);
insert into test_sub values(20,to_date('22-DEC-2007','dd-mm-yyyy'),19);
end;
/
I could write as below but I would like to know if there is a better way of doing it.
select bb.vehicle
,sub.price
,bb.value_date
from test_bb bb
,test_sub sub
where bb.vehicle=sub.vehicle
[code].........
I need to find the closest Date that matches a Particular Date. The Closest Date from the Group may be less than or greater than the Date I am trying to find.
I have two columns: VISIT_DATE and ACTUAL_DATE. The VISIT_DATE columns has many records with different dates while the ACTUAL_DATE column would only have one record per Student ID.
Here is an example of dates:
Visit Date Actual Date
==========================
01-APR-09 19-MAR-10
16-NOV-09 19-MAR-10
17-MAR-10 19-MAR-10
21-MAR-10 19-MAR-10
04-APR-11 19-MAR-10
15-JUN-11 19-MAR-10
19-SEP-11 19-MAR-10
24-FEB-12 19-MAR-10
The closest date to 19-MAR-10 are in fact 17-MAR-10 and 21-MAR-10. I would in that case need to pick up both records.
My requirement is to find the closest date from col B(a.p_date) to the dates in col A(d.p_date). I got the following which works great:
SELECT
d.p_no_s,
d.p_date,
MIN(a.p_date),
MIN(a.p_date)-d.p_date||' Number of Days' NUM_OF_DAYS
[Code]...
Output:
p_no_s p_date MIN(a.p_date) MIN(a.p_date)-d.p
-------------------- ---------- ---------- -------------------------------------------------------
Z1575560 15/06/2008 29/07/2008 44 Number of Days
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008 27/12/2008 36 Number of Days
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Problem:
For 1st and 2nd row,
I am getting 29/07/2008 as a.p_date for both 15/06/2008 and 15/07/2008 which is wrong in my scenario. This is because data is missing in the second table for row 1 (similarly for row 3). So What I want is :
Z1575560 15/06/2008
Z1575560 15/07/2008 29/07/2008 14 Number of Days
Z1575560 21/11/2008
Z1575560 17/12/2008 27/12/2008 10 Number of Days
Say for eg: a person is in city A. He is departing on (15/06/2008 ) and arriving on 29/07/2008 and again departing on 15/07/2008 which is not possible.
It should be departing on (15/06/2008 ) and arriving between 15/06/2008 and 15/07/2008 (missing data hence null is required here) and departing on 15/07/2008 and arriving on 29/07/2008 .
let me know how to achieve this.
is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?
View 4 Replies View RelatedI need to find out that assume i have a table having 2 column
Num Name
1 Adam
2 Akanksha
1 barren
2 bosli
3 Benergee
4 Bhawna
3 anjani
I want a query as if A is there 2 times then there should be 1 then 2 then there is b coming in 4 places then it should be 1 2 3 4 and again there is anjani so 3 should be there as 1 and 2 in first 2 places and the num should be automatically generated number based on the count of the alphabets
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
i want a simple query to find out for example the highest age in the table.
View 9 Replies View RelatedI have a table with two columns Column1 and column2
Like such
create table testTable (column1 number(15), column2 number(15));
insert into testTable values (1,5);I need to find the numbers between column 1 and column 2 including the column 1's number and column's 2 number.
so my answer set should be
1,2,3,4,5
I want to print Fibonacci Series like :-
0,1,1,2,3,5,8,13,21,34..........
How can i do it.Can i do it by procedure or function?
We are interested to find the number of connections from specific client. Is tracing on sqlnet.ora in the client machine the answer? If yes, which trace has the information?
View 1 Replies View RelatedI want to find out the oracle client version on a particular server. How can I do that? I used to know some files to look at, and part of the file name indicated the version #, but it's been too long and I don't remember which files they are.
View 2 Replies View RelatedWe have data as below in the table. I need the to display the records in the order based on number of NULL values and position for each record.
provide a simple query using case in ORDER BY clause.
ID CLASS NAME DIST_ID DIST_NAME
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL NULL NULL NULL NULL
0 C123 RAJESH 0 AP
NULL C123 RAVI NULL AP
We have to give the rank based on NULL values and NULL value column position.Let us assume column positions as
1 2 3 4 5
ID CLASS NAME DIST_ID DIST_NAME
for the following three records number of NULL values are same. but positions are different.
0 NULL NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
Based on the column positions the ranks as
2*2+3*3=13
1*1+3*3=10
2*2+3*3=13
1*1+4*4=17
Which is having high rank (greatest number) that record should come last . The record which is having all values that should come first. The record which is having all NULL values should come last. The out put I want as
ID CLASS NAME DIST_ID DIST_NAME
0 C123 RAJESH 0 AP
0 NULL KIRAN 0 AP
0 C1213 NULL 0 AP
NULL C1234 NULL 0 AP
0 NULL NULL 0 AP
0 NULL NULL 0 AP
NULL C123 RAVI NULL AP
NULL NULL NULL NULL NULL
I want to display a boilerplate item and their value only on the last page.
The item is under frame . So print object at last page doesn't work.
I could find the page number by the use of srw.get_page_num(n).
But not sure how to find the last page number.
How to find the line number along with package name or procedure name (for ex. in Oracle 10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ) where exactly error is coming in forms.There is plsql code written and while executing the code, ORA-01722: invalid number error is coming. For easy tracing the error, any way is there ?
I searched in Google before posting in this forum and found that ON-ERROR trigger if we write DBMS_ERROR_TEXT it will display the ling number along with package or procedure name. But it is not working and this is only giving me error name no line number.
Is there any way I could find the number of inserts happened every day to a particular table.
View 10 Replies View RelatedWe 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.
Is it possible to find the Ip address and Port number of a database server in Oracle 10g version?
actually i wanta to connect it through Putty.
provide SQL query (not PL/SQL block) for the following. I have data in table as shown below
Serial No value
========= ========
123
2-99
3-99
4-99
534
656
77
88
990
1065
1167
12-99
13-99
14-99
15-99
16-99
17-99
182
1945
206
The output of the SQL will be the largest consecutive series of the value -99 is 6 and total count of the value -99 is 9
In SQL plus
<SQL>
i need to repeat last six or seven commands instead of typeing
is there any command to using arrow keys o backward and forward last typing comamnds
i know / to execute last command
how to do that in SQL plus.
I would like to be able to sort through a schema's tables to find if particular vendor numbers exist. The decode cross tab works fine, except that it needs static data for each table. How can I code a PL/SQL to populate the table_name and give a count for each row? See example below:
col table_name format a15
set numwidth 10
set lines 1000
set pages 50
select b1.table_name,
count(case when a.vendor='86444' then 1 else null end) "86444",
count(case when a.vendor='86445' then 1 else null end) "86445",
count(case when a.vendor='86469' then 1 else null end) "86469",
count(case when a.vendor='86470' then 1 else null end) "86470",
[code]........
is there any query to find number of primary keys present in a table.
View 13 Replies View Relatedwhether the overlapping Multiple series charts are possible in Oracle APEX or not .
I am trying to create two graphs for two data sets , one as marker and another as line , but its giving me issues such as
1) X axis is taking all the points of the first series suppose(
1,5,10,180) itsplotting these first . and its then starting labeling
the x-axis for another one which has values like
(20,50,100,1000,15000)
so my X-axis is now ---> 1,5,10,180, 20,50,100,10000,150000
In case i make my marker chart series as the 2nd sequence , then its drawing all the markers on the right side of the chart .
[URL].........
Workspace:- NR_WORKSPACE
Username:-user1
password:- H123$
whether this thing is achievable in oracle apex or not , is there any work around ?