I have a select query select userid from user_tbl where name='hhh';
I want to return 0 when no records are fetched and I want to handle both teh cases in a single query other than proc or function.
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?
I've stucked with a query. I have a table that i store the IDs of logically equal records.
For example;
A = B
B = C
X = Y
Z = Y
My query must return all equivalent records. If you call the query with parameter 'A', the result set must contain B and C. And if you call the query with parameter 'Y', the result set will contain X AND Z. I have thought that i can write the query wity using start with connect by statement. But the query does not work as i expected. Here is my code and sample data:
create table temptable (ID1 number,ID2 number);/
insert into temptable values(11,12);/
insert into temptable values(12,13);/
insert into temptable values(13,14);/
insert into temptable values(13,15);/
SELECT distinct ID1 from
(
SELECT * FROM temptable
START WITH ID1 = 13 OR ID2 = 13
CONNECT BY NOCYCLE
(
(PRIOR ID1 = ID1) OR
(PRIOR ID1 = ID2) OR
(PRIOR ID2 = ID1) OR
(PRIOR ID2 = ID2))
) WHERE ID1 <> 13
union
[code]....
When i call the query with parameter 13, i'm expecting to get 11,12,14,15. But it returns only 12,14 and 15.
Having following table:
UserID REC_TYP REC_CD
12345 'OFFR' 12
23456 'MSG' 13
I'd like to construct the query which in this particular case would return the REC_CD as 'Record_ID' for REC_TYP='OFFR' where USERID=? (always fetched by the application) and if such USER_ID doesn't exists (for the particular REC_TYP of course) to return string or any other value. e.g. The result for this query in case of user_id 23456 = would be "doesn't exist" or sth for instance 'FALSE' and for 123456 it would be '12'
I need to get the super set(parent) record from a relational table. Following is the structure of the table,
create table relation (data_field1 number(20),data_field2 number(20),data_field3 number(20),
primary key (data_field1,data_field2,data_field3) );
insert into relation (data_field1, data_field2, data_field3) values (-10,123,334);
insert into relation (data_field1, data_field2, data_field3) values (200,123,334);
insert into relation (data_field1, data_field2, data_field3) values (300,123,334);
insert into relation (data_field1, data_field2, data_field3) values (400,123,334);
[code].....
Here '-10' will be considered as parent(super set) for all other values. If i have records like below,
-10 200 300
100 200 300
123 200 300
521 201 300
Output should only contain two rows. A super set row and other distinct row(second column value is not equivalent to super set).
I have a Select query which is not fetching records in 11g (11.2.0.2.0) but working fine in 10g (10.2.0.4.0). The query is as below.
--CREATE TABLE t1 (col1 NUMBER, col2 VARCHAR2 (15 CHAR), flag varchar2(1))
--insert into t1(col1, col2, flag) values(1, 'a', 'Y');
--insert into t1(col1, col2, flag) values(2, 'b', 'N');
SELECT *
FROM t1 x
WHERE col1 = 1 AND col2 = 'a' -------------- condition1
AND 0 = -------------- condition2
NVL (
(SELECT COUNT (1)
FROM t1 y
WHERE y.flag = 'N'
AND x.col1 = y.col1
AND x.col2 = y.col2),0)--=0
When remove NVL function or change the condition by having AND NVL(SELECT) =0 the query working fine.
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
We are trying insert records from a select query into temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
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
I have a table like this basic example:
ID Product Color Time-In
1 Apple Green May
2 Apple Red April
3 Pear Green May
4 Pear Green April
5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May
2 Apple Red April
3 Pear Green May
5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
I would like to know, if there is a solution under 10g for something like this
select case when "C" = 1
then
(select 1,2,3,4,5 from dual)
else
(select 6,7,8,9,0 from dual)
end
from dual,
(select 1 "C" from dual)
What I really need is the full result of (select 1,2,3,4,5 from dual) or (select 6,7,8,9,0 from dual) if a condition as a result of a different sql-statement is true or false. It doesn't matter if the solution uses "case when" or anything else.
I have been using With for many queries for readability. This most recent query seemed to be taking a bit longer to run and I didn't think much of it until a colleague showed me their version (totally different sql statement) which ran faster, but seemed more complicated and had more lines of code. I noticed that the other version did not use with. I moved the sql of the with into the join and the query ran faster dropping from 30 seconds to 798 msecs. The question is am I sacrificing speed for readability by using With, or it really depends on the overall sql statement.
Here is the query using WITH:
with prev as
(
select person_uid, id, name, academic_period,
case when enrolled_ind = 'Y' and registered_ind = 'N' and student_status = 'AS' then 0 else 1 end as enreg_stat,
[Code]....
nex lines showing SELECT DISTINCT return different rows when used with ORDER BY.
CREATE TABLE M1( ID_ NUMBER, A NUMBER, B NUMBER);
CREATE TABLE V1 ( A NUMBER, B2 NUMBER);
DELETE FROM M1;
DELETE FROM V1;
INSERT INTO M1 (ID_, A,B) VALUES (107, 5,1);
INSERT INTO M1 (ID_, A,B) VALUES (108, 11,1);
INSERT INTO M1 (ID_, A,B) VALUES (109, 17,1);
[code]....
How to call a function with a row type return in an Oracle select statement.
For e.g. :
If I had this function with a rowtype return:
------------------------------
create function abc
return xyz%rowtype
is
rec xyz%rowtype;
begin
select * into rec from xyz where col1 = n;
return rec;
end;
--------------------------------
How could I use this in a select clause, as there is a multi column return by the function ?
I have developed a simple outbound program which creates a CSV file for all the batches uploaded in Oracle. The outbound program has a column named last_run_date
The outbound program checks for the last_update_date of batches should be greater than the last_run_date.
But the outbound program misses some rows randomly. When I run the same cursor individually, it fetches them as no other condition is violated.
I am not really able to debug the issue whether its temp table space issue or anything else.
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 null
2666054111 06-154 77.000 85.000 null 85.370
2666054111 06-154 77.000 85.000 null null
I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 85.370
this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.
is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date
2012-10-05
2012-10-06
2012-10-07
2012-10-08
2012-10-09
2012-10-10
2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date
2012-10-16
2012-10-17
2012-10-18
2012-10-19
2012-10-20
2012-10-21
2012-10-22
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
i am trying to update below statement that has multiple rows but giving the error like :
update test t
set (t.org_id) =
(select o.org_id
from organisation o inner join test t
on (o.name=t.full_name
or o.name=t.chart_name))
error:- single return sub query return more value.
how to write update join query base on multi[ple ow.
I have a view base on this query returned more rows returned.
select *
From (
Select c_code ,
from_date,
c_range ,
[code].........
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
I am Having below query which is having total 664 records and for WHERE Clause (accountno ='13987135') it is having 3 records but when i am taking count it is returning 3 at first time and again returning 4 every time from then onwords.
SELECT Count(*) cnt FROM(SELECT rownum rnum,
secno,
positionname,
tradingsymbol,
cusipcum,
businessclientname,
businessclientid,
[code].....
I have not found a solution for this, but it could be that I don't know what to search for.
I need to edit the following statement:
SELECT a.id||a.name||a.amount*2 as transaction
FROM a
WHERE a.amount IN (500, 1000)
To return:
TRANSACTION
------------------------------------
123SMITH1000
123SMITHADJUSTED AMOUNT PER X
456JONES2000
456JONESADJUSTED AMOUNT PER X
The returned rows need to be in this format to be executed in another database.
Can this be done?
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
Just want to confirm, that where Oracle stores data fetched from inline view.
I am asking, as I am making an inline view on a very large table and its getting failed with TEMP space error.
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
I have this query that returns results that contain duplicates(somewhat). I only want either the FIRST or LAST (either one is fine). Here is the query:
select unique PLLA.attribute4, PLA.item_description from po_lines_all PLA, po_line_locations_all PLLA
where PLLA.po_line_id = PLA.po_line_id
and PLLA.attribute4 is not null
So my output is something like this:
RCE12 This is an item for AUL1
RCE13 This is an item for PWEILL
RCE14 This is an item for AUL1
I just want either the RCE12 or RCE14 record and not both since they both have the same description.
I need to insert values fetched from an object type into an empty table.
-- >> table creation:
create table ASSET
(
ASSETID NUMBER(5),
ASSETTYPE VARCHAR2(20));
-- >> created an object_type
SQL> create or replace type obj1 as object(v_ASSETID NUMBER(20),
2 v_ASSETTYPE varchar2(20));
3 /
Type created
--- >> create the package
SQL> create or replace package test_arr is
2 type nest_tab is table of obj1;
3 procedure insert_data(t_nest IN nest_tab);
4 end test_arr;
5 /
Package created
SQL> create or replace package body test_arr is
2 procedure insert_data(t_nest IN nest_tab) is
3 begin
4 for i in t_nest.first..t_nest.last loop
5 insert into asset(ASSETID,
6 ASSETTYPE) values (t_nest(i).v_ASSETID,
[code]....
-- >> a block to execute the above package:
SQL> declare
2 type ref_tab is table of obj1;
3 ref_tab1 ref_tab;
4 begin
5 ref_tab1 := ref_tab(1,'a');
6 test_arr.insert_data(ref_tab1);
7 end;
8 /
But I am getting the below error when executing the package:
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 13:
PLS-00306: wrong number or types of arguments in call to 'REF_TAB'
ORA-06550: line 5, column 1:
[code]....
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
I have a need to query a real time production database to return a set of results that spans a three day period. When the three days are consecutive it's easy but sometimes there is a 1 or two day gap between the days. For example I'm querying results from a group of people that work between Tuesday and Saturday. On a Wednesday I need t produce a set of results that spans Tuesday of the current week, and Saturday and Friday of the previous week; on Thursday I need to produce a set of results that that spans Wednesday and Tuesday of the current week and Saturday of the previous week.I'm using SQL Developer to execute the code.
View 7 Replies View Related