Sequence In Select Ignoring Where Clause?

Sep 28, 2011

I want to use a sequence to populate a field for my insert statement. Should be simple right?

So here is an example
insert into sometable ( seq, else)
select
myseq.nextval
,somethingelse
from sometable@dblink
where somethingelse = 10

now if i remove my sequence call for nextval it correctly uses the where clause but when I use the sequence in the select statement it is ignoring the where clause completely. I've never had this issue before...is this because i'm using a dblink now?

I'm using toad 10 on oracle 11g in multi-schema environment.

View 2 Replies


ADVERTISEMENT

Forms :: Data Block Ignoring Order By Clause?

Mar 6, 2012

I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.

View 6 Replies View Related

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

Reports & Discoverer :: Ignoring Null Values?

Jun 21, 2010

I have a report in report 6 which has static field where values are shown for previous year and for current year. the output of the report will be like this

sl no department prev year cur year
01 revenue 2000 5000
02 costing 500 1000
03 excise 8000 900
and so on....

I have a created a fomula field for each and every department and get the value from a particular table. What i require is if suppose the department revenue does not have any value then that row should not be visible. for eg if revenue has a null value then the report should be like this

sl no department prev year cur year
01 costing 500 1000
02 excise 8000 900

View 6 Replies View Related

SQL & PL/SQL :: Select Clause With Condition

Jul 22, 2010

Based on same salary i need to retrieve data like below whose hire date should be within a week (7days). The table may have 200000 records.

empno Hiredate salary
1234 03-JUN-10 3000
1354 21-MAY-10 3000
1834 01-JUL-10 3000
1954 08-JUL-10 3000

View 9 Replies View Related

SQL & PL/SQL :: Getting Different Column Value For Every Select Clause

Nov 28, 2011

I have a requirement of getting different column value for every select clause. my selection is so much freequent. For example.. 10 Select statement /second.

But when if I am running more than select in a second its is showing the same record which is last dequeue.and also tried with RANDOM(). how to get different value for every statement which is running in single point of time.

View 9 Replies View Related

Create Sequence Using Subselect / Value From Select

Jul 8, 2008

CREATE sequence customer_id start with (select max(customer_id) from customer) increment by 1

i am trying to do that, can it be done or should i just find the max id myself and replace that select with a no?

View 2 Replies View Related

Ignore Case In SQL Select With Where Clause

Oct 11, 2010

We have and Upper and a Lower function.Is there any function or way in which I can issue a select while ignoring the case(like IgNoReCaSe) in a where clause? I don't want to use like

select * from emp where upper(job)= upper('dba');
or
select * from emp where lower(job)= lower('dba');

I just want to know if there is any way in which I could do it without using upper and lower.

View 2 Replies View Related

SQL & PL/SQL :: Hide A Column In Select Clause?

Aug 11, 2011

I need to know if it is possible to hide one or more of the columns in my SELECT statement as I only need them in there where clause.

View 6 Replies View Related

SQL & PL/SQL :: Creating Sequence - Select Returns Integer

Oct 18, 2010

im creating sequences for an already created db and need to set MINVALUE to sequences.

create sequence tb_1
minvalue X
start with X;

so, X == select Max(id_tb1) from tb_1.

I need that select returns an integer.

How can I do this??

View 11 Replies View Related

Using Array Values In IN Clause Of Select Query?

Sep 9, 2008

I have some issues in passing array values to IN clause.

I am passing a String Array from Java to PL\SQL and want to use the Array values in the IN CLAUSE of Select Query

