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


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 :: 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

Application Express :: How To Hide Column Link URL On Status Bar

May 8, 2013

Apex 4.1

I have IR wit one column link target to URL, and it uses javascript:my_function(#COLUMN_VALUE#)as URL, that is, calls some function and passes the value of that column to the function; everything works fine; but when I place a cursor over the link, bottom line of IE9 browser (is it called a 'status bar'?) clearly shows:

'javascript:my_function(<actual value>)' Is there any way to prevent it from showing function name and parameter value? I tried onmouseover="window.status='';"but it does not work

View 3 Replies View Related

Application Express :: Hide Column In Interactive Report In 3.2?

Feb 15, 2013

In Apex version 4.2 in interactive reports, you can stop users from hiding columns by unchecking a check box under column definition.Is there a way to stop users from hiding a specific column in version 3.2.

View 1 Replies View Related

Application Express :: Hide / Show Column In Download To CSV?

Nov 27, 2012

I have an IR where I have to show a column in report, but hide it while downloading to CSV.

I get this by having a condition on this column - NVL(:REQUEST,'EMPTY') NOT IN('CSV','XLS','PDF','XML','RTF')  I also have a column where I have to hide it in the report, but show it while downloading to CSV.

I am able to hide it, but this column doesn't show up while downloading.I am using this condition on this column NVL(:REQUEST,'EMPTY') IN('CSV','XLS','PDF','XML','RTF') APEX - 4.0.2

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

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

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

PL/SQL :: Where Clause In Partition By Column?

Sep 24, 2013

i have in tb1 data like and i need the latest status changed date : when date on which the status of the task changes.for the data below for taskno :

1 i need to get the o/p : 1/2/2013and for taskno :
2 I need the o/p 1/4/2013 taskno :

upd_dateold_status new_status11/1/2013openWIP11/2/2013WIPClosed11/3/2013closed closed21/1/2013openWIP21/2/2013WIPmore_info_needed21/3/2013more_info_neededWIP21/4/2013WIPclosed  im writing the query as below select taskno, max(upd_date) MAX_UPD_DATE from (select taskno, upd_date, old_status, new_status, rank from (select taskno, upd_date, old_status, new_status, rank() over (partition by taskno, old_status, new_status order by upd_date asc) as rankfrom tb1)where rank = 1and old_status <> new_status)group by taskno;

IS there any more efficient way to write this query as the clause "old_status <> new_status" is taking a toll on the performance

View 2 Replies View Related

SQL & PL/SQL :: Expression Column With A Group By Clause?

Mar 28, 2011

getting expression into a Group By query in oracle.

I have a simple table with two columns. 'ID' and 'Amount'

I want output of the SQL to the following (only 2 fields in the output): I have attached the desired output.

select sum(amountheld) from table1
where member_status = 'MEMBER'
group by ID

This group by query works. But how can i get the expression field (the first field which 'TEMPACCOUNT') in this query (based on my attached output).

View 5 Replies View Related

PL/SQL :: Order By In Union Clause In Date Column

Nov 3, 2012

Oracle version : 11.2.0.2 Linux EL6 server

I have a query like below:

select col1, col2, col3, col4
from tab1, tab2, tab3
where conditions
union

[Code]...

Now, the col4 is a date column and I have to order by the entire result sets on it. I know I can do it by (order by col4) or by (order by 4) at the end of the entire query.

But the problem is that, the output is coming in dd-Mon-yyyy (i.e 31-Nov-2012).

I want every output in dd/mm/yyyy format so I need to use to_char function.

But in that case, I cant use the order by clause, because in that case it is getting arranged by character i.e by 1,2,3,4,5 like this.

View 3 Replies View Related

PL/SQL :: Derived Column Data As New Column In SELECT

Jul 31, 2013

I have a INSERT query which is happening with a SELECT query.

 ===================================================

INSERT INTO tbl_fact_effort_lvl_data (  ...............       )                       
SELECT ria.report_id,report_status:  :,
((SELECT lov_num_val                                   
FROM tbl_reference_data                                
WHERE lov_type = 'FREQUENCY'   ) * (SELECT SUM(pph_task)
FROM tbl_ri_process                                      
WHERE report_id = ria.report_id )) TOT_YEARLY_PROD_HOURS   ,TOT_YEARLY_PROD_HOURS * tf.fac_value TOT_FACT_DATA,location_id                                                                                                      
FROM tbl_fact tf  LEFT JOIN ......... ;

==================================================== 

So, here I want to use column alias TOT_YEARLY_PROD_HOURS as another column to derive another column value TOT_FACT_DATA.

View 8 Replies View Related

Performance Tuning :: Index Usage In Order By Clause On Nullable Column

Jan 28, 2011

I came across situation where a Nullable column is not using index for 'order by' clause. I added Not Null condition in the 'where' condition but it wasn't useful. I don't wanted to make composite index with not nullable column or with constant or modify column to 'Not Null'

So I carried out test cases and during which I found that in one case the sql statement does 'fast full scan' for data access but does not use index for 'order by' sorting

here are the steps

Initially I kept the column Nullable

SQL> create sequence s5;
Sequence created.

SQL> create table t5 as select s5.nextval id,a.* from dba_objects a where rownum<1001;
Table created.

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

SQL> create index i5 on t5(id);
Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T5',cascade=>true);
PL/SQL procedure successfully completed.
exit

SQL> alter session set events '10046 trace name context forever, level 12';

select *
from
t5 where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.00 0 16 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=4771 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1157 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 49.49 49.72
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t where id is not null order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=5167 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3141 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 69 0.00 0.00
SQL*Net message from client 69 22.89 28.04

Now I modified the 'id' column to Not Null

SQL> alter table t5 modify id not null;

SQL> set pages 100
SQL> select column_name,nullable from user_tab_columns where table_name='T5';

COLUMN_NAME N
------------------------------ -
ID N
OWNER Y
OBJECT_NAME Y
SUBOBJECT_NAME Y
OBJECT_ID Y
DATA_OBJECT_ID Y
OBJECT_TYPE Y
CREATED Y
LAST_DDL_TIME Y
TIMESTAMP Y
STATUS Y
TEMPORARY Y
GENERATED Y
SECONDARY Y

14 rows selected.

select *
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 29 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 16 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.01 0.01 0 45 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=16 pr=0 pw=0 time=2398 us)
1000 TABLE ACCESS FULL T5 (cr=16 pr=0 pw=0 time=1152 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 37.74 37.91
********************************************************************************

select /*+ index(t i5) */ *
from
t5 t order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 150 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 150 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 TABLE ACCESS BY INDEX ROWID T5 (cr=150 pr=0 pw=0 time=4166 us)
1000 INDEX FULL SCAN I5 (cr=71 pr=0 pw=0 time=3142 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 8.28 8.45

select id
from
t5 order by id

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 68 0.00 0.00 0 6 0 1000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 70 0.00 0.00 0 6 0 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 5

Rows Row Source Operation
------- ---------------------------------------------------
1000 SORT ORDER BY (cr=6 pr=0 pw=0 time=1342 us)
1000 INDEX FAST FULL SCAN I5 (cr=6 pr=0 pw=0 time=1093 us)(object id 4673065)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 68 0.00 0.00
SQL*Net message from client 68 1.88 1.89

Questions are

1) Why adding 'where id is not null wasn't enough for the index to get used in 'order by'?
2) While we got 'fast full scan' why index wasn't used for 'order by' clause?
3) Do we need the indexed column in where clause for being used in 'order by clause' too?
4) Do we need 'order by' clause if we are selecting only the indexed column with sequence generated values?

