SQL & PL/SQL :: Query Must Return All Equivalent Records

Nov 26, 2012

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.

View 7 Replies


ADVERTISEMENT

SQL & PL/SQL :: Query To Return Value / String If No Records Found

Jun 17, 2012

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'

View 2 Replies View Related

PL/SQL :: Select Query - Return 0 When No Records Fetched

Jun 25, 2013

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.

View 12 Replies View Related

Domain Index - Select Query With Clause Does Not Return Any Records

Jun 30, 2012

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?

View 2 Replies View Related

Query To Get IST equivalent Time

Mar 20, 2013

My requirment is something like below.

My Oracle DB server time is in UTC. which lags by 9:30 mins to IST.how to get tHe IST time (Asia/Kolkotta) time for that UTC timezone? I can not hard code +9:30 to UTC as this difference varies as per Daylight savings every 6 months.

View 3 Replies View Related

SQL & PL/SQL :: How To Make Return All Records Except Duplicates

Mar 29, 2010

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.

View 1 Replies View Related

PL/SQL :: Records Return Faster Inline Rather Than With Or Depends On Situation

Jan 15, 2013

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

View 7 Replies View Related

SQL & PL/SQL :: Return Only One Row Of Data On Query?

Nov 28, 2012

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.

View 3 Replies View Related

PL/SQL :: Query To Return Next 7 Dates

Oct 5, 2012

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

View 13 Replies View Related

SQL & PL/SQL :: Single Return Sub Query Returning More Value

Aug 21, 2013

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.

View 8 Replies View Related

SQL & PL/SQL :: Query Result More Row Return In Oracle

Apr 3, 2013

I have a view base on this query returned more rows returned.

select *
From (
Select c_code ,
from_date,
c_range ,
[code].........

View 5 Replies View Related

PL/SQL :: Single Query Return Multiple Value

Sep 6, 2012

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

View 3 Replies View Related

Inconsistent Count Return By Same Query?

Nov 19, 2012

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

View 4 Replies View Related

SQL & PL/SQL :: Query To Return Two Different Concatenated Rows

Jan 10, 2012

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?

View 8 Replies View Related

SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query

Sep 6, 2010

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.

View 18 Replies View Related

SQL Query - Return Operation Over Multiple Dates

Feb 16, 2011

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

View 5 Replies View Related

Query Return Results That Contain Duplicates - Only First Or Last Required

May 27, 2008

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.

View 2 Replies View Related

SQL & PL/SQL :: Select Query To Return Parent Rows?

Apr 1, 2012

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

View 14 Replies View Related

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

Dec 22, 2011

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 .

View 2 Replies View Related

SQL & PL/SQL :: Query To Return Results From Three Previous Non-consecutive Days?

Dec 10, 2011

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

Application Express :: Finding Query Which Will Return Turnout For Each Day?

Oct 30, 2012

members table
mem_id,registrationdate and some other details

sales table
mem_id,purchasedate and some details

i want a query which will return the turnout for each day i.e. purchasedate and number of new members who made purchase on a particular date.

View 2 Replies View Related

Performance Tuning :: Query Takes More Than 30 Minutes To Return Data

Aug 10, 2012

The below query takes more than 30 minutes to return data.All the objects used are views. There is no direct reference to any table.The views with _mnth_ have data for 7 distinct months. The base table for all the views have a composite PK on the columns AR_ID (or ACCT_AR_ID),MSRMNT_PRD_ID

I need the order by, as the query is part of informatica code, and the order by works in the further processing.

SELECT ac.ar_id AS acct_ar_id, m.msrmnt_prd_dt AS msrmnt_prd_dt
--removed the rest of column list to reduce size of code.
FROM edxf.ar_rsrv_mnth_v ac,
edxf.crdt_acct_mnth_v c,
edxf.crdt_acct_v ca,
(SELECT msrmnt_prd_id, msrmnt_prd_dt
FROM edxf.msrmnt_prd_v
WHERE msrmnt_prd_id =
[code]....

Also, the count of data in the views is as below.

ViewTotal countCount for 1 msrmnt_prd_id
---------------------------------------------------------
ar_rsrv_mnth_v1841892281945
crdt_acct_mnth_v664941457087369
crdt_acct_v12258728NA

View 7 Replies View Related

Performance Tuning :: Query Is Taking More Than 5 Minutes To Return Data For Any Criteria

Aug 17, 2011

The below query is taking more than 5minutes to return the data for any criteria.The big tables are

SECURITY_POSITION_SUMMARY -- 60Million
WEB_TEAM_X_ACCOUNT_BM -- 26Million

and the rest of those are small tables..All the indexes are in place and I have tried with few hints but this query is slow.

WITH REPS
AS (SELECT DISTINCT REP_SET.FILTER_TOKEN
FROM (SELECT /*+ INDEX (wdsd WEBDATASETDTL_PK_TEAM) */
DISTINCT
WDSD.DATA_SETTING_ID, WDSD.FILTER_TOKEN
FROM WEB_DATA_SETTING_DETAIL WDSD,
[code]....

View 1 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.

Is there a simple statement that can be used to fix this in the apex dictionary?

View 1 Replies View Related

SQL & PL/SQL :: Hexadecimal Equivalent

Jun 16, 2011

I need to get the output as ú (Lowercase U-acute) using oracle sql.

SQL> select UTL_RAW.CAST_TO_VARCHAR2(HEXTORAW('<x>')) from dual;

where <x> represents the hexadecimal equivalent for the character 'ú' ,

View 3 Replies View Related

PL/SQL :: Top N Percent Equivalent

Jul 16, 2013

what is the equivalent of Top n Percent in Oracle sql 11g. Here is my requirement:

I have to find stores contributing top 20% of sales: Store Sales PercetageABC200(200/380)*100=52%XYZ100(100/380)*100=26%PQR50(50/380)*100=13%dddd20(20/380)*100=5%rrrr10(10/380)*100=2% 

In the above example I have to get only store ABC as this store alone is contributing more than Top 20%If I change the requirement to Top 70% I have to get store's ABC and XYZ.

View 6 Replies View Related

Upgrade :: 10g Companion CD Equivalent In 11g?

Sep 25, 2013

I am upgrading from 11.2.0.1 to 11.2.0.3 (64 bit) on Windows (64 bit)  I planned to install Oracle 11.2.0.3 on new home and i am not sure wheteher i have to install:  Oracle Gateway and Oracle Examples on the 11.2.0.3 new home.  I am not using any non - oracle so i may not need Oracle Gateway.  I am not sure about examples media . Does it have only examples to learn (or)will it have any optional components which we may require (like companion CD in 10g)?    What about the ODBC and com components will it be installed with the database media itself?  After DB upgradation is it possible to install oracle gateway/examples on the same home?

View 2 Replies View Related

SQL & PL/SQL :: What Would Be The Sybase Str Equivalent In Oracle

Mar 4, 2010

What would be sybase str(approx_numeric [, length [, decimal] ]) equivalent in oracle.

Sybase call :

select str(10000.5555, 6, 3) as Test.

Tset
-------
10001

View 4 Replies View Related

SQL & PL/SQL :: Send Again Function Of Equivalent Of DATEADD

Apr 19, 2011

Is it possible to send again the function of equivalent of DATEADD in sql for plsql??

View 3 Replies View Related

SQL & PL/SQL :: Left Function Equivalent In Oracle 9i

Dec 22, 2006

Is there is an equivalent to the 'LEFT' Function of SQLServer in Oracle9i?

View 10 Replies View Related







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