SQL & PL/SQL :: SMSEngine - Result Mutation Error

Jul 15, 2010

I have SMSEngine which gets data from SMSEngine_tbl.

I want to insert record into SMSEngine_tbl with trigger fires on Attendance_tbl after insert(if column 4 of Attendace_tbl is "A")

SMSEngine_tbl have a column "Message" which needs(Select ) the calculation of total "A" absents of this student in particular month from Attendance_tbl(Same which is inserted)

Result mutation Error

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: ERROR At Line 1 - ORA-01489 - Result Of String Concatenation Is Too Long

Aug 13, 2013

I am getting below error when i try to update in my table

ERROR at line 1:
ORA-01489: result of string concatenation is too long

how to resolve this. Note :This is Update query not select query

View 2 Replies View Related

SQL & PL/SQL :: How To Get Result Without Minus

Nov 29, 2011

I got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.

And I need to get list of channels by contract which are an assigned to customers.

The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.

select o.channel_name from the_table_2 o
minus
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY

And the select under (after minus) gives a list of non assigned channels by contract_key:

select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY

how to rewrite a select to get required result?

View 2 Replies View Related

SQL & PL/SQL :: Getting Result From Three Tables

Sep 24, 2012

CREATE TABLE FLIGHTS(
FLIGHT_NUM NUMBER PRIMARY KEY
)

[Code]...

how to get the name of all passengers who have bookings in all flight_num?

o/p is 555

View 2 Replies View Related

SQL & PL/SQL :: Same Result When Use NOT IN And IN Condition?

Dec 14, 2011

I am using Oracle 8i.

Below is my query...

In the below 2 tables the records are like...

Table_1

IDNameCode
1AII
1AMNET
1AAXIS
1AUAT
2BMNET
2BUAT
2BUTC
3CMNET
3CII
3CUTC

Table_2

IDName
1AAA
2BBB
3CCC
4DDD

When i execute below query with using (NOT IN & IN) i am getting same result..

select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE not in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B
select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE in ('MNET', 'II', 'AXIS', 'UAT'))

Result:

IDName
1A
2B

When we execute the 1 query above it should not show 1 and 2 records...as i am using NOT IN condition.

View 5 Replies View Related

PL/SQL :: Query To Get Result Set

Feb 12, 2013

I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records

SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
     " ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
    LKP_Item.Group, LKP_Item.Modality Source
    FROM ((LKP_Product
[code]....

T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp

Just want to know if there is any possibility of running the query with all the Groups in one go

Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.

View 1 Replies View Related

PL/SQL :: How To Do The Partition On Result Using Sql Alone

Aug 27, 2012

How to do the partition on sql result result using query.

sample

BPREF_NO     BILL_MONTt AVG_IND     partition
Q12345     1/31/2009     2     part1
Q12345     2/28/2009     2     part1
Q12345     3/31/2009     2     part1
Q12345     4/30/2009     2     part1
Q12345     5/31/2009     2     part1
Q12345     6/30/2009     1     part1
Q12345     7/31/2009     2     part1
Q12345     9/30/2009     1     part2
Q12345     10/31/2009     2     part2
Q12345     11/30/2009     2     part2
Q12345     1/31/2010     1     part3
Q12345     2/28/2010     2     part3
Q12345     3/31/2010     2     part3
Q12345     11/30/2011     2     part4
Q12345     2/29/2012     2     part5
Q12345     3/31/2012     2     part5
Q12345     4/30/2012     2     part5
Q12345     5/31/2012     2     part5
Q12345     7/31/2012     2     part6

i want to create the partition column using query based on below logic

if bill_month is sequence then we have to create a partition and if any breaks comes we have to introduce the new partition.

just for example....
from 31-jan-2009 to 31-jul-2009 is called part1
from 30-aug-2009 to 30-nov-2009 is called part2
like wise...

is it possible to make the partition by query itself.

View 8 Replies View Related

XML DB :: Pivoting A Result Set

Oct 26, 2012

BANNER
--------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....

Middle EastThe table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?

View 11 Replies View Related

SQL & PL/SQL :: How To Display Result

Aug 7, 2012

I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.

declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
[code].....

View 1 Replies View Related

Concatenating Result Of Query

Aug 30, 2007

My scenario is something like this:

MyTable

Rownum Colval
1 23
2 34
3 45

I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?

View 2 Replies View Related

SQL & PL/SQL :: How To Display Result Horizontally

Feb 11, 2013

1.Requirement....

deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o

2.How to swap the column values ? ex. col2 into col1 and col1 into col2

3.What is the major Diff. b/w Right outer join & Left outer join ?

View 3 Replies View Related

SQL & PL/SQL :: Code For RESULT Column?

Aug 4, 2011

I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:

create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );

[code]...

The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.

What is the sql code for the RESULT-column?

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 :: How To Use Substr To Get Required Result

Jan 7, 2013

i have following table

CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)

