SQL & PL/SQL :: How To Combine Queries When WITH Clause Is Used

Jun 21, 2010

I have 4 select queries.

Query1
Query2
Query3
Query4

First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.

Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.

Third Step: Is to now combine both the data set from First Step and Second Step.

In order to achieve my First and Second Steps I used WITH clause.

With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID

With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID

1. Is that approach right for achieving my First and Second step requirements ?

2. How to achieve Third Step ?

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Combine 2 Queries Into 1 Without Using UNION

Jun 5, 2012

SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........

View 5 Replies View Related

SQL & PL/SQL :: Combine Two Queries To Get Full Output

Jun 8, 2011

how to combine 2 queries as attached in notepad file in order to get full output?

View 3 Replies View Related

Does Trcsess Combine Various Trace Files In Order In Which Queries Executed

May 28, 2013

Does trcsess combine various trace files in the order in which the queries were executed?

View 3 Replies View Related

Auditing For Queries With No Where Clause

Jul 31, 2012

I'd like to audit a table for any SELECT queries that are executed against it with no WHERE clause. I've read the documentation on DBMS_FGA carefully, and as close as I can tell, creating a policy with a NULL audit_condition causes all queries against the table to be audited, which isn't what I'm looking for.

What I'd like is something like this:

DBMS_FGA.ADD_POLICY (
object_schema      =>  'scott',
object_name        =>  'emp',
policy_name        =>  'mypolicy1',
audit_condition    =>  'WHERE CLAUSE IS ABSENT',
audit_column       =>  'comm,sal',
[code].......  

SELECT * FROM EMP;but queries with conditions ('WHERE sal > 400', for instance) are not trapped.

I'm using 11gR2 (11.2.0.2) on OEL.

View 2 Replies View Related

SQL & PL/SQL :: Connect By Not Working With Nested Queries In From Clause

Jan 11, 2012

Using Connect By on a query which has a nested from clause(The from clause fetches around 100k records) gives incorrect results but if the same nested queries are used to build a table and the Connect By is used on the table then the output is correct.

I put the nested queries in a 'WITH' clause and got the correct output also.

I am not sure how to give the code here as you would need dump to make them work. I am giving the a sample

--Non Working Code

SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT bd.item AS con_item, bd.fromid AS compsite,
bd.toid AS bcsite, bd.toid AS ibrsite,

[Code]....

View 1 Replies View Related

PL/SQL :: Define SELECT Clause Once And Reuse It In Many Other Queries?

Mar 16, 2013

Is there a way to define a SELECT clause once and reuse it in many other queries?

I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.

View 12 Replies View Related

Converting MySQL Queries To Oracle Compatible Queries

Jan 23, 2007

our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.

this is the error message:

The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.

SELECT productID,productName FROM products order by productName;

select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3

SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'

SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1

View 6 Replies View Related

SQL & PL/SQL :: Combine Both Selects Into One?

May 11, 2012

Is it possible to combine both these selects into one.?

SELECT a.grouping_set_member_id,
a.service_agreement_id,
a.enrolment_group_id,
a.enrolment_group_class_id,
a.effective_date,

[code]...

Where l_service_agreement_id,l_enrolment_group_id,l_ENROLMENT_GROUP_CLASS_ID,l_effective_date
are vaues from the First SELECT.

View 2 Replies View Related

SQL & PL/SQL :: To Get Output From Table - Possible To Combine A Row?

Mar 6, 2012

To get output from a below mentioned table? can we combine a row like that?

Table: Test_Fruits

ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit
2Vegitable
4 Apple
3 Potato
-----------------------------------------
Expecting output:
Pls guid me how to get like this?
ID_NO_1 LABELS1 ID_NO_2 LABELS2
------- -------- ------- --------
1Fruit 4 Apple
2Vegitable 3 Potato
-----------------------------------------

View 6 Replies View Related

SQL & PL/SQL :: Combine Query Output In One Row

Aug 13, 2010

If I have below two sql statment.

select count(*) from table_a;
select count(*) from table_b;

How can I output it in one row together like below

Table_a | Table_b|
10 | 20|

give me detailed example ....

View 2 Replies View Related

SQL & PL/SQL :: How To Combine 3 Decodes Together On One Line

Mar 4, 2011

I am having trouble putting all 3 of these decodes together on one line. I would like my output to return like this:

ytd_state_tax ytd_ss_tax ytd_medicare_tax
============= ========== ================
182.29 163.28 56.37

if deduct_type=6012 return sum(pdt1.deduct_amount) YTD_MEDICARE
else
if deduct_type=6020 return sum(pdt1.deduct_amount) YTD_STATE_TAX
else
if deduct_type=6010 return sum(pdt1.deduct_amount) YTD_SOCIAL_SEC

[Code]....

View 7 Replies View Related

Aggregate Function - Combine Results

Nov 24, 2009

I have a query more or less like this:

SELECT field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY field1;

For field1 I want to get a count, but if field1 is like 'ABC%' then I want to combine all of those.

So if I have the following:
ABC1 | 5
ABC2 | 10
XYZ1 | 3

I want results like this:
ABC | 15
XYZ1 | 3

I've tried using some case statements like

SELECT CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END AS field1,
COUNT(x) AS COUNT
FROM my_table
GROUP BY CASE
WHEN field1 LIKE 'ABC%' THEN
'ABC'
ELSE
field1
END;

but this just gives me
ABC | 5
ABC | 10
XYZ1 | 3

How can I combine record 1 and 2 from the last record set example above?

View 4 Replies View Related

SQL & PL/SQL :: Combine Update And Insert Command?

Jul 23, 2012

i have a given pl/sql program that first deletes records out of a table and afterwards inserts new rows. now for example 2 rows out of 10 have a foreign constraint and can not be deleted that easily anymore. so i delete the ones i am able to (with the where not exists clause).

now i want to update the records who have a foreign key constraint and the rest with a regular insert. how would i do this the easiest way. i thought i could use insert with a where clause!!

here i have some part from the original

declare
procedure add(
i_id pls_integer,

[Code].....

View 3 Replies View Related

SQL & PL/SQL :: Combine Two Independent Select Statements?

Dec 10, 2010

ive got two select statements which fetches data from different tables. I need to join the two result set . is it possible to do it from sql. Heres the query.

1)

SELECT
COUNT(CASE WHEN (INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=1) THEN 1 END)CUSTOMER_EMAIL,
COUNT(CASE WHEN INTERACTION_TYPE= 'EmailED' AND CONTACT_PARTY=2 THEN 1 END)OTHER_EMAIL,

[Code]....

2)

