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
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.
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedI 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.
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.
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.
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]...
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?
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.
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
---------------------------------------------------------------------------------------------
How to select max value of date column which tables are having date coulmn.
View 1 Replies View RelatedI 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.
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 RelatedIs 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;
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..
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
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.
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.
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
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?
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
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.
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)
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..?
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 ?
(1.) We have an Oracle 10g DB.
(2.) We want to store PDFs/MS Word docs inside BLOB column in a table.
(3.) We need to show PDF/MS Word content to the user, without opening the PDFs/MS World like we normally do (i.e. Using PDV reader/ MS Word).
(4.) i.e. We need to use SQL statements and
(a.) Get all contents of the PDF/MS WORD doc.
(b.) Search for specific strings inside these docs in the BLOB columns.
I need to write a function which will take table name as input and should return all the columns separated by coma (,).
For example I have a table product as
PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001
My function should return the output as
100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001
Is there any inbuilt function available?
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'
I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.
eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..
How to write a query for this inside a loop.
is it possible to use the records returned by a query as column names in a select query.
select (select column_name from dba_tab_cols where table_name='V_$DATABASE' and column_name like '%CONTROL%')
from v$database;
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row