[Code]...

Result is

ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS

I want a query which should return only Sahde # from above data result must be as

7921
C-8762
C-8762
C8676

View 9 Replies View Related

SQL & PL/SQL :: How To Join 2 Tables To Get Result

Sep 7, 2010

how to join 2 tables to get the result I need.I have following 2 tables (all dummy data)

Table 1

org_iddd_1dd_2dd_3
K87973789865879876432465
J87879454321981234723454

Table 2
acc_numacc_nameacc_typevol_sales
789865 Abcn185765
879876 defd494854
432465 efgg5948545
789865 hijh685765
879876 klmj794854
432465 nopl9948545

I want to join the above tables to get the following result

org_idacc_numvol_sales
K8797378986585765
K8797487987694854
K87975432465948545
J8787978986585765
J8788087987694854
J87881432465948545

I have tried writing union queries and exists clause in where and seem to get nowhere with this.

View -1 Replies View Related

SQL & PL/SQL :: OUTER Join That Gives No Result

Aug 17, 2012

i have the following problem

select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))

I should have a result with this request but there's nothing and the problem come from the fact that when the following condition is not met, there are no resulty even if there's an outer join

and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))

This condition makes Oracle ignores the outer join..I have tried to add this:

select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and (memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
or memo.dateCreation is null)

but there are too much records and the result is not consistent

View 7 Replies View Related

SQL & PL/SQL :: How To Get Rows N Times In A Result Set

Mar 9, 2010

How to get rows N times in a result set?

For obscure test purposes I need to modify an existing SQL query to emit the rows N times instead once. I'm aware of the possibility to "UNION ALL" the query with itself to get the all rows twice.

But as I require the resulting rows to be emitted around ten to hundred times this approach doesn't seem sensible to me. Not to speak of the missing possibility to parametrize the number of "repetitions".

View 2 Replies View Related

SQL & PL/SQL :: Result From Rows To Columns

Oct 29, 2013

How can i retrieve result of multiple rows into single row. Ex

select ename from emp;

Ename:
--------
ALLEN
WARD
JONES
MARTIN
BLAKE

[Code]...

Result should be like

ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER

View 8 Replies View Related

SQL & PL/SQL :: Query Result Into Excel Or CSV

Jun 15, 2011

I have a sql query which fetch the data from 4 different tables. I want to write the output of that query into a excel or a CSV file without using TOad and all. Let me know is it possible via creating function or procedure.

View 4 Replies View Related

SQL & PL/SQL :: Query Result In One Line

Aug 29, 2011

I have a select that return the query like this:

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br
434Ronaldo 11 21454117compras2@gralha.br
434Thiago 13 25418745thiago.alcarin@br.com.br

so,I need the query result in just one line...

EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI EMPCODEMPCONNOMEEMPCONFONEEMPCONEMAI
434Ronaldo 11 25411414compras@gralha.br 434Ronaldo 11 21454117compras2@gralha.br

I saw some exemples of "pivot query" but I still looking for a solution.

View 10 Replies View Related

SQL & PL/SQL :: Split Result Set Into Column

Aug 17, 2012

i have a table emp with three column

columns are (empid varchar,empnomini varchar,nominitype varchar), data in table like

empid empnomini nominitype
1 x B
1 y c
2 xx B
2 yyyy c

and i want data comes like

empid nominitype b nominitype c
1 x y
2 xx yyyy.

View 6 Replies View Related

SQL & PL/SQL :: Show Result Random

Mar 24, 2010

I run follwing query.

select * from crp_00_08

it shows result like

BAS_CODBAS_DES

1MAIN GATE (FRONT)
2NEAR MILL # 3, 3-1
3NEAR MILL # 3, 3-2
4NEAR MILL # 3, 3-3

[Code]...

i want that when i run above query every time bas_cod column should start with difference value. now it is starting from 1, i need when i run next time it should start with another number except 1,

i mean whenever i run above query,order of bas_cod must be change from last.