View 5 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 :: 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

How To Select Table From Known Column Name

Sep 2, 2009

I want to find out all the tables that contains a known column name?

I know a column name called 'receiving_number', but don't what what table it's in.

View 2 Replies View Related

SQL & PL/SQL :: Select Just 1 Row For Each Distinct Column Value?

Feb 16, 2011

i need a Select * from tablename just 1 Row for each distinct ActionMode column value

CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),

[code]...

View 5 Replies View Related

SQL & PL/SQL :: How To Select Last Code From A Column

Jun 27, 2012

i have a table name item.

itemid itemdate Amt
200 17/06/2012 2000
201 18/06/2012 3000
202 19/06/2012 1000
203 20/06/2012 6000
204 21/06/2012 9000
205 22/06/2012 1000
205 24/06/2012 2000
203 26/06/2012 1000

this is the table i have in which repeated itemids for different dates,for example itemid 205 and 203 comes 2 times because that item was consumed again and again in a different date. like this the table data has more than a 20 thousand records;

In which i need to select the last updated dates itemid from this table, because same item id could be consumed 'n' number of times so i need to select it as per the last date or latest dates itemid from this table for each and every item, so how can i select it?

View 6 Replies View Related

SQL & PL/SQL :: Not To Select A Column From A Table

May 10, 2012

If there are 100 colums or above in a table and we dont want select a single(any one) column from those 100 columns. is there any command or function to do this in oracle.

I can do it by writting names of all 99 columns but as this method is lenghty, so need some efficient way to do this.

like in SQL server we have a "except" keyword through which we can rule out the columns .

ex: select * except (column_name1,column_name_2)
from Table_name.

View 6 Replies View Related







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