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.
SQL> select l.state,l.country as DestinationCountry,to_char(t.datefrom,'yyyy-mm'
)as Year_Month,avg(hf.sat_rate)as AverageSatisfactionRate from holiday_fact hf,t
ime1 t,location l where l.state=(select l.state from location l where l.country=
'Australia')and hf.loc_id=l.loc_id and hf.dest_id = l.loc_id and hf.date_from=t.
[Code]....
ERROR at line 1: ORA-01427: single-row subquery returns more than one rowplz tell my mistake.
i am trying to get the states only in Australia and rest of the attributes as they are.
we just installed the patch 10.0.2.0.5 on a 10.0.2.0.3 database and some selects didn't work as before. While changing the select clause, there are different counts.
There are 3 tables:
Detail_1 => MASTER_1 <= Detail_2
MASTER_1 has a primary and an unique Key. The 2 detail tables have FK. Now when selecting only columns from the detail tables (joined with master) we get cartesian selects. If i use one column of the master table in the select clause everything is fine.
Here is an example:
CREATE TABLE MASTER_1
( "KUNID" NUMBER NOT NULL ENABLE,
"CUSTOMER_NO" VARCHAR2(20 BYTE),
CONSTRAINT "PK_MASTER1" PRIMARY KEY ("KUNID"),
CONSTRAINT "UK_MASTER1" UNIQUE ("CUSTOMER_NO"));
[code].......
So you see, we get different rows only by changing the select clause.
Note: It seemed to be important to have both FK. When deleting one of them - or both - we get the same correct results: 3 rows with any select clause.
At my Workplace we have a large Orcle 11g Database with 30 different tables for production control issues.I try to get a couple of different information from the database, so i started with SQL Query's, but for this problem i was not able to write an working query.
In this case i have 2 tables:
Table 1:
ID ;ORDER_NR ;DESCRIPTION ;CREATE_DATE
1 ;A500236 ;CLEAN HOUSE ;02/20/2012
2 ;A623555 ;REPAIR CAR ;01/10/2012
3 ;A866944 ;MAINTAIN EQUIPMENT ;02/11/2012
Table 2:
ID;ORDER_NR;WO_STEP;STEP_DATE;EMPLOYEE
1;A500236 ;A;02/21/2012;W0010
2;A500239 ;F;02/21/2012;W0010
3;A500239 ;S;02/22/2012;W0027
[code]....
And the result of my Query should look like this:
ORDER_NR;DESCRIPTION ;CREATE_DATE;A_STAT_AGE;R_STAT_AGE;U_STAT_AGE
A500236;CLEAN HOUSE ;02/20/2012 ;5 ;3 ;1
A623555;REPAIR CAR ;01/10/2012 ;42 ;39 ;38
A866944;MAINTAIN EQUIPMENT ;02/11/2012 ;15 ;4 ;3
The age of my query result should be calculated from the Create date of the Order.I want to know 2 things, one is how old was the Order when they reached that status A, R and U.The second this ist, how long did the order remain on the stat A,R and U (and if possible all other status also)It could happend that not each order reaches each status, so it ca go directly from A to you in this case i want display a wildcard in this row/column
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
I'm trying to use SYSTDATE in a WHERE clause of nested SELECTS..I want to select a range of info from two days back from today until today (or time it is being run). But when I run this, it says I have a missing expression...
SELECT XXXX
FROM XXXX
WHERE DATE BETWEEN TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') -2)
AND TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'))
The Oracle DB in question is 11.2.0.1, x64, Server 2008.I also have a SQL Server 2005 database that runs a third party product, "PaperVision", which we use to manage documents of various kinds. This SQL Server server is also Win 2008, x64.Now, on the server that runs SQL Server, I have a simple view which is defined as such :
select DOCID,
DOCINDEX1,
DOCINDEX2,
DOCINDEX3,
DOCINDEX4,
DOCINDEX5,
DOCINDEX6,
DOCINDEX7,
DOCINDEX8,
[code]....
This view works great from the SQL Server side. I also created a database link from Oracle to the SQL Server machine, and it also works great.It is defined as such :
CREATE PUBLIC DATABASE LINK PVE_SQLSERVER
CONNECT TO EVP_PVE_USER
IDENTIFIED BY <PWD>
USING 'PVE_SQLSERVER';
Where EVP_PVE_USER is a user created on the SQL Server machine with rights to select from this view.I know it works because I get results with a sql command like :
select * from VW_PVE_DOCS_1_1@PVE_SQLSERVER;
I also created a view on the Oracle server that refines this information. It is defined as such :
CREATE OR REPLACE FORCE VIEW EVPDBA.VW_PVE_CONTRACTS_INALERT
(
DOCID,
EFFECTIVE_DATE,
EXPIRATION_TYPE,
ALERT_PERIOD_START,
ALERT_PERIOD_END,
ACRONYM,
[code]....
This view also works fine, i.e., I can select * from it from the sql command line.Now, the problem comes in when I need to run a procedure that processes this view every night and/or week.I have stripped everything out of this procedure that is not relevant, and it is defined as such for this forum :
CREATE OR REPLACE PROCEDURE EVPDBA.TESTME
is
tnum number := 0;
begin
select count(*) into tnum from VW_PVE_CONTRACTS_INALERT;
end;
/
If I execute this procedure from the sql command line, all is well.When I run it from a scheduler job, I get
ORA-01010: invalid OCI operation
ORA-02063: preceding line from PVE_SQLSERVER
ORA-06512: at "EVPDBA.TESTME", line 5
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_ISCHED", line 185
ORA-06512: at "SYS.DBMS_SCHEDULER", line 486
ORA-06512: at line 1
I am aware that DBMS_SCHEDULER performs a commit when scheduling a job, however, this is not scheduled from a trigger.I scoured the forums and have found a few things that seemed relevant, but not much. One had to do with the version of the JDBC driver between two Oracle databases, but I wonder if the age difference between Oracle 11 and SQL Server 2005 (Express) might be an issue. The fact that all command line select statements and running the procedure work fine implies to me that there is an additional issue raised due to the scheduler.
The other posts I found talked about performing a commit just before any select that ultimately pulls across a db link. I did this, and still no luck.One other useful fact - the job appeared to run succesfully at 5am, yet trying again at 8am threw the error, so it may be sporadic. (Although during regular daytime hours it is a very repeatable error).
I am looking into reformatting things to use the older DBMS_JOB, however, I really like the log history of job details and other functionality available with SCHEDULER.
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
-----------------------------------------
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 ....
SELECT b.KPCNO
,b.KPC_FULL_NAME
,min(c.time) in_time
FROM xxkpc_hr_personnel_v2 b
,xxkpc_fingerscan_data_v c
[code]........
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]....
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 ?
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?
how to combine 2 queries as attached in notepad file in order to get full output?
View 3 Replies View Relatedi 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].....
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
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]....
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
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.
Does trcsess combine various trace files in the order in which the queries were executed?
View 3 Replies View RelatedHow 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]....