SQL & PL/SQL :: Select-on-Select?

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


ADVERTISEMENT

SQL & PL/SQL :: Select First 40 Columns Without Giving All Column Names In Select Clause?

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

SQL & PL/SQL :: Select Dynamic Column Names In Select Statement In Function?

Jul 4, 2010

i want to select dynamic column names in my select statement in my function.

View 4 Replies View Related

Application Express :: How To Select MIN Value Under Default Tag Of Select List

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

SQL & PL/SQL :: Why Blind Select Is Better Than Conditional Select Statement

Dec 29, 2010

Why Blind select is better than Conditional select Statement?

View 10 Replies View Related

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 View Related

Select Vs Order By

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

How To Use Variables Within Select

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

Select One Row From A View

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

If Statement In Select?

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

Handle When No Value In (Select Into)?

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

SQL & PL/SQL :: Select From View

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

SQL & PL/SQL :: Select Max Date And Pk?

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

SQL & PL/SQL :: Select MAX Value From A Column?

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

SQL & PL/SQL :: Select Whole Row Based On Max Value?

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

SQL & PL/SQL :: Select On DBA_DATA_FILES

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

SQL & PL/SQL :: Select Only Last Date?

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

SQL & PL/SQL :: Select From Table

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

SQL & PL/SQL :: Select Record As Specified

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

SQL & PL/SQL :: Select Null Value

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

SQL & PL/SQL :: Rows Not In Inner Select?

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

SQL & PL/SQL :: Select First 25% Of Rowid

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

SQL & PL/SQL :: How To Select From A Table

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

SQL & PL/SQL :: Updating On Select?

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

ISQL*Plus :: Select A First Row Which Contains A MAX Value

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

3 Tables In SELECT

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

SQL & PL/SQL :: Select INTO Is Not Working

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

PL/SQL :: Select All With Count And Sum

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

Cannot Select Data

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

Different Plans On Select Query

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







Copyrights 2005-15 www.BigResource.com, All rights reserved