SQL & PL/SQL :: Select Row With Max ID?
May 3, 2010
I have a table as follows
CREATE TABLE ANALYSIS_CRITERIA
( CRITERIA_ID NUMBER(15) ,
FLEX_NUM NUMBER(15) ,
SEGMENT1 VARCHAR2(150 BYTE),
START_DATE DATE,
END_DATE DATE )
I am giving 2 rows of that
INSERT INTO ANALYSIS_CRITERIA VALUES (
833, 50332, '01384456','2009/02/18 00:00:00','2010/02/18 00:00:00');
INSERT INTO PER_ANALYSIS_CRITERIA VALUES (
68457, 50332, '4283-325-A','2010/02/19 00:00:00', '2011/02/18 00:00:00');
COMMIT;
My objective is to pick the row having max of CRITERIA_ID for a given FLEX_NUM.For example in this case for FLEX_NUM=50332 only the row with CRITERIA_ID=68457 should be picked.
View 4 Replies
ADVERTISEMENT
Mar 3, 2011
I have a table with around 80 columns. All i need is to select first 40 columns.
Is there any way to select first 40 columns without giving all the 40 Column Names in select clause.
View 2 Replies
View Related
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Oct 5, 2012
I M USING APEX 4.1 AND CREATED SELECT LIST ON PAGE, I WANT TO SHOW MIN VALUE OF THE SELECT LIST FOR THAT I WROTE IN THAT SELECT LIST PROPERTIES UNDER DEFAULT TAG MIN; AND CHOOSE PL/SQL EXPRESSION BUT ITS GIVING ERROR "Error computing item default value for page item P1_PRODUCT."
BUT IF I HARDCORE THE VALUE CONTAINING IN MY DATA LIKE PRODUCT ID = 1, I HARDCODED IN DEFAULT VALUE 1 AND SELECT PL/SQL EXPRESSION IT WORKS.
BUT ITS NOT DONE LIKE THIS I WANT TO SELECT BY DEFAULT MIN VALUE OF THE SELECT LIST, SO THAT THE DATA SHOULD BE DISPLAYED ACCORDING TO THAT.
THE EXACT REQUIREMENT IS TO ENTER THE SELECT LIST DEFAULT VALUE IN SESSION SO THAT DATA IS TO BE DISPLAYED.
View 7 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Jan 20, 2012
The followinf query is working perfectly.
select dated, ccode, amount
from vcusthist1
where aors='A'
and code in ('C','B')
and (dated,ccode) in (
[code]...
But when i add a further Select satatement, it does not execute and hangs.
Select *
from
(
select dated, ccode, amount
from vcusthist1
[code]...
What is the possible cause of hanging/not working?Note: I am not giving table/view (vcusthist1 is basically a view) structure as my first query is working perfectly and want to do more operations on outcome of the query no one but a further simple select is creating problem.
So badsically how come the following query can create problem?
Select *
from
(
select *
from a_table_or_view
)
View 10 Replies
View Related
Nov 9, 2006
In a query which gets executed first:
Select clause or Order by clause? First the columns specified in the select clause are fetched and then ordered or is it the vice -versa? In a query if a psuedo-column rownum is selected, then if we use order by on the rownum column, what happens?
What would be the output of the following query and why?Which part of the query is first executed, is it select clause or order by clause?
select empno,ename,rownum from emp order by rownum desc;
View 1 Replies
View Related
Apr 5, 2011
im as using oracle 8 with sqltools i have a Very large query. and i notice that many things are repeating. so i want to add them to a variable, instead of re-typing them.for example:
select SomeID from SomeTable;
i want SomeID to be put into a variable.but i still want to be able to get a normal select query at the end so that i can see the returned value:
i tried things like:
declare x number;
begin
set x=45454
select x from SomeTable;
end;
but could not get it to work.
View 2 Replies
View Related
Dec 7, 2006
I am using some UPS shipping software that imports shipping information based on an order number that the user enters and we are having performance problems. In the software's configuration you choose one database object (namely a table or view) to get the information from. I have created myView that selects from multiple tables, lookup tables, etc. I must use a view because the software only has the ability to add a " and <importKeyThatIHaveDefined> = <valueEnteredByUser>". For instance " and order_number = 123". It cannot join multiple tables, etc. The address_field1 info is the same for each record with the same order_number so I can just get the first row (or any row with order_number = <enteredOrderNumber> for that matter).
When I first looked at this it was created as something like this:
select order_number, line_number, order_number || '' || line_number as ol, address_field1 from order;Keep in mind that there are more than just this table and more fields selected. They are all joined correctly (for lookup values etc.).
The concatenation was determined to be the bottleneck in performance. ol was defined as the import key to be used and the users were just entering the order number plus a 1 to get ONLY the first line number. Each order (usually) has more than one line and even the line number is not necessarily unique for each order. For instance, order 123 may have line 1 twice and line 2 once.
select order_number, line_number, address_field1 from order where rownum = 1;
The software appended the " and order_number = 123" as expected but because rownum = 1 had already been executed (before the and order_number = 123" the view only returned one row even WITHOUT the order_number = 123.
I need to return only ONE row for a given order number EVEN IF there are multiple records with the same order_number and line_number. Using "where order_number = <enteredOrderNumber> and line_number = 1" is not necessarily unique so that won't work.If I can't make it work this way and can't use the rownum = 1 what options do I have?
View 7 Replies
View Related
Mar 22, 2013
simply select and works great:
select 'CARAT Issue Open' issue_comment, i.issue_id, i.issue_status, i.issue_title, i.ISSUE_summary ,i.issue_description, i.severity,gcrs.Area_name, gcrs.sector_name,
substr(gcrs.stream_name,1,case when instr(gcrs.stream_name,' (')=0 then 100 else instr(gcrs.stream_name,' (')-1 end) ISSUE_DIVISION,
case when gcrs.STREAM_NAME like 'NON-GT%' THEN 'NON-GT' ELSE gcrs.STREAM_NAME END as ISSUE_DIVISION_2
from table(f_carat_issues_as_of('31/MAR/2013')) i inner join v_gcrs_with_stream gcrs on i.segment_id = gcrs.segment_id
where UPPER(ISSUE_STATUS) like '%OPEN%'
Now I want to callte two columns:ISSUE_DIVISION and ISSUE_DIVISION_2
if they are equal in new columns should be value 1 if are not equal should be 0,how can I do it ?
View 4 Replies
View Related
Mar 24, 2008
I have a stored procedure that does a "select name into v_name" SQL statement, which works fine. The only problem is when the query finds no data (the procedure will error because there is no value to put into the variable). Now i have a work around to this by running the query first with a count statement (which will always have results) and then if it is not equal to 0, then i will run the select into.
My question is, is there a better way to handle this kind of issue?
View 1 Replies
View Related
Mar 1, 2012
I am using Oracle Database 9i. I have created a view as follows:
CREATE OR REPLACE FORCE VIEW ipl.abc_test AS
SELECT '10' dsgn, TRIM (b.oc1) code, b.grp, b.POLICY, b.premcode,
b.receipt, b.recptdt, b.PLAN, b.term, b.paymode, b.sumass, b.datcom,b.duedt, b.instl, b.lprem, b.rprem, b.sprem, b.extprem, b.totprem,b.oc10, b.oc9, b.oc8, b.oc7, b.oc6, b.oc5, b.oc4, b.oc3, b.oc2,b.oc1, b.bill_oc1 billno, a.fprdate riskdt,
NVL (a.bmon, TO_CHAR (a.fprdate, 'YYYYMM')) bmon, a.entryopt,
a.extprem3 suplext
[Code]...
View created properly, But when i select from this view this giving message missing right parenthesis. For information the select statement of the view gives multiple rows. Where is the problem.
View 17 Replies
View Related
Sep 6, 2013
here is my query
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.
View 4 Replies
View Related
Sep 4, 2012
I have a column SIX_NO with numbers and null values in it and I would want to display the column with the highest number first and others, in a descending order. I have used the below code
select * from (select six_no from table_name order by six_no desc ) where rownum <=5;
It displays the results as NULL.
View 12 Replies
View Related
May 10, 2013
Suppose I have the following, what is the best way to select the whole row that has the highest (IDX+LEN desc, LEN desc)
IDIDXLEN
5109153
7273118
9165356
I currently have made the following two method, which work but I don't particularly care for.
WITH bob AS
(SELECT 9165 id, 35 idx, 6 len FROM DUAL
UNION
SELECT 5109 id, 15 idx, 3 len FROM DUAL
UNION
SELECT 7273 id, 11 idx, 8 len FROM DUAL)
[code]....
View 3 Replies
View Related
May 11, 2010
I am working on a Script in Perl where i have to find out which Tablespaces are autoextensible and how many space is in use at this spaces - i've found a Standardscript and wanted to customize this with this Autoextensible-clause - but it doesn't work...here ist my script to find out the raw Data.
bb Michaelf20
select a.TABLESPACE_NAME,
A.AUTOEXTENSIBLE
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
[code]....
View 10 Replies
View Related
Jun 25, 2011
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
View 8 Replies
View Related
Sep 24, 2010
Below is my table structure.
create table test_join (
user_name varchar2(20),
attribut varchar2(30),
attribute_val varchar2(30)
);
[code].......
I need to query an users name, manager,location and the manager's manager. Say the info for user A would be,
User : A
Location : Bangalore
Manager : B
Manager's Manager : C
I can get this in two step process.
select attribut,attribute_val from test_join where user_name = 'A';
select attribute_val from test_join where user_name =
(select attribute_val from test_join where user_name = 'A' and ATTRIBUT = 'manager') and
ATTRIBUT = 'manager';
But is there a way to get this in one single query ?
View 11 Replies
View Related
May 30, 2012
Based on ACCOUNT_NUMBER column we have to check the GUID data in test table.
for example ACCOUNT_NUMBER =11 as duplicate it has 2 values then only, we need to check the corresponding GUID if any occurence ("9f680174-cb87-4f71-887a-92" and "9f680174-cb87-4f71-887a-91"),
we should select,if not leave it.
CREATE TABLE test
(
GUID VARCHAR2(32 BYTE),
ACCOUNT_NUMBER NUMBER(30),
INDIVIDUAL_ID NUMBER(13)
)
[Code]...
it tried as like as below it's working.
select t2.* from (select distinct t1.guid
from test t1,( select ACCOUNT_NUMBER from test
group by ACCOUNT_NUMBER having count(ACCOUNT_NUMBER
[Code]...
View 15 Replies
View Related
Feb 18, 2011
I have a sql queries in that i saw one sql querie like this
select columnname,null columnname from tablename
what is the meaning of this which i marked with red (null columanmea)
View 2 Replies
View Related
Apr 11, 2013
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
View 2 Replies
View Related
Feb 2, 2012
I'm looking for a way to select 25% of a view.. I need to do this four time to get all the data. The size of the view changes constantly (I need to split my select because of heavy load on an application)
something like this:
Select * from MyView where [some condition that makes only 0-25% show ]
Select * from MyView where [some condition that makes only 25-50% show ]
Select * from MyView where [some condition that makes only 50-75% show ]
Select * from MyView where [some condition that makes only 75-100% show ]
I tried to search in the forum but found nothing.. maybe ROWID could be used to accomplish this somehow?
View 5 Replies
View Related
Aug 23, 2005
I have requirement where I need to use a regular select statement for a PL/SQL table. How can I do this? Here is a code example:
Create or replace PACKAGE cv IS
type comp_rec is RECORD
(deptno number,
[Code]....
In the function AAA, I am trying to return a ref cursor. I want to base the ref cursor on a PL/SQL table rather than a Oracle table. But unfortunately
open temp_cv for select * from temp_tbl;
is erroring out.
Can I do this?? that is, use a PL/SQL table in a regular select. If not, then, is there a workaround ?
View 4 Replies
View Related
Jun 15, 2010
I have two tables,
CREATE TABLE repos
(
rep_key VARCHAR(10) NOT NULL,
base_term VARCHAR(100) NOT NULL,
blt_key INTEGER NOT NULL
[code]...
gloss table has the unique set of base_term as in repos. BLT_KEY will be primary_key in gloss and foreign key in repos.
Data in gloss table
BLT_KEY BASE_TERM
1 base1
2 base2
3 base3
Now, I need to update the BLT_KEY in gloss to matching entries in repos. Can I do that in a update on select statement? like,
UPDATE repos
SET blt_key = (SELECT gloss.blt_key
FROM repos,
gloss
WHERE repos.base_term = gloss.abase_term) This throws subquery returns more than one row.
And the end of update the repos table should look like,
REP_KEY BASE_TERM BLT_KEY
M001 base1 1
M002 base1 1
M003 base2 2
M004 base1 1
[code]....
Also I need a single query which can update on select as the no of records to be updated are more than 90000 in repos. So two step process would slow down the process
View 13 Replies
View Related
Jul 8, 2013
create table to ( name varchar2(1), b varchar2(4), c number);insert into toto values ( 'a','toto',1);insert into toto values ( 'a','fifi',10);insert into toto values ( 'a','gigi',999);insert into toto values ( 'a','lili',999);insert into toto values ( 'b','mimi',3);insert into toto values ( 'b','vivi',300);
I'm searching for a query which returns a FIRST row which contains MAX(c) for each name :
i.e. : a gigi 999 ( or "a lili 999" I don't care !!) b vivi 300
View 2 Replies
View Related
Jul 22, 2010
When I use the below code in my perl script (it is oracle database):
$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
everything is ok... and when I try to add a third table...
$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx";
$sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!
View 1 Replies
View Related
Mar 8, 2013
why my "select into" not working.i also create the table testjobs but also not working
SQL> select *
2 into testjobs
3 from jobs;
into testjobs
*
ERROR at line 2:
ORA-00905: missing keyword
View 8 Replies
View Related
Jan 8, 2013
i was just looking to find out how it is possible to select * from a sub select that has data coming from 2 tables.
For example:
select *, count(*) AS Count
from
(
select ....does not work, but i am not sure how i can select all fields from the sub query.
View 3 Replies
View Related
Nov 27, 2012
In SQLDeveloper (I use oracle 11g), I can see a table with its all data. However, in sqldevelopr, I cannot select some data of it.
If I use sqlplus, it works fine.
Why I cannot select some data of that table in SQLDeveloper?
View 6 Replies
View Related
Dec 6, 2012
We're using Oracle 10g for development purposes. I have 2 same schema with approximately same data. I'm running same query on 2 schemas and I see that the first schema runs the query around 20sec and the 2.schema less than 1 sec. I thought first that there may be missing constraints or indexes but all are the same.
I checked the plan for the 2 schemas and I see that the plan is different.
Here is the query:
SELECT ccc.ComponentId AS "ComponentId", ccp.Code AS "ParentCode", ccc.Code AS "ChildCode" FROM CatalogueComponent ccp INNER JOIN CatalogueComponent ccc ON ccp.ComponentId = ccc.ParentComponentId WHERE ccc.ComponentId IN (20934777, 1594747)
I'm sending also the 2 output of PLAN results from 2 different schemas.
what should I do to fix the problem with the NAFBCA schema.
Attached File(s)
NAFBCA.JPG ( 57.49K )
Number of downloads: 6
NAFBCA2.JPG ( 60.36K )
Number of downloads: 4
View 1 Replies
View Related
