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
I am using 10g.
I want to do a select statement that will give me the name of the fields in a table. And I also want to know the field type and field size.
Can this be done?
I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
Table A
Id Country city
1 US
2 US Boston
3 Boston
4 US Newyork
5 London
6 Japan Tokyo
Im looking for a query which returns results based on both city and country passed.
If i pass country US and city Boston it should return row2 with US and Boston row
If i pass country null and city Boston it should return row3
If i pass country UK and city Boston it should return row3
If i pass country UK and city London it should return row5
i.e. If country/city combination exists in DB return that row Else city row should be returned.
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
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)
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.
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 ?
Is it possible to hold the data from select statement without temp table or materialized view or view in oracle?
because my DBA does not give access to create temp table.but we are selecting the records from 3 different sql statement.
Example: inserting in temp table
a) insert into temp select empno,ename,sal from emp where sal>4000
b) insert into temp select empno,ename,sal from emp where dept=40
c) insert into temp select empno,ename,sal from emp where comm is null
Yesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1')
insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?
I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?
ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");
select * from course_section
where term_id = (select term_id from term where term_desc in ('Spring 2006','Spring 2007'));
and it is showing:
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.
For example:
select
x,y,z
from
mytable@dblink
returns 788,324 rows
while
select
x,y,z
from
mytable
returns 712,102 rows
It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.
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??
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]....
Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.
Original query and plan
SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....
Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.
I have a table that has hierarchical data within it. I need to select this data in a specific way, showing the hierarchy.
E.g. Data in table (Key is unique)
Lvl KeyParKey HasChild
1k101
1k200
1k301
1k401
2k34k10
2k22k11
2k24k10
2k13k30
2k52k30
2k35k30
2k13k30
2k11k40
3k56k220
3k109k221
3k67k220
4k61 k1090
Etc etc....
That�s generally the format the values would appear in the table if I just did a standard select. I want it displayed in a more hierarchical Parent � child way.
The format I need to get out is as follows:
Lvl KeyParKey hasCh
1k101
2k34k10
2k22k11
3k56k220
3k109k221
4k61 k1090
3k67k220
2k24k10
1k200
1k301
2k13k30
2k52k30
2k35k30
2k13k30
1k401
2k11k40
Here is my case,
create table t (id number,row_id number primary key ,value number);ID row_id value
1 1 10
1 2 11
1 3 1
2 4 11
3 5 11
3 6 12
4 7 12
4 8 12
4 9 13
4 10 11
4 11 10Requirement is
1) To get all the ID that have the value 10
2) The row_ids for the ID I got in I
Can I do this with Hierarchical query. If not which one of below will be fast?
select * from t where id in (select id from t where value=10);
or
select T2.* from t T1, t T2 where T1.ID=t2.id and T1.VALUE=10;
I have billion of rows so I am looking for either Hierarchical query to solve it or some other way . All the three column the index.
I am using the following query with like 'T_%', i am getting 80 rows out of which the first table_name doesn't even have a beginning part 'T_%'.
the first table name has not started with 'T_', why is it appearing.
*********************************************************************
SELECT 'Truncate table epic500.'||table_name
FROM user_tables where table_name like 'T_%' order by table_name;
*********************************************************************
output:
Truncate table epic500.TEMP_ENC_DEL
Truncate table epic500.T_ACCOMMODATION_CODE
i want to select dynamic column names in my select statement in my function.
View 4 Replies View RelatedI 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 ?
I am getting an error "Single row query returns more than one row" in an Exception block.
But in subqueries I am using IN operator not using =.
And I don't have duplicates rows in tables.
this is my sql=
" SELECT
hpn.id AS id,
hpn.psn_id,
hpn.last_name,
[Code].....
"what i want is add subselect something like this (select card_number from REGISTRATION_CARDS x where (hpn.id=x.hpn_id(+)) )as card_number,
but it returns error single row query returns more than one row, because one people can have more that one card_number.
I am trying to update values in a table from another table and getting the error: Single Sub Row Query Returns More Than 1 Row.
I want table B's PRV_NAME updated into table A's PRV_NAME where A.PRVID = B.PRVID where B.PRV_TYPE = M'
Both tables have all unique PRVID's, however, table B has PRVID's that have the same name. So table B data can look like this:
PRVID PRV_NAME
1234 PHOENIX MED
1235 SAC MED
1236 SAC MED
1237 OVERLAND
etc..
So, as you can see the PRVID's are unique, but not the PRV_NAME's. Is this the reason why I get this error?
I did not build the tables and have no control over what is put in them. If this is the reason for the error, is there any way to resolve this?
For reference, here is the query.
update msb_prv_source ps
set ps.prv_name =
(select prv00.prv00_prv_name
from prv00_prv prv00
join msb_prv_source ps
on prv00.prv00_prv_id = ps.prvid
where prv00.prv00_prv_type = 'M')
i like to display a Table data like the below format,
Output:
EMPNO JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
00094 122 153 145 224 245 545 114 544 444 111 555 222
00095 233 123 145 224 205 545 114 444 444 111 555 222
00096 163 123 145 224 215 545 114 551 444 111 555 222
00097 163 0 145 224 215 545 114 551 444 111 555 222
conditions:
where condition:
where year = 2007
Table Structure:
create table HR_PAYSLIP
(
EMP_NO VARCHAR2(6) not null,
YEAR NUMBER(4) not null,
MONTH NUMBER(2) not null,
BASIC_PAY NUMBER(9,2),
)
Insert Command;
INSERT INTO PAYSLIP (EMP_NO, YEAR,MONTH,BASIC_PAY)
VALUES(00046, 2007, 1, 2314);
Pls Note: The above table data i have mentioned is an example with employee numbers and the basic_pay for all months in the particular year 2007, the employee no may be more and that must be displayed only one time like above for year 2007, and if the basic salary is zero for a month then it should be displayed as zero for a particular month
So how to write a Query for that?
I have a table with columns job_id, jan, feb, mar ... , and year
I need to create a select query that will get the data from 18 months ago based on sysdate.
So something like:
Select to_char(add_months(sysdate, -18),'MON') from table1 where job_id = 56947 and year = to_char(add_months(sysdate, -18),'YYYY');
However I need the result of to_char(add_months(sysdate, -18),'MON') to actually act as a column name, not a string result.
Why Blind select is better than Conditional select Statement?
View 10 Replies View Relatedin 10g report builder i have written this query
----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP
[code]....
this query returns multiple row. how to get distinct row.
I am not able to update a table cloumn using below query and getting ORA 01427: single row subquery returns more than one record error.
update fuctions f set id = (Select g.fn_id from access g where g.level = 'A' )
where f.u_id = "ABC";
I understand that problem is there with inner select query.
I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.
View 14 Replies View Related