SELECT
SUM (CHEQUE_TOTAL) CHEQUE_TOTAL
FROM RI_CHEQUE_VOUCHER_REFUND refund
INNER JOIN CH_CASE case ON (case.id = refund.id)
INNER JOIN EVA_ENTITY_DEFINITION ed ON (ed.name= 'ChequeRefundCaseED')
WHERE
case.creation_time<= SYSDATE
AND
case.creation_time>= SYSDATE-7

Again I need to combine the resultset.So the result would look like

Customer_Email Other_Email Customer_Whitemail Other_whitemail Customer_telephone Other_Telephone Cheque_total

View 3 Replies View Related

Combine Tables With Union But Limit Number Of Records?

Sep 13, 2007

Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.

I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?

The query is:

SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath

[code]....

View 1 Replies View Related

SQL & PL/SQL :: Query - Combine Prefix / Sfirst Suffix1 And Last Suffix2?

Mar 5, 2013

I have the following table
PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
0220unknown
011200unknown
021240unknown
025300unknown
031340unknown

[code]...

If code value is same i would like to combine prefix,sfirst suffix1 and last suffix2. So from above table i want something like the following table.

PREFIXSUFFIX1SUFFIX2CodeDescription
0117default
022410unknown
02421426UKred

View 11 Replies View Related

Complex Case Expression - Combine Two Statements To Get Desired Bucket

May 19, 2011

I have a case expression as follows:

(CASE WHEN DATEa=DATEb THEN 0
WHEN DATEa> DATEb THEN NETWORKDAYS(DATEb, DATEa)
WHEN DATEa < DATEb THEN NETWORKDAYS(DATEa, DATEb)
WHEN STATUS='PENDING' THEN NULL
ELSE NULL
END) AS RESULTa,

Now what I need to be able to do is place those results in buckets, similar to this:

(CASE WHEN RESULTa < 0 THEN '<0'
WHEN RESULTa between -1 AND 6 THEN '<=5'
WHEN RESULTa >5 THEN '>5'
ELSE ''
END) AS BUCKETa

I understand that I can't call an alias from a previous case expresson to get these desired results and how I could combine the two statements to get the desired bucket.

View 1 Replies View Related

SQL & PL/SQL :: Write A Query To Combine Amount1 And Amount1 Into Single Column Amount?

Aug 3, 2011

How can I write a query to combine amount1 and amount1 into single column amount?

create table a (
id_code varchar(20),
invoice_date date,
invoice_type number,
interest number,
advance number,
invoice number
);

[code]....

View 7 Replies View Related

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 Replies View Related

UNION ALL Vs 2 Queries

Nov 12, 2012

is there any difference between

- returning from the procedure 2 ref cursors containing result set of 2 queries
- returning from the procedure 1 ref cursor containing result set of that 2 queries as one (with UNION ALL)?

Will 2nd option be faster or similar to 1st?

View 1 Replies View Related

Multiple Sub-queries In View

Jun 16, 2009

I have a table similar to the following,

USER, DETAILS, TYPE, UPDATED
1, user1home1, 1, 01/05/2009
1, user1home2, 1, 02/05/2009
1, user1work, 2, 03/05/2009
1, user1mobile1, 3, 04/05/2009
1, user1mobile2, 3, 05/05/2009
1, user1email, 4, 06/05/2009
1, user1other, 5 ,07/05/2009
2, user2home1, 1, 01/05/2009
2, user2home2, 1, 02/05/2009
[code]...

