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
ADVERTISEMENT
Jul 4, 2010
i want to select dynamic column names in my select statement in my function.
View 4 Replies
View Related
Apr 29, 2011
have a performance problem with a simple query, for example:
SELECT *
FROM CA_ADDRESS
WHERE address_k1 = 'L163'
I have an index created in column address_k1. CA_ADDRESS is a 3-4 Million Record table.
I need to improve the performance of the query. How I can improve it?
View 3 Replies
View Related
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
Sep 28, 2011
I'm unable to create a select statement which calculates & brings the below fields :
- Act_Net_Last_Month (LastMonth)
- Act_Net_Same_Month_Last_Year (LM_LastYear)
My Table :
Acc_ID - Period_Name - Period_Year - Act_Net
000044 - Aug-2011 - 2011 - 4493
000044 - Aug-2010 - 2010 - 4300
000044 - Jul-2011 - 2011 - 4389
000044 - Jul-2010 - 2010 - 4266
Example :
Acc_ID - Period_Name - Period_Year - Act_Net - LastMonth - LM_LastYaer
000044 - Aug-2011 - 2011 - 4493 - 4389 - 4266
View 7 Replies
View Related
Nov 19, 2012
I have following queries:-
#select name from v$database;
#select log_mode from v$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
View 7 Replies
View Related
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
Aug 23, 2012
I am new to oracle. I am creating a view, where in the inner query returns below kind data.
column1 column2
-------------------
a y
a y
b y
b n
c y
d n
I want to extract all column1 values which always has "y" in column2.
In this case, output has to be
output
------
a
c
View 10 Replies
View Related
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
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
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
Jul 3, 2011
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
View 3 Replies
View Related
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
Jan 11, 2012
I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).
When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:
SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...
From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?
View 10 Replies
View Related
Dec 29, 2010
Why Blind select is better than Conditional select Statement?
View 10 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
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
May 11, 2011
I'm having some issues with my NVarchar2 columns that I want to solve
My current NLS_CHARACTERSET is 'AL16UTF16', and therefore I can only create NVarchar2 columns with an maximum extension of 2000 bytes. But I do need a column with 4000 bytes length. How can I create an 4000 NVarchar2 column?
(I do not want to use a CLOB column...only a NVarchar2)
View 5 Replies
View Related
Feb 21, 2010
I have a table which have almost 90000 rows.
1. The datatype of some columns is NVARCHAR2. I want to change it to VARCHAR2 and NUMBER for some.
View 5 Replies
View Related
Feb 16, 2011
I have written a trigger & procedure to call a webservice from pl/sql procedure. Everything was working fine until I was told to use nchar & nvarchar2 instead of varchar2 as per requirement. Now I am not able to run the procedure and getting errorcode with return response from server.
I didn't changed the width of columns but only datatype. What precautions do I need to take in code while doing this and what could have caused the error only by converting the data type.
View 5 Replies
View Related
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
Sep 13, 2011
I want to know, Is NVARCHAR2 available in Oracle standard edition?
View 5 Replies
View Related
May 5, 2010
I have a question about select statements, as I am new to them and don't know how to work all the commands yet.
I'm making a select statement that is about half right... it is shown below:
select t.warehouse_id,
t.quantity_on_hand,
c.product_name
from pahtest3.inventories t
join pahtest3.product_information c using (product_id)
WHERE warehouse_id in (7);
I need to take this select statement and make it so it shows all the products that don't have any quantities in the warehouse in addition to the ones that are already being shown in that select statement.
View 2 Replies
View Related
Jun 15, 2007
decode (a.cancel_time,'',sum ((to_date('23:59','hh24:mi') - a.alarm_time)*24*60), sum((a.cancel_time - a.alarm_time)*24*60)) Duration,
After executing this statement. I get negative values if cancel time is null
I want to subtract alarm_time by 24 hours, if cancel time is null. If not null then cancel-time - alarm_time
View 1 Replies
View Related
Aug 23, 2008
how to set the timing on for all SELECT statements in a procudeure...and i want that should come as a output for very SELECT statement slong with my original output.
View 6 Replies
View Related
Sep 1, 2011
I have a need to use the Alias name of a column within the same select statement( because I can't have another select statement using the first select as table - BO tool limitation).
Ex:
Select dept_id, agency, sum(quantity) as "sum_qty"
where sum_qty > 500;
Currently oracle won't allow using alias name Sum_qty in the same select statement. Is there a way to use alias within the same select statement?
View 12 Replies
View Related
Jul 5, 2011
I'm trying to write a simple query so I can do some testing on my application. I am trying to do something like this:
SELECT
Location,
LEVEL,
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
in the where statement, I have put in the 'Active' that isn't a column. I want to be able to be able to change that in the select part. But I am not able to do so.
this is what I have tried:
SELECT
Location,
LEVEL,
Active = 'N' --I want to change this in the to N or Y so I can get different results.
FROM
S_ORG_EXT
where
Location = 'North America' and LEVEL ='Software'
OR location = 'North America'
and Active = 'N'
View 7 Replies
View Related
Feb 11, 2011
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
View 4 Replies
View Related
May 6, 2011
Below is the query that I have:
Quote:
select a.contact, b.db_name
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b,
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
How can I add two more columns into the select statement and the two columns are in the tables that were in the sub query..I would like to have something like this:
Quote:
select a.contact, b.db_name, COLUM1, COLUMN2
from MOM.db_contacts@DB_LINK a, MOM.databases@DB_LINK b, mgmt_targets t, mgmt_current_severity s
where a.DB_ID=b.DB_ID
and b.DB_name=(SELECT unique substr(upper(t.target_name), 1, instr(t.target_name,'_',1,1)-1)
FROM mgmt_targets t JOIN mgmt_current_severity s
ON s.target_guid = t.target_guid
WHERE t.target_type = 'oracle_database'
and UPPER(t.target_name) like '%11GDB%');
View 5 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