how can i do it.

View 12 Replies View Related

SQL & PL/SQL :: Different Result On Every Execution Of Query?

Jan 13, 2011

when I am executing the below query getting diffrent count every time and not able to guess what is happening.

SELECT
count(1)
FROM table1 where table1.LAST_UPDATE_DATE >= current_date - interval '9' day

View 6 Replies View Related

SQL & PL/SQL :: Use DECODE Function For Getting Result

Aug 28, 2012

I have a table like Create temptbl (Client_status CHAR(1), Rollbak NUMERIC(3), count_org NUMERIC(3), count_member NUMERIC(3))

With Values
Cliet_Status Rollbak count_org count_member
------ ------- --------- ------------
Z 3 7 5
P 39 5 8
R 49 1 6
S 5 6 4

I need to use DECODE function for getting the result like

IF client_status NOT IN('Z', 'P')
THEN rollbak in(7, 39,49,10)and count_org is not null and count_member is not null.
ELSE NULL

View 3 Replies View Related

SQL & PL/SQL :: Result Be Only One Record With Max LICENSE_EXPIRE_DATE

Apr 13, 2010

i have this query

SELECT V.VEHICLE_CODE VEHICLE_CODE,MAX(L.LICENSE_EXPIRE_DATE) L_EXP_DATE
,L.LICENSE_PLACE
FROM VEHICLES V,VEHICLE_LICENSE L
WHERE V.VEHICLE_CODE = L.VEHICLE_CODE
and V.VEHICLE_CODE='14'
GROUP BY V.VEHICLE_CODE
,L.LICENSE_PLACE

that should result the max "LICENSE_EXPIRE_DATE" for this "VEHICLE_CODE" it gives me two record

VEHICLE_CODEL_EXP_DATELICENSE_PLACE
1431/08/2004LIBYA
1413/04/2010EGYPT

i want the result be only one record with the max "LICENSE_EXPIRE_DATE".i want the result be like that

VEHICLE_CODEL_EXP_DATELICENSE_PLACE
1413/04/2010EGYPT

also in the same time i want to select(show) the "LICENSE_PLACE".

View 9 Replies View Related

SQL & PL/SQL :: Query Yields No Result

Mar 20, 2012

in my table there is a row as below.

CITY_ICITY_NST_PROV_CPSTL_CUPDT_USER_IUPDT_TSUPDT_PGM_I
342DC79a03654822-FEB-12 02.02.13.410000000 PMOMR

when i use below below query, there are no values returned.

select city_i from city_e where (trim(city_n))= 'DC' and st_prov_c=79 but while i use below query, im bale to get the result

select city_i from city_e where city_n='DC' and st_prov_c=79

View 5 Replies View Related

SQL & PL/SQL :: Return A Result Set From One Database To Another?

Sep 4, 2012

Is there any way to return a result set (using a Database Link) from one Database to another Database? Database A needs to call Database B and Database B needs to return a result set via a stored package/function. The stored procedure/function on Database B also accepts 1 input parameter. Something like "select test.test('some input parameter')@database_b from dual'.

View 39 Replies View Related

Result Of Expensive Query

Jul 11, 2012

I need a a query which will give the result of most expensive query i.e. which is taking most of I/O /memory.

View 3 Replies View Related

PL/SQL :: String And Result Number

Jul 9, 2012

Im trying to generate this link on my result [URL].....

materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..

View 14 Replies View Related

PL/SQL :: How To Add Or Merge 2 Rows Together In Result Set

Sep 10, 2013

What is the easiest way to add 2 rows together? I need this results set below as is except I need rows with activity codes 0 and 30 to be merged together into Activity Code 0. So, Regular hours would be 7466.75. Do I need to do a Union or is there a more elegant solution?

My current sql results set looks something like this: 

Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT
0 0: Business As Usual 7409.25 305.5 1603.25 Information Technology
10 10: Special Projects 190.75 17 0 Information Technology
20 20: Strategic Initiatives 1067 52 0 Information Technology
30 0: Business As Usual 57.5 0 0 Information Technology

and I want my new results to look like this:

Activity Code Activity Code Desc REGULAR OT NONPROD DEPARTMENT
0 0: Business As Usual 7466.75 305.5 1603.25 Information Technology
10 10: Special Projects 190.75 17 0 Information Technology
20 20: Strategic Initiatives 1067 52 0 Information Technology

View 7 Replies View Related







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