which contains multiple contact details for users of different types; type 1 is home, type 2 work etc. The following query returns the user's number and the latest home number for that user.

select user, details as latest_home_number from nc_test t
where type = 1
and updated = (select max(updated) from nc_test t2
where t2.user = t.user
and t2.type = t.type)
order by t.user

However I am not very experienced with sql and I am not sure how to create a view which would contain the fields:

user, latest_home_number, latest_work_number

View 3 Replies View Related

Tuning Aggregate Queries?

May 1, 2011

I have performance problem with 7 queries involving groupby clauses in OLAP database.These are queries triggered during siebel DAC run

kumar[size="4"][/size][color="#0000FF"][/color]kumardba

View 5 Replies View Related

How To Convert SQL Queries To Oracle

Nov 16, 2006

I have to change some queries from SQL to Oracle but I couldn't convert these queries because they use some system tables in SQL that I don't know the equivalent Oracle tables. Following are SQL Queries

1. SELECT name, xtype FROM sysobjects WHERE xtype IN('U', 'V') AND name <> 'dtProperties' AND objectproperty(id, 'IsMSShipped') = 0 ORDER BY name

2. SELECT tS.name FROM sysobjects AS tS WHERE (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'U') AND xtype ='U') OR (tS.name IN (SELECT name FROM sysobjects WHERE xtype = 'V') AND xtype ='V')

3. SELECT o.name as TableName, c.name as FieldName, c.colid as Field_Ordinal, t.name as FieldType, c.length as FieldLength, c.prec as FieldPrecision, c.scale as FieldScale, c.isnullable, c.iscomputed, CASE WHEN c.status & 0x80 > 0 THEN 1 ELSE 0 END AS isidentity, columnproperty(o.id, c.name, 'IsRowGuidCol') as isrowguidcol FROM (sysobjects o JOIN syscolumns c ON o.id = c.id) JOIN systypes t On c.xtype = t.xtype WHERE o.xtype IN ('U', 'V') AND (t.xtype = t.xusertype)

View 2 Replies View Related

Process Queries Parallel?

Feb 2, 2005

On a tab page should be displayed the result of four indifferent queries, each based on a stored procedure.At the moment, the queries are processed serially, by the statements:

GO_BLOCK('one');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;
GO_BLOCK('two');
CLEAR_BLOCK(No_Validate);
EXECUTE_QUERY;

Is there a way to processes the queries parallel ?

View 1 Replies View Related

Modifying SQL Queries Before Execution

Jul 30, 2012

We have the following case: an application modifies a table in an Oracle db (10.2.0.3.0).

Unfortunately the update SQL statements from the application always use the condition "where Column1 = 'some given value'" which is wrong (never mind why).

It should be instead "where Column1 = 'some value' and Column2 = 'val for Column2'. The 'val for Column2 will be taken from the very SQL query being issued (we can make the application do an update for Column2 even if the value in it never changes).

So all the update queries from the application look at the moment like that:

"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value'".

We would like to capture them and somehow on the fly modify them to look like that:

"update my_table set Column2 = 'val for Column2', Column3 = 'some other values', Column4 = 'some other value' where Column1 = 'some given value' and Column2 = 'val for Column2'".

Can a trigger "before update" do it? For some reason we cannot at the moment ask the vendor to change the hard code of the application so we are looking for a temporary workaround.

View 3 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

SQL & PL/SQL :: Running Queries Of 2 Different SID On 1 Pane

Jun 4, 2013

I have two queries that I need to run and compare the outputs against each other. Each query runs on a different host. I can run each query on a different pane (Window - I am using Toad for running query). What I am trying to do is:

- Run both queries on a single pane
- Compare the output where if a "study" matches on both query output, then display the result.

To being with, is it possible to run the queries on a single pane by defining SID string as a part of query syntax......?

SELECT study,
TO_CHAR (completed_date, 'mm/dd/yyyy') completed_date, status
FROM ...

SELECT study, name
FROM .....

View 15 Replies View Related

SQL & PL/SQL :: Record Of Last Executed Queries

Sep 15, 2010

I have some troubles when I try to retrieve last executed queries in a database.

For example;

I run the script below:

select distinct t.first_load_time, t.sql_text, t.last_load_time, s.username
from v_$sql t, v$session s
where s.username='SYS'

And as a result, I retrieve the queries executed by SYS user. But the problem is that, if SYS user executed the same query more than once,
only the very first record is shown.

It is like this,

SYS user executes "select * from table_abc" at 10:54:35, and after that SYS executes the same query at 13:45:55. and after running
the query above, I can only see the record which was executed at 10:54:35. I need to see the both results.

View 39 Replies View Related

Reports & Discoverer :: How To Run Two Different Queries

Aug 27, 2010

how to run two different Queries In One Report.

View 7 Replies View Related







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