Unable To Send Grouped Value To Subquery
Mar 9, 2010
resolving this problem the query is
SELECT DAY_NBR ||' '||HOUR_NBR ||' '||MINUTE_NBR AS "@TIMEPOINT",
RTRIM ( xmlagg (xmlelement (c, procedure_nm || ', ')).extract ('//text()'), ', ' ) AS "@LABEL",
RTRIM ( xmlagg (xmlelement (c, procedure_id || ', ')).extract ('//text()'), ', ' ) AS "@PROCEDURE_ID",
cursor
(SELECT SUBJECT_NBR AS "@LABEL",TO_CHAR (PSUB.procedure_tm,'HH24:MI') AS "@TIME",
PROCEDURE_ID AS "@PROCEDURE_ID", PROCEDURE_SUB_ID AS "@PROCEDURE_SUB_ID"
FROM PROCEDURE_SUBJECT_TIME PSUB WHERE PSUB.ACTIVE_FLG = 1
AND PSUB.PROCEDURE_ID in pr.PROCEDURE_ID
ORDER BY PSUB.procedure_tm,PROCEDURE_ID
)AS VOLUNTEER
FROM SCHEDULE_PROCEDURE PR
WHERE active_flg = 1 and SCHEDULE_ID = 200900195269 group BY day_nbr,hour_nbr,minute_nbr,procedure_nm;
and the error getting is ORA-00979: not a GROUP BY expression
View 2 Replies
ADVERTISEMENT
Jun 23, 2010
I have ORacle XE installed on my machine and everything works fine. I can connect to it using forms, run my application etc. Now, I wanted to write a proc which can send email from Oracle XE.So after investigating, I think UTL_SMTP and UTL_MAIL are my options. For which I need to enable the ut_mail package and one more which I have done. But everytime I run a package, I get the error
DECLARE
v_From VARCHAR2(80) := 'oracle@mycompany.com';
v_Recipient VARCHAR2(80) := 'test@mycompany.com';
v_Subject VARCHAR2(80) := 'test subject';
v_Mail_Host VARCHAR2(30) := 'mail.mycompany.com';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
[code].....
Ora 29278 SMTP transient error.
View 13 Replies
View Related
Mar 6, 2013
I am using utl_smtp to send emails. I am getting the below error message.
Error in XX(Package name) ORA-29279: SMTP permanent error: 501 5.5.2 Syntax error in parameters scanning "TO" at - 5150
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 5200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7200
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 6000
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 7250
Error in XX(Package name) ORA-29277: invalid SMTP operation at - 8450.
I have checked the mail id in the "TO" list and found them to be valid. Is there could be any other reason? I have run this package in test instance and it is working fine. I am able to receive mails. In the test instance, I have put my mail id in the to list.
View 1 Replies
View Related
Dec 1, 2012
how to send email (PL/sql Procedure) to private domain through google mail.
View 2 Replies
View Related
Sep 14, 2010
I am sending mail thru UTL_SMTP. But i am unable to send the CLOB in it.If i break the CLOB into varchar2 then also it doesn't work.
Variables:-
vBuffer VARCHAR2 (4000);
l_amount BINARY_INTEGER := 4000;
l_pos PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
[code].....
While using the write_data procedure it is giving error as "Invalid OPeration"...how we can send the mail with CLOB.
View 1 Replies
View Related
Feb 23, 2007
I am succeeded to send mail (through my comapany mail server) using following pl/sql. But, i am unable to send mails using the same coding, when i connected to smtp.gmail.com. i received the following error :
ERROR at line 1:
ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first
y78sm463148pyg
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
[code]...
Then I received the following error message:
ERROR at line 1:
ORA-29278: SMTP transient error: 454 TLS not available due to temporary reason
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 96
[code]...
my demo_mail package body coded as
FUNCTION begin_session RETURN utl_smtp.connection IS
conn utl_smtp.connection;
BEGIN
-- open SMTP connection
[code]...
View 2 Replies
View Related
Oct 18, 2010
I have a sql like this
SELECT RO_NO, HANDLED_BY
FROM MSS_VEH_INPUT
GROUP BY HANDLED_BY, RO_NO
I want the output like this with row num grouped by handled_by,ro_no
SNO HANDLED_BY RO_NO
1 KUMAR 12
2 KUMAR 13
3 KUMAR 14
1 LEE 15
2 LEE 16
1 MARVIN 17
2 MARVIN 18
So from this I can know Kumar how many ro,lee how many ro and marvin ro.?
View 6 Replies
View Related
Sep 5, 2013
the point is that I have a table with two columns and I am trying to find and easy way to show grouped data.
CREATE TABLE TEST_DATA(C1 VARCHAR2(1), C2 VARCHAR2(2));
INSERT INTO TEST_DATA VALUES('1','A1');
INSERT INTO TEST_DATA VALUES('1','A2');
INSERT INTO TEST_DATA VALUES('1','A3');
[Code] .....
With the sample data there are 4 groups based in c1 column:
1 : A1,A2,A3
2 : A1,A2,A4
3 : A1,A2,A3
4 : A1,A2,A4
5 : A6,A7
I'm trying to get and output like :
A1,A2,A3 : 1,3
A1,A2,A4 : 2,4
I was trying something like :
select t1.c1 as gr1, t2.c1 as gr2, t1.c2
from test_data t1,test_data t2
where t1.c1<>t2.c1 and t1.c2=t2.c2
and
(select count(*) from test_data t3 where t3.c1=t1.c1)=
(select count(*) from test_data t4 where t4.c1=t2.c1)
order by 1 asc, 2 asc
but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.
View 4 Replies
View Related
May 27, 2013
I have the following table structure with values
CREATE TABLE DUMMY
(
SR_NUMBER VARCHAR2(100 CHAR),
ASSIGNMENT_GROUP VARCHAR2(100 CHAR),
REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
CREATED DATE,
[code]...
?I have the following requirement, the output should be:
Ticket count (sr_number)
% of tickets inside DL
Number of tickets inside DL
Average cycle time (cycle time = closed date - created date)
Total cycle time (cycle time = closed date - created date)
Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
View 10 Replies
View Related
Nov 5, 2009
I have a table that cannot be changed with a field called transaction_reference in the transactions table. This field contains any number of some values in a look-up table called codes.
The table codes contains 'AA', 'BB', 'CC'.
A typical transaction_reference field may look like 'CC BB' or 'AA' or 'AA CC' or 'AA CC BB' - any number, any order.My goal is to get a count of records grouped by another field from the transactions table.
Transactions table example:
transaction_id | transaction_reference | family
---------------------------------------------
1 | AA BB | foo
2 | BB CC | bar
3 | BB | hello
4 | AA CC BB | foo
5 | BB AA | bar
So the results should look like:
family | code | count
foo | AA | 2
foo | BB | 2
foo | CC | 1
bar | AA | 1
bar | BB | 2
bar | CC | 1
hello | AA | 0
hello | BB | 1
hello | CC | 0
If the counts of 0 (like the third to last and last line above) don't show up I'm ok with that.I put together an explode function like this one here but I'm really not sure where to go from here. I can split the transaction_reference, but I'm not sure what to compare it to or how.
I realize that a field in the transactions table for AA, BB, and CC would be ideal, but I can't do that... the powers that be won't let me change the table.
for each exploded segment from transaction_reference
look for it in the codes table
if it exists, add 1 to the count
View 7 Replies
View Related
Feb 17, 2011
My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.
SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......
What will be the case when I need to concatenate for more number of values.
like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.
View 10 Replies
View Related
Oct 13, 2012
Using Oracle 11g...We have a table in our database of data with the following information:
MASTER_RECORD,
MEMBER_RECORD,
BUSINESS_UNIT,
GENDER,
DOB (date),
age [at time of month_record],
MONTH_RECORD (date) [31-MON-YEAR for recorded active month]
The table has ~55 million records. Existing index is only on MASTER_RECORD.There is now a need to create a view which is an aggregate count of member records, grouped by business_unit,gender, age per year. eg:
business_unit, gender, age, month_record, num_of_members -> for every combination
unit5, F, 25, 31-JUN-2011, 622
unit3, M, 18, 31-MAY-2011, 573
The view can be created now, but, is not fast enough to be reasonably considered a view. This table is re-created every month from a procedure, so there is flexibility on how it is created. Use interval partitioning by year( something I have not experienced using), create an index on the month_record,then create view.
View 2 Replies
View Related
Dec 14, 2011
Im trying to list the products list of a client grouped by type of the product. Ex:
product type
prod.A acid
prod.B flavour
prod.C acid
prod.D cleaner
prod.E flavour
I want to list something as:
Acid
Prod.A
Prod.C
Cleaner
prod.D
Flavour
prod.B
prod.E
View 1 Replies
View Related
Jun 10, 2010
I was reading a tutorial for analytical function and i found something like this
sum(princial) keep(dense_rank first order by d_date) over partition by (userid, alias, sec_id, flow, p_date)
How to translate this into simple queries / subquery? i am aware that analytical function are faster but i would like to know how this can translate to using query without analytical function.
View 12 Replies
View Related
Nov 6, 2010
I have a database containing the following after entering the following sql command
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
TITLES.ROYALTY_RATE AS ROYALTLY_RATE,
AUTHOR_TITLES.ROYALTY_SHARE AS ROYALTY_SHARE,
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T01 12446,34 ,05 1 Sarah Buchman
T02 190841,7 ,06 1 Sarah Buchman
T03 1025396,65 ,07 1 Christian Kells
T04 168882,99 ,08 ,6 Hallie Hull
T04 168882,99 ,08 ,4 Klee Hull
T05 1400008 ,09 1 Klee Hull
T06 225834 ,08 1 Wendy Heydemark
T07 35929790 ,11 ,5 Wendy Heydemark
T07 35929790 ,11 ,5 Klee Hull
T08 40950 ,04 1 Kellsey
T09 69750 ,05 1 Kellsey
TIT (PRICE*SALES) ROYALTLY_RATE ROYALTY_SHARE FNAME LNAME
--- ------------- ------------- ------------- --------------- ---------------
T10 1 Wendy Heydemark
T11 752042,77 ,07 ,3 Hallie Hull
T11 752042,77 ,07 ,3 Klee Hull
T11 752042,77 ,07 ,4 Kellsey
T12 1299012,99 ,09 1 Wendy Heydemark
T13 313905,33 ,06 1 Sarah Buchman
17 rows selected.
What I need to do is create a subquery and use Aggregation to list the author receiving the greatest royalties on revenue. so i used the command to get the royalties
SELECT TITLES.TITLE_ID AS TITLE_ID, (PRICE * SALES),
AUTHORS.FNAME AS FNAME, AUTHORS.LNAME AS LNAME,
((PRICE * SALES) * TITLES.ROYALTY_RATE * AUTHOR_TITLES.ROYALTY_SHARE) AS ROYALTIES
FROM TITLES, AUTHOR_TITLES, AUTHORS
WHERE TITLES.TITLE_ID = AUTHOR_TITLES.TITLE_ID
AND AUTHORS.AU_ID = AUTHOR_TITLES.AU_ID
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T01 12446,34 Sarah Buchman 622,317
T02 190841,7 Sarah Buchman 11450,502
T03 1025396,65 Christian Kells 71777,7655
T04 168882,99 Hallie Hull 8106,38352
T04 168882,99 Klee Hull 5404,25568
T05 1400008 Klee Hull 126000,72
T06 225834 Wendy Heydemark 18066,72
T07 35929790 Wendy Heydemark 1976138,45
T07 35929790 Klee Hull 1976138,45
T08 40950 Kellsey 1638
T09 69750 Kellsey 3487,5
TIT (PRICE*SALES) FNAME LNAME ROYALTIES
--- ------------- --------------- --------------- ----------
T10 Wendy Heydemark
T11 752042,77 Hallie Hull 15792,8982
T11 752042,77 Klee Hull 15792,8982
T11 752042,77 Kellsey 21057,1976
T12 1299012,99 Wendy Heydemark 116911,169
T13 313905,33 Sarah Buchman 18834,3198
17 rows selected.
So how do I add up the royalties values associated with each author and find the max? for example I add klee hulls's royalties from each book and get 2,123,336.32(doing it by hand on calculator) what is the sql to find the max royalties for each author? P.S the answer should be KLEE HULL with 2,123,336.32
View 6 Replies
View Related
Dec 6, 2012
I am returning the ORA-01427 after running the query below. why I am returning the error and how to address it.
select b.value , b.name, p.value ......
(case when p.value <> 'G2' then null else (select c.oldvalue from ad_changelog c
where c.record_id = b.c_bpartner_id and c.ad_table_id = 291
and c.ad_column_id = 4216 ) end) as oldtradeName
from c_bpartner b, zz_receipt r, zz_recp_alloc a, m_product p, ad_user us
where a.c_bpartner_id = b.c_bpartner_id
and a.zz_receipt_id = r.zz_receipt_id
and us.ad_user_id = r.createdby
and p.m_product_id = a.m_product_id
View 2 Replies
View Related
Dec 7, 2009
Just trying to update a table in which the sales amount is inserted when the sales amount is null.
I have
UPDATE ph2_customer_temp
SET sales_amount = (
SELECT sl.sales_amount
FROM PH2_CUSTOMER_TEMP pct
join
sales_limit sl
on substr(pct.site_code,1,2) = sl.state
where pct.credit_limit is null )
View 2 Replies
View Related
Oct 1, 2007
restricting a subquery's results to the first record. If I use the following:
SELECT WU.DISCREP, M.PART, M.ETA
FROM DB.MICAP M
LEFT JOIN DB.WRITEUPS WU ON M.WRITEUPID=WU.WRITEUPID
LEFT JOIN DB.WUC WUC ON WU.WUCID=WUC.WUCID
WHERE (WU.AIRCRAFTID=205) AND (WU.CORRECTED=0)
ORDER BY WU.PACER DESC, WUC.WUCCODE
I get
-------------------------------------------------------
#5 GEN KICKED OFFLINECABLE02-MAR-08
#5 IDG HARNESS REQUIRES WIREWIRE24-MAY-08
#1 GEN WENT OFFLINECABLE02-MAR-08
AERIAL REFUEL DOORS W/N OPENLINEAR DEL29-SEP-07
AERIAL REFUEL DOORS W/N OPENCHECK VLV30-SEP-07
WEATHER RADAR TILT KNOB HASRADAR COMP05-OCT-07
But I need the highlighted line eliminated. I've tried DISTINCT subqueries in the WHERE M.WRITEUPID IN (SELECT DISTINCT... various experiments with joins, etc.
View 4 Replies
View Related
Jan 10, 2012
I can't seem to wrap my head around this problem I'm having with a query. I need to update all rows in my ps_ntsr_gf_stufile tables with the concatenated values from the ps_classes_tbl table where a.CLASS_NBR = b.CLASS_NBR. I tried to limit it to emplid from the ps_stdnt_enrl table but no luck.
UPDATE ps_ntsr_gf_stufile a
SET a.CLASS_NAME = (SELECT CONCAT('SUBJECT', 'CATALOG_NBR')
FROM PS_CLASS_TBL b
WHERE a.CLASS_NBR = b.CLASS_NBR
AND a.STRM = '1118'
AND a.INSTITUTION = 'NT752')
WHERE a.EMPLID IN (SELECT distinct EMPLID FROM PS_STDNT_ENRL);
I'm still getting the ORA-01427 error.
View 1 Replies
View Related
Jan 8, 2013
I'm facing an issue "ORA-00904". Below is the test case. Both the queries are different. I'm only focussed to find out the reason.
CREATE TABLE ACCT
(
ACCTNBR NUMBER(10)
) ;
[Code].....
In first query I'm able to refer to table alias A but in second query I'm not able to refer it. The only difference is that in second query I've not used outer table at second level. Is it the desired behaviour?
View 8 Replies
View Related
Mar 20, 2012
/* (SELECT A.TOTAL_ALLOCATED_AMT FROM CLS_ALLOCATION_HDR@LAMS_PROD A WHERE A.APPROVAL_DATE BETWEEN BCTH.TRAN_FROM_DATE AND BCTH.TRAN_TO_DATE
AND A.CUSTOMER_ID=BCTH.CUSTOMER_ID AND ALLOCATION_ID IN (SELECT ALLOCATION_ID FROM CLS_ALLOCATION_DTL@LAMS_PROD B WHERE ENTITY_TYPE='REC' AND
B.ALLOCATION_ID=A.ALLOCATION_ID AND ENTITY_ID IN (SELECT RECEIPT_NO FROM CLS_RECEIPT_DTL@LAMS_PROD C WHERE B.ENTITY_ID=C.RECEIPT_NO AND
RECEIPT_STATUS='A'))AND ALLOCATION_STATUS='A')*/
when i am trying to run this query i am getting an error'single row subquery returns more than one row'.
View 2 Replies
View Related
Jul 30, 2010
My problem : I Wrote a package(a function), this function just return a varchar2 , but in this function I wrote a cursor , when I call this function in this manner :
SELECT secu.u_menu_id,
secu.menu_id,
menu.prompt,
secu.u_type,
secu.u_id,
[code]....
SQL return : Ora-01427 Single-row subquery returns more than one row
Yes , in this function it has cursor that more than one row. But I let this function return varchar2 ,
View 13 Replies
View Related
Apr 28, 2012
I have written the below sql select loc,(select ename from emp where emp.deptno = dept.deptno) from dept
It results in the below error.
[Error] Execution (1: 13): ORA-01427: single-row subquery returns more than one row
I have modified the SQL and got the required output
select em.ename,dep.loc
from
(select loc,deptno from dept) dep ,
(select ename,deptno from emp) em
where dep.deptno=em.deptno(+)
I have written the below sql to fetch all loc for emp which got executed
SELECT ENAME, (SELECT LOC FROM DEPT WHERE DEPT.DEPTNO=EMP.DEPTNO) LOC
FROM EMP
But as i need all locations irrespective of any emp in the locaton so i tried to put emp side (+) which resulted in error.
View 3 Replies
View Related
Jun 5, 2010
I read in a book that you can't use subquery in an insert statement . E.g:
1)insert into dates (date_col) values (select sysdate fom dual) but when i tried using subquery like this:
2)insert into regions values ((select max(region_id)+1 from regions), 'Oce');
This query worked but 1st query didnt.From my assumptions if we try inserting values in table with the subqueries for a particular column as in 1st query , it will throw error but not while inserting values in all columns as in 2nd query.
View 5 Replies
View Related
Mar 31, 2010
The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:
SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid
But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.
SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)
Is there any way to select the DISTINCT taskids ordered as per requirements?
View 13 Replies
View Related
Jun 5, 2011
A SINGLE SQL QUERY...
i have tried with no luck!!
consider this sample table:
NAME GENDER MARRIED
---------------------------
AAA M Y
BBB M N
CCC M Y
DDD F Y
EEE F Y
FFF M N
OUTPUT SHOULD BE:
GENDER TOTAL MARRIED
------------------------
MALE 4 2
FEMALE 2 2
TOTAL 6 4
View 24 Replies
View Related
Feb 10, 2013
Managed to confuse myself significantly. I essentially want to write a query to determine when two counts are the same using a subquery.
Eg:
R(x,y)
Select count(x)
from R
group by x;
Then I want to run another query to determine which x's have the same count value and output these corresponding x's.
View 6 Replies
View Related
Oct 18, 2012
Can we use a subquery after the update keyword??
For example
UPDATE ( Select col1 from test)
set col1='x';
In short can we replace the table name after an update and use a select statement instead??
View 3 Replies
View Related
Feb 7, 2013
I want to display first joined and last joined employee name(first_name) in each department like this
department_name | first joined employee | last joined employee
I think we have to use joins and sub query.
employee table attributes -
EMPLOYEE_ID
FIRST_NAME
LAST_NAME
EMAIL
PHONE_NUMBER
HIRE_DATE
JOB_ID
[Code]....
View 1 Replies
View Related
Oct 13, 2009
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)=
(SELECT B.id,
B.fullname,
MIN(B.nm_rankval)
FROM B,
A
WHERE A.id = B.id
AND A.name <> B.fullname
AND B.nametyp = 'ON'
GROUP BY B.id,
B.fullname)
;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
View 2 Replies
View Related