I am confused with third one.
CODE1. >> Comparing two strings >>
SQL> select greatest('99' ,'100') from dual;
GR
99
2. >> comparing both numbers >>
SQL> select greatest( 99 ,100) from dual;
GR
100
3.>> Comparing string and number >>
SQL> select greatest('99' ,100) from dual;
GR
99
whats the logic behind 99 being returned for thirdone.
I've a table where I've to select the greatest value from each row.
create table find_max ( colA number, colB number, colC number );
insert all
into find_max values (8,12,13)
into find_max values (-1,-22,3)
into find_max values (1,null,13)
into find_max values (-1,-2,-3)
into find_max values (-1,0,-10)
select * from dual;
the desired format of output is:
three fields for colA, colB, colC and max_value
max_value for 1st row is 13, 2nd is 3, 3rd is 13, 4th is -1,5th is 0.
how can I find the max_value without using any built in functions??
I can't understand >>
1. Number 2 is less than number 100,
2. string '2' is greater than string '100'.
>> Numbers are ordered by numerical value; strings are ordered by alphabetical value. >>
I am trying to update the greatest value in a column from a string of other column.
Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only
so for that I had written query as follows:
update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))
The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.
SQL> select greatest ( 1000,null) from dual;
GREATEST(1000,NULL)
-------------------
SQL> with t as
2 ( select 1 a from dual union all
3 select null from dual)
4 select max(a) from t;
MAX(A)
----------
1
Why greatest returns null ( as the greatest value among the list of values) as the greatest value while max returns 1 in the above case?
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>
just i want add diiferece column in my select statement.For Example:-
select
Name,
Salary,
Designation,
date_first_payment,
date_last_payment,
(date_first_payment-date_last_payment) Datediff
from My_table
then its working
select *,(date_first_payment-date_last_payment) Datediff
from My_table
its not working..
setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.
CODESELECT
To_Date(need_date, 'YYYYMMDD') Need_Dt,
To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date,
To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
FROM tableT
WHERE
need_date <= (Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END)
ORDER BY Date_Diff ASC
I need the query to calculate minutes from difference of to number fields. Test case is as below.
DROP TABLE tmp;
CREATE TABLE tmp
(
code NUMBER(4),
stime NUMBER(4,2),
otime NUMBER(4,2)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]......
CODE STIME OTIME
---------- ---------- ----------
1065 20 19.49
1082 20 18.57
1279 19.3 18.59
2075 19.3 15.32
Required output is
CODE STIME OTIME HR_MIN MINUTES
---------- ---------- ---------- ------------- --------
1065 20 19.49 00 HR 21 MIN 21
1082 20 18.57 01 HR 03 MIN 63
1279 19.3 18.59 00 HR 31 MIN 31
2075 19.3 15.32 03 HR 58 MIN 238
If I run the following query I got 997 records by using GROUP BY.
SELECT c.ins_no, b.pd_date,a.project_id,
a.tech_no
FROM mis.tranche_balance a,
FMSRPT.fund_reporting_period b,
ods.proj_info_lookup c,
ods.institution d
WHERE a.su_date = b.pd_date
AND a.project_id = c.project_id
AND c.ins_no = d.ins_no
AND d.sif_code LIKE 'P%'
AND d.sif_code <> 'P-DA'
AND a.date_stamp >='01-JAN-2011'
AND pd_date='31-MAR-2011'
GROUP BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no;
I want to show the extra columns a.date_stamp and a.su_date in the out put so that I have used PARTITION BY in the second query but I got 1079 records.
SELECT c.ins_no, b.pd_date,a.date_stamp,a.su_date, a.project_id,
a.tech_no,
COUNT(*) OVER(PARTITION BY c.ins_no,
b.pd_date,
a.project_id,
a.tech_no)c
[code]....
why I got 1079 records.how to show the two extra columns in the out put whcich are not used in GROUP BY clause.
If I have a "smart" key in a database, say one for which each byte of the key is meaningful, is it generally a good idea to string together a number of LIKEs with ORs? For example, if I want to select people where the last two bytes of that smart code are in a certain list and write:
where smart_key LIKE '%02'
or smart_key LIKE '%03'
or smart_key LIKE '%07'
or smart_key LIKE '%19'
or smart_key LIKE '%23'
or smart_key LIKE '%30'
or smart_key LIKE '%33'
or smart_key LIKE '%34'
or smart_key LIKE '%41'
etc.
Say I string a lot of those together, with 40 or 50 ORs. Right off the bat is it fair to say that's a bad query for Oracle, or do substrings of this sort generally perform well?
I found this query in my sub version repository and really wondering how this working
select to_char(to_date(1000000,'J') ,'JSP') string_value from dual;
I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:
SELECT regexp_replace (regexp_replace ( REGEXp_REPLACE (' !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 | " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','') FROM dual
{code}
Is there a more efficient way to get this done ?
Im trying to generate this link on my result [URL].....
materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..
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 wanna convert the amount of money from number to string such as 144.5 to be one hundred forty four point five is there any function or i have to write my function? How could i put new line in the string?
for example if i have 'SAB Bank' || 'Riyadh'
but i want SAB bank to be displayed in line and Riyadh in line.
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
i want display a string like this using a select statement in oracle 10g.i have tried but not yet done.
example:
-----------
from 'ABCDEFGH' to 'ACEG'
removing 'BDFH' from the source string 'ABCDEFGH'
i giving here the example you can take any valid string i want the result like the above example and also in a dynamic manner means we can give string to a select statement in run time.can it is possible in a select statement only.
I'm trying to map a database (with a stupid schema) into an ontology. That's why I need to do such a stupid query (as you will see). Here is the structure of the table:
CodonUs {
ID STRING PRIMARY KEY,
GCT FLOAT,
GCC FLOAT,
GTC FLOAT,
and so on for all possible triplets
What I would like to do is to have a query that joins the organisme table on the ID and extract the name of the triplet (thus, the name of the column) and the exact value:
SELECT ID, 'GCT' AS Codon, Codon
FROM organisme JOIN codonUs ON Abbrev = ID
The result set should be something like:
ID of the organisme | Name of the triplet | Value of the triplet
Select * from XXX where xxx.a in �123,456�-----> this values comes dynamic to my query.
Here xxx.a will contain values like 123 or 456 like that .
If i try to find length of the string with more than 4000 char in SQL Developer it throws error "ORA-01704: string literal too long". if anything i need to SET in preference.
select length('string with more than 4000 char) from dual;
[URL]...
I have a quite complex view that selects from approx 10 long tables (approx 4M records each) and build one "customer sentence" pre customer id. I will be always getting just one row from this view, eg. select * from my_view where party_id = XYZ. I'll NEVER EVER select the whole view.
The problem is that running a query:
select * from my_view where party_id = XYZ takes really long time, while putting the party_id = XYZ condition directly into the view executes in 0.0 seconds.
After putting a ORDERED FIRST_ROWS(1) hint into a view the execution plans seems to be the same (or very similar) for both queries. Unfortunately, I can not transfer anything but screenshot from the environmnet - therefore I paste the exec plans as screenshots only - pls follow the link: [URL]...
View DDL:
create or replace view my_view as
select /*+ ORDERED FIRST_ROWS(1) */ pt.party_id
pt.party_id as id_klienta_mdm,
pt.master_reference_no as id_klienta_ref_mdm,
[code]...
I have a string like this:
s_list varchar2(234) :=
'asdasd
asfsdf
dsfsdfs
dfsdfs';
How can I find the number of lines in this string? I tried using
INSTR('s_list', '
', 1, 1)
but it gives 0.
Is there any inbuilt function/proc SQL or PL/SQL which can do this?
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
I have a table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:
#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0
Here, I want to update the above field value to a value in the form:
#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0
This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).
I need to concatenate string to the number field in an update statement like this:
update test1 set model_pin = seq_no || '_' || model
where eoc_code like 'AEW%'
When I run this command in sql , I get ERROR at line 1:
ORA-01722: invalid number
I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
Which part of the Posting Guidelines (
[URL]......)
did you NOT read & follow?
Was it the part about using SEARCH (
[URL]......
or GOOGLE (
[URL]......
before posting?
In sql plus How do I get the number of distinct rows of a certain value?
for example
select group_number from records group by group_number
How would I query for the total number of group_numbers in this query?
I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since
View 11 Replies View Related