cust_array is the Array
search_id VARCHAR2(1000);
search_id := '';
FOR j IN 1 .. cust_array.count
LOOP
IF (j != 1) THEN
search_id := search_id || ''',''' || cust_array(j) || ''';
ELSE
search_id := search_id || '''' || cust_array(j) || '''';
END IF;
END LOOP;

trying to form a string of below form: search_id '3211335201','3211335209','3211335279','3211335509'

and use the string search_id in the IN clause of the search Query select * from DPP_EMP where empl in (search_id)

but the query does not returns any result

When I try to hardcode the values in the query as below, its returing 4 rows

select * from DPP_EMP where empl in ('3211335201','3211335209','3211335279','3211335509')

How to achieve this (String to the IN clause) or is there a better way of passing the Array values to the IN clause

View 13 Replies View Related

SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query

May 27, 2011

I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.

SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );

[code]....

I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.

select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));

[code]....

Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.

View 3 Replies View Related

PL/SQL :: Define SELECT Clause Once And Reuse It In Many Other Queries?

Mar 16, 2013

Is there a way to define a SELECT clause once and reuse it in many other queries?

I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.

View 12 Replies View Related

SQL & PL/SQL :: View Not Having ORDER BY Clause In SELECT Statement?

Jun 13, 2010

currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :

Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.

The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.

but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.

View 5 Replies View Related

Performance Tuning :: How To Overcome Rownum Clause From Select

Dec 27, 2010

high number of executions of specific types of queries which is using only rownum clause. For exam.

select ani, rowid from tbl_smschat_upuor where rownum<=:"SYS_B_0";

DB is having high number of executions of these type of queries and these when I m checking the execution plan for the same type of queries it is accessing the full table scan.

======================execution plan for above query
1000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 91289622
--------------------------------------------------------------------------------

[code]....

View 3 Replies View Related

Domain Index - Select Query With Clause Does Not Return Any Records

Jun 30, 2012

I have created domain indexes on text columns of a materialised view to use "contains" clause when searching for data. The select query with "contains" clause does not return any records, however I was able to retrive data using via regular query using a like search.

-> will exec ctx_ddl.sync_index('index_name')'resolve my problem?
-> since the view is a materialized view, how can i make sure that the latest data added are also picked up?

View 2 Replies View Related

SQL & PL/SQL :: Different Number Of Result Sets While Adding Further Columns In Select Clause

May 4, 2010

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 PAYMENT_METHOD_MAP.NETTINGGROUP_ID,
PAYMENT_METHOD_MAP.CREDITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.DEBITPAYMENTMETHOD_CD,
PAYMENT_METHOD_MAP.AGENT_ID,
SETTLEMENT.NETTINGGROUP_ID,
SETTLEMENT.SETTLEMENTDATE
[code]....

View 8 Replies View Related

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

Application Express :: Select List From Query With Row Level Where Clause?

May 10, 2013

I am trying to create a tabular form based on a SQL query, that has a query-based select list with a where clause that references a column in the originating SQL query.

The situation is, I have a table that stores client_id, source_id and build_id, lets call it client_source. I have a second table, build_source, that contains source_id and build_id, with a one to many relationship between the two (source_id of 1 could have build_id of 1-7).

Using a tabular form, I want to select the corresponding build_id to be used in client_source, but the select list must only contain the build_id's for that rows particular source_id.

Here is an example of the SQL source of my tabular form;

select
s.ROWID,
s.CLIENT_ID,
s.SOURCE_ID,
APEX_ITEM.SELECT_LIST_FROM_QUERY(1, s.BUILD_ID,
'select b.build_id display, b.build_id return from
build_source b where b.source_id = s.SOURCE_ID ') lst
from client_source s

... what I am trying to achieve is that the source_id fields in bold match. When the query is built this way I get an 'invalid identifier' Oracle error on s.SOURCE_ID at runtime.

Is there some special tags that need to be used to reference the outside column?I am running on Application Express 4.1.0.00.32, on a Oracle 10g release 10.2.0.4.0 database.

View 2 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

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

Get A Query Of Each Sequence?

Jul 24, 2010

How do I get a query of each sequence and who has the permissions to it?

View 1 Replies View Related

Using Sequence In A Trigger

Jun 1, 2009

I have created a trigger for after insert which updates a table when there is a row inserted in that table. The update is on a column which stores the application description along with the sequence number. Now my requirement is that sequence number should be unique only with in an application but not with in the table.Say the row entry can be as follows:

App_Desc Request_ID
-----------------------
DEV 100 1
DEV 101 2
STG 100 3
STG 101 4

Here Request_ID is unique But the sequqnce thats created for DEV (100,101) should take an entry of 102 for the next entry for DEV and same applies for STG. So I have to use the same sequqnce for all the application.

View 1 Replies View Related

Trigger Corresponding To A Sequence

Apr 16, 2007

I have 3 tables whose structure is as follows:

1) Invoice
Invoice_no number(8),
Debtor varchar2(35),
Invoice Date Date,
Print Date Date,
Currency varchar2(3),
Total Amount Ex Number(11,2),
Total Amount Vat Number(11,2),
Total Amount Number(11,2),
Status Varchar2(15),

2) Invoice Line

Invoice_no number(8),
Invoice_Line_No number(8),
Description_1 varchar2(35),
Description_2 varchar2(35),
Line_Quantity Number(11,2),
Currency varchar2(3),
Line_Amount Number(11,2),
Vat Code number(1),
Status Varchar2(15),

3) vat code
vat code number(1),
Description varchar2(35),
Vat Percentage number(11,2)

Here invoice no and invoice line no are automatically generated fields?

How to generate invoice line no corresponding to invoice no and how to satisfy the following conditions:

1) total vat amount in invoice table should be automatically filled . total vat amount=(line_quantity* line_amount*vat percentage)/100

2) invoice cannot be printed, changed when status is 'closed'.

3) invoice lines cannot be added,changed or removed when status of invoice is closed

4) when any of invoice line no is changed,added, removed or invoice is changed the status of invoice should be changed to 'new'.

5) invoice date of a new invoice cannot be of the past

View 1 Replies View Related

How To Set Sequence Cache

Oct 10, 2012

Are there any recommendations or good practices to set sequence CACHE parameter (for example one caching per hour, day etc)?

View 4 Replies View Related

SQL & PL/SQL :: Sequence Skips First Value?

Jul 23, 2010

For some reason Oracle Sequence is skipping the first value. Here is an example of script.

drop sequence tseq;
drop table xyz;
create table xyz
(sno integer);
create sequence tseq
start with 1;
insert into xyz values (tseq.nextval);
select * from xyz;

Now the runtime of the script in SQL Developer:

drop sequence tseq succeeded.
drop table xyz succeeded.
create table succeeded.
create sequence succeeded.
1 rows inserted
SNO
----------------------
2

Why is it skipping the starting value 1. Is there something new in 11g that is causing it to skip the first value. I have many scripts and they all are having errors due to this issue.

View 39 Replies View Related

SQL & PL/SQL :: How To Create A Sequence

Sep 11, 2010

Create a sequence E_SQ which start with the (current max empno + 1) and increment by 1.

View 5 Replies View Related







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