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


ADVERTISEMENT

Performance Tuning :: Derived Column Index

Apr 2, 2011

here is my query

SELECT CURRENTSTEP
FROM (SELECT ( WFENTRY.NAME
|| ','
|| CURRENTSTEP.STEP_ID
) AS CURRENTSTEP,
(CASE
WHEN WFENTRY.NAME IN

[Code]...

in this query I am concatenating tow columns , I use this query as a sub query in my other queries and filter the results with and CURRENTSTEP = ?

here is how I use it

select
sys_audit_id
from
( SELECT
*
FROM
(SELECT
F.FINDING_NUMBER,

[Code]....

I saw adding this as a subquery with the filter and CURRENTSTEP = ? is slowing my query very much , as this is a derived column i cannot add index then how to improve performance for this subquery ?

View 3 Replies View Related

JDeveloper, Java & XML :: Select Data From XML Type Column

Jan 24, 2013

I have a table Table_xml_tmp which has a column MESSAGECONTENTS as XMLType data which contains data as follows


"<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<xclimSchemaMain xmlns="http://clones.telcordia.com">
<response>
<clHeader>
<msgName>queryPlace</msgName>
[code]......

I need to get all rows where node <operationStatus> value as SUCCESS. I am running following query but it is returning null value insted of data of columns

SELECT a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()
FROM Table_xml_tmp a
WHERE a.MESSAGECONTENTS.extract('/clHeader/msgName/object/msgType/sender/senderInstance/operationStatus/text()').getStringVal()= 'SUCCESS'

View 2 Replies View Related

SQL & PL/SQL :: Row To Column / Column Data Can Be Varied From 1 To Any Number Of Words

Mar 21, 2013

I have a table where i have description column which free text column, the data in description column is seperated and i want to corvert 1 row data in multiple rows dependeing on the number of words.

eg

id description
78664 Pumps Alarm from CAMS RTU154

In the above example this column has 5 word so i want data in 5 rows like below

78664 Pumps
78664 Alarm
78664 from
78664 CAMS
78664 RTU154

This column data can be varied from 1 to any number of words.

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

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

SQL & PL/SQL :: How To Update Data In A Column From Another Column

Dec 28, 2010

I was wondering how can I do below statement in oracle

update table1 t1 set t1.column1 = t2.column2
from table2 t2 inner join table3 t3 on t3.column3=t2.column4
where t3.column5 is null

I tried read up merge and update, but not really understand how the syntax works in oracle.

View 2 Replies View Related

SQL & PL/SQL :: Getting Column Data As Column Heading?

Apr 20, 2012

I would like to have column data as column headers.

Tables:

skill_table
SKILL_ID | NAME
3431060 | Stomach
3431064 | Hand
3437806 | Finger

localnode_table (which actually has the order/alignment (like what is next and what is previous) of the name from skill table.

NODE_ID | PREVIOUS_ID | NEXT_ID
3431060 | | 3431064
3431064 | 3431060 | 3437806
3437806 | 3431064

How to make it appear like:

Stomach | Hand | Finger
3431060 | 3431064 | 3437806

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

SQL & PL/SQL :: Select Records By One Column?

May 7, 2012

By select I got records (see table below) and I need to make select where result will be just one row with all values by payer_flag=Y.

There is a table:

CREATE TABLE XXX_MAPE_CC
(
CK_CUSTOMER_CODE VARCHAR2(24 BYTE),

[Code].....

And result should look like:

--------------------------------------------------------------------------------------------
ck_customer_code | invoice_media | personal_no | cnt_active | cnt_deactive | payer_flag
--------------------------------------------------------------------------------------------
6.107441.10 | electronic | 0 | 663 | 128 | Y
---------------------------------------------------------------------------------------------

View 6 Replies View Related

PL/SQL :: How To Select Max Value Of Date Column

Feb 14, 2013

How to select max value of date column which tables are having date coulmn.

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

SQL & PL/SQSL :: Column Select On MV

Jun 20, 2011

I have created a MV here but i cant select the columns from the MV.

create materialized view WGA_ODB_VALID_ATT_Q1_MV
BUILD IMMEDIATE
REFRESH COMPLETE
AS
select
a.userid "Userid",
COL10 "Order_Action",
DUEDATE "Date_Processed",
COL7 "FEIN",
[code].......

Error : ORA -00904: "ORDER_ACTION":invalid identifier

View 1 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 :: Select Column Values Into Array

Sep 10, 2013

Is there any way in PL/SQL to select the values from all columns of a table record into an array?

For example:

C1|C2|C3
0 |1 |2

v_array(0) value is 0
v_array(1) values is 1
v_array(2) values is 2

or

v_array(C1) value is 0
v_array(C2) values is 1
v_array(C3) values is 2

But i need to do this without mention the column names, something like: SELECT * FROM TABLE WHERE id=1 INTO v_array;

View 10 Replies View Related

SQL & PL/SQL :: Add Difference Column In Select Statement

Aug 29, 2012

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

View 7 Replies View Related

Substitute Column Name From Variable For Select Query?

Jan 24, 2012

In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below.

Create table Test1(IDNumber(10),
Namevarchar2(100),
Descriptionvarchar2(1000),
Sitevarchar2(100));

Insert Into Test1 values(10,'Thomas','Manager','LA');
Insert Into Test1 values(11,'George','Chairman','LA');

declare
MTest1Test1%RowType;
str1VarChar2(1000):='Name';
MValue1Varchar2(100);
begin
Select * Into MTest1 from Test1 where ID=10;
str1:='Select MTest1.'||str1||' from dual';
execute immediate str1 into mvalue1;
dbms_output.put_line('mvalue1 '||mvalue1);
end;
/

I am getting the error:

ORA-00904: "MTEST1"."NAME": invalid identifier
ORA-06512: at line 9

View 5 Replies View Related

Multi Column Index Select Performance

Dec 11, 2012

explain slow performance of multicolumn indexes on oracle 11g R2 observed in the following scenario? A multi-column index (b-tree index) not partitioned, not unique, not reversed with 3 columns.

A series of queries are run using all 3 columns. The performance hit comes when the first order column values changes. So, maybe after 10 select queries the value changes. The 2nd and 3rd order columns are changing throughout the series of select calls, but no performance bottleneck it hit then.

View 2 Replies View Related

SQL & PL/SQL :: Select Only Rows Where Certain Column Repeating Values

Mar 6, 2012

I am trying to come up with a sql select statement that provides all rows for employees with 2 or more cities.

with sample_table as (
select 'John' name,'city' ValueType,'Toronto' Value from dual union all
select 'John' name,'city' ValueType,'Vancouver' Value from dual union all
select 'Susan' name,'city' ValueType,'Toronto' Value from dual union all
select 'Susan' name,'city' ValueType,'Seattle' Value from dual union all
select 'Susan' name,'age' ValueType,30 Value from dual union all
select 'Susan' name,'city' ValueType,'Atlanta' Value from dual union all

[Code]...

NAME VALUETYPE VALUE
----------- ------------- ------------
John City Toronto
John City Vancouver
Susan City Toronto
Susan City Seattle
Susan Age 30
Susan City Atlanta
David City Chicago
David age 35
David Status married
David City Dallas

The above code is just to describe the sample table and the desired result set. Please note that Mary is not on the result set since she has no city assigned to her. Also Julia is not on the result set since she only has one city assigned to her. The others are there because they had at least 2 cities assigned to them.

I need the sql syntax that would return this result set.

View 6 Replies View Related

SQL & PL/SQL :: How To Select Only Records That Have Multiple Values For A Column

Nov 21, 2011

I'm trying to select id's in a table that have 2 certain values for another column. Example below explains:

idCoupon Type
123Amount
123Percent
456Amount
789Percent

I would like to write a sql statement that would select all rows where id=123, because id 123 has both coupon types "Amount" and "Percent". So the result set of the sql statement would look like:

idCoupon Type
123Amount
123Percent

View 6 Replies View Related

Security :: VPD Function For Column Masking With Inner-select?

Sep 12, 2013

I'm writing a VPD function to be used for column masking. The predicate (WHERE-clause) it generates may take many different forms. In particular, it may contain inner-selects; for example,

"exists(select '*' from B where B.VAL = '123' and A.KEY = B.KEY)"

where A is the table that is associated to the VPD function, and B is some other table.

if this is OK for column masking? If not, my VPD function may sometimes work and sometimes fail, in unexpected ways.

The Oracle Database Security Guide (11g Release 1) says

Column-masking conditions generated by the policy function must be simple Boolean expressions, unlike regular Oracle Virtual Private Database predicates".

This seems to indicate that there are cases where a VPD function works for row-level security, but not for column masking.

an example of a 'regular Oracle VPD predicate' that doesn't work for column-masking?

View 5 Replies View Related

SQL & PL/SQL :: Select Numeric Values From A Varchar Column

Feb 5, 2011

select numeric values from a varchar column

For Example:

select * from t1 ;

ID
----------
00300
ABCXY
04230
xyzab

i need to fetch only numeric values from column id

My output should be

00300
04230

View 8 Replies View Related

SQL & PL/SQL :: Substitute Column Name From Variable For Select Query

Jan 27, 2012

In a select query, I want to substitute the column name from a variable (In turn from an array of column names). Details are given below. I am getting an error as mentioned below. sort it out?

Create table Test1(ID Number(10),
Name varchar2(100),
Description varchar2(1000),
Site varchar2(100));

[Code]...

I am getting the error:

ORA-00904: "MTEST1"."NAME": invalid identifier

ORA-06512: at line 9

My objective is to get the name 'Thomas' in the variable - MValue1. I don't want to use the statement MValue1:=MTest1.Name because, moving forward, I want to have the column name to be dynamic, taken from an array of column names.

I need to achieve it by giving only one select query to get entire record and fetch the column values one by one, by substituting the column name from an array of column names, instead of writing that many lines of code / querying that may times to the database.

View 3 Replies View Related

SQL & PL/SQL :: How To Select A Column Dynamically Inside A Query

May 1, 2011

I have a problem executing a function.

There are two tables,

1. Table with column names of a second table mapped to certain variables.

ex: Table1
col1, var1
col2, var2
col3, var3
--------
--------

2. Table with the values for columns as given in table 1 ex:

col1, col2, col3, col4, col5,
a , aa , 1 , x1 , p
b , ab , 2 , x2 , q
c , ac , 3 , x3 , r

I have to select values from table2.col1 do some processing and calculate values and store it in a table then do the same thing with col2 and so. This needs to be done for all the columns that appear in table1.For example in table 1 i have only three columns mentioned thus i have to process col1, col2 and col3 from table2. col4 and col5 will not be processed since they do not appear in the first table.

The problem is i have hundred columns in table 2 and the user can add up to hundred columns in table 1 as and when it is required.

I have created a cursor to first select column name from table 1 where variable is not null.For each value in cursor i put it in a local variable.

Second step is to select values from table2 where instead of column name i am using the local variable.But the problem is instead of choosing values from col1 the query returns the value as col1 (the value of local variable)

View 2 Replies View Related

SQL & PL/SQL :: Select Multiple Values Into Single Column

Oct 5, 2011

I have following tables with data as under:

table1: table2:
column1 (char) column1 (char) column2 (num)
A A 10
B A 20
C B 15
D C 12
E D 25
D 9

I need to generate output as :

column1 column2
A A10, A20
B B15
C C12
D D25,D9
E null

Is there anyway to achieve this thru simple SELECT ...and if not, then thru any PL/SQL construct..?

View 5 Replies View Related

PL/SQL :: Query NVARCHAR2 Column In Select Statement

Sep 3, 2013

I have table desc xx_testName    

Null     Type          

-------- -------- --------------

COL1     NOT NULL NVARCHAR2(100)  COL2     NOT NULL NVARCHAR2(100) COL3     NOT NULL NVARCHAR2(100)

i am able to query select * from xx_test however if i query as select col1 from xx_test then it is giving error.

ORA-00904: "COL1": invalid identifier00904. 00000 -  "%s: invalid identifier"*

Cause:    *Action:Error at Line: 3,131 Column: 13  

Let me know how to query NVARCHAR2 column and how can we put in WHERE condition ?

View 5 Replies View Related







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