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


ADVERTISEMENT

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

Concatenating Two Select Statements

Oct 23, 2009

Been trying to concatenate the following two select statements (to then pass as a parameter in DML statement) without much luck.

Select lpad(ST_DAY,'2',0) || '/' || lpad(ST_MONTH,'2',0) from RET_FORMATS;

Select to_char(sysdate,'YYYY')"Year" FROM DUAL;

View 1 Replies View Related

SQL & PL/SQL :: Using $ Substitution In Select Statements?

Oct 11, 2011

Is it possible to have $ substitutions in Select statements ?

For example

Select * from my_table where ID in ${ID_LIST} and DAY >= to_date(${SOME_DATE})
$ID_LIST = (100,200,300)
$SOME_DATE = 10-10-2011 12:12:00

For the ID_LIST is using Prepared statements with ?,?,? the way to go ? Or are there are any Define we can do in SQL plus for this substitions ?

View 2 Replies View Related

Cannot View Database Or Run Select Statements

Jun 7, 2011

I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.

I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.

View 3 Replies View Related

Auditing Unsuccessful Select Statements

Sep 3, 2013

I'm attempting to audit unsuccessful Select statements in order to trap a problem we're experiencing with our application. I have set the AUDIT_TRAIL initialization parameter to DB_EXTENDED, and bounced our database.

I've issued the AUDIT SELECT ANY TABLE WHENEVER NOT SUCCESSFUL command, and when I issue a SELECT statement as an application user, nothing appears in SYS.AUD$ even though the application has issued a select statement which returned no rows.

View 3 Replies View Related

Select Statements - Use A Join - Getting Error?

May 2, 2010

We are doing select statements. I have 3 tables that I need to get information out of and I believe I need to use a join but everything I put into oracle gives me an error.I'm doing the selects for a pharmacy and have a customer table, a drug table, and a prescriptions table.

I need to write a select statement that shows what customers are taking what drugs and how many mgs they take

customer_id,
customer_first_name,
customer_last_name,
drug_id,
drug_name,
prescription_unit

i think i need that information for the select but I cant seem to write a select statement that runs without errors.

View 6 Replies View Related

SQL & PL/SQL :: Combining 2 Select Statements In A View?

Apr 8, 2012

1. I Wnat to analyze the buffer cache hit ratio. This is what i did.

DECLARE
bufcac NUMBER(10, 2);
BEGIN

[Code]....

2. I would like to analyze the PGA and determine what percentage out of the maximum allocated PGA is being used. I tried the code below but can't find the percentage. Sad

SELECT NAME,
To_char(Decode(unit, 'bytes', VALUE / ( 1024 * 1024 ),
VALUE), '999,999,999.9') VALUE,

[Code]....

Apart from the little problem I have with number 2, not figuring out how to get the precentage seeing that all the data is store in the field VALUE.

I would also like to create a view that has both 1 and 2 in it, but whenever I try creating it, I got and error stating and INTO clause is expected.

View 10 Replies View Related

SQL & PL/SQL :: Insert And Update Through A Select Statements

Jul 7, 2010

how can i insert and update to a table in oracle database 10g through a select statement. not using merge.

View 2 Replies View Related

SQL & PL/SQL :: Won't Continue To Other Stuff Like Select / If / Then Statements?

Oct 9, 2013

if i have a function

function test_1
begin
if....then ...end
SELECT...
BULK COLLECT INTO..
FROM ....
[code]...

obviously this is not the whole code, but lets say the first if...then..end is true , does this mean it will exit the block? like it won't continue to the other stuff like select, if ...then statements?

View 15 Replies View Related

SQL & PL/SQL :: Bulk Collect And For All Statements To Select And Insert

Feb 19, 2013

I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.

here modified the code and provided only one cursor.

Create Or Replace Procedure Sales_Hist_Update_Bkp Is
Type Type_Name Is Record(
Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
[code]...

View 11 Replies View Related

SQL & PL/SQL :: Retrieve Data With Multiple Select Statements

Jun 3, 2012

I'm trying to retrieve data with multiple select statements. The query works fine for 1 account (segment6) but fails for more than account. Below is the query.

select PERIOD_NAME "Month/Year",segment1 "Company" ,Segment6 "GL Account", currency_code "Currency",Mmt "Movement",
BEGIN_BALANCE+PERIOD_DR-PERIOD_CR "Balance At Date"
FROM
(select gb.period_name "PERIOD_NAME",decode (gcc.segment1, '20', 'Internode', '10', 'Agile') SEGMENT1,
gcc.segment6 "SEGMENT6", gb.currency_code "CURRENCY_CODE", gb.begin_balance_dr "BBDR", gb.begin_balance_cr "BBCR",

[code].....

View 3 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

SQL & PL/SQL :: Merge Two Statements (Select ID) Into Single Output

Aug 11, 2013

I want to merge the following two sql statements into single output.

select id,count(*) from derailed where changed_on between to_date('26-july-13 18:30:00','DD-Mon-YY hh24:MI:SS') and to_date('01-August-13 18:29:00','DD-Mon-YY HH24:MI:SS') group by id;

select id,code from dbo;

View 11 Replies View Related

Oracle 10g - Loading In Computer Select Statements Say No Data Found?

Jun 7, 2011

I am using oracle 10g i have a table on my computer that i made for a friend when i load it on their computer the select statements say no data found if i use select * from table name all the data will show

if a column name select * from table name where duty_date = '05-JAN-11'no data found

View 1 Replies View Related

Server Utilities :: Possible To Configure Auditing To Get Number Of Rows Returned By Select Statements

Aug 22, 2011

I am importing some data from Oracle into another database on a regular basis. It works fine for most of the queries but couple of queries don't work sometimes (random). I don't get any errors or any data.

We switched on the Oracle auditing to find out the queries being sent to oracle db. We can see all the queries in the Audit log. Is it possible to configure Auditing to get the "Number of Rows" returned by Select statements so that we can be sure that some data was returned.

View 8 Replies View Related

SQL & PL/SQL :: Finding Maximum Value Of Two Independent Columns Of Single Table

Jan 18, 2012

The challenge I am currently facing is finding the maximum value of two independent columns of a single table. Here is the structure of the table:

[FiscalYear] [AccountingPeriod]
------------ ------------------
[2012] ------------- [2]
[2011] ------------- [12]
[2012] ------------- [1]

What I need to be able to do is to:

1) Determine the maximum value present in column FiscalYear, and then the maximum value available for this FiscalYear under the column Accounting Period.

I can do this fairly easy on Microsoft SQL server, but so far was not able to do this easily on Oracle database. My other observation is that using the MAX function on Oracle is very slow (even with thse fields being indexed). Is it possible to run this query on Oracle with only one pass through the table where the returned result will show 2012 for FiscalYear, and 2 for Accounting Period?

View 11 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 :: Insert Statements / Give A Commit One By One After Each Insert Statements?

Oct 11, 2012

Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?

View 8 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 :: 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 :: 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

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

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

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

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







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