Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
create table try1
(id_number varchar2(10),
item1 varchar2(10),
item2 number(10),
item3 varchar2(10))
[code]....
Table's data:
ID_NUMBERITEM1ITEM2ITEM3ROWID
1asasas12dadasdaAAA9/BAAOAAA0JtAAA
22dadad231fsfsfAAA9/BAAOAAA0JtAAB
I have constructed a cross join query, with the test case below
create table ajit_sites (
site_id char(1));
insert into ajit_sites values ('A');
insert into ajit_sites values ('B');
insert into ajit_sites values ('C');
COMMIT;
sql below is constructed to display combination of all sites (cross-join), it also removes records where "origin" is the same with "dest"
select
a.site_id origin, b.site_id dest
from
(select site_id from ajit_sites) a,
(select site_id from ajit_sites) b
where
a.site_id <> b.site_id b
Is there any way i could remove records with the behavior below
Origin , Dest
A , B
B , A
For instance from the example above, i want to only retain one of the records since record (A, B) or record (B, A) means the same.
I have a editing database with an eversions table:
NAME OWNER
---------------------- --------------------------------
WR5936_DN6676 FRED
WR6739_DN7507 FRED
WR12744_DN13994 FRED
WR6739_DN7511 BARNEY
WR6801_DN7513 BARNEY
I have a process database with a pversions table:
SOID
----------------
5936
6739
12744
6739
I need to select from the editing.eversions table all the records that do not have a matching record in process.pversion. The eversion table is text, and has some additional crap surrounding the characters I want to use for creating a join.
Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)
I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation URL....I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed.
WITH tmptab(empId, mgrId, lvl) AS
(
SELECT employee_id, manager_id, 0 lvl
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)
[code]....
107 rows selected.
SQL> However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.That is, if instead of writing
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees, tmptab
WHERE (manager_id = empId)I write
. . .
UNION ALL
SELECT employee_id, manager_id, lvl+1
FROM employees CROSS JOIN tmptab
WHERE (manager_id = empId)I get the following error message
ERROR at line 4: ORA-32044: cycle detected while executing recursive WITH query
I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example
SQL> WITH tmptab1 AS
2 (
3 SELECT 'a1' AS colval FROM DUAL UNION ALL
4 SELECT 'a2' AS colval FROM DUAL UNION ALL
5 SELECT 'a3' AS colval FROM DUAL
6 ),
[code]....
SQL> So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.
what are the collections available in Oracle Plsql, what are concepts of collection.
How to Transpose a Table from rows to columns or columns into rows.
DDL and DML concepts.
What is the concepts of statistics in Oracle Plsql.
I need to transpose the following table columns to rows and rows to columns...Im not quite sure how to acheive this...I have the following table with fixed number of columns and dynamic number of rows based on date filter in query
MONTH_YEAR RMS RMS_OCC TTL_RMS
---------------------------------------
SEPTEMBER 200917790017790
OCTOBER 2009183831278818347
NOVEMBER 2009177901460517762
and I need to display this as
COL1 SEPTEMBER 2009 OCTOBER 2009 NOVEMBER 2009
--------------------------------------------------------------
RMS 17790 18383 17790
RMS_OCC 0 12788 14605
TTL_RMS 17790 18347 17762
I want to join two columns, one with a Long and another with a Varchar2.
How to achieve that?
Oracle v11.1 Standard edition. I am trying to use TRIM function around the columns which are used for outer join and it's not working. However, if I use ANSI syntax, it works. Please check the SQL below.Is there any way, I can use old syntax and TRIM and get the outer join to work?I know, I can fix/trim the data in the tables, but for some reason (need to keep the data exactly same as we received it), that is not possible here.
SQL>SQL> desc tabaName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> desc tabbName Null?
Type----------------------------------------- -------- ----------------------------COL1
VARCHAR2(20)SQL> select a.col1, b.col1 from taba a, tabb b where a.col1 = b.col1 ;
no rows selectedSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD
DSQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1) ;
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) (+)= trim(b.col1)
*ERROR at line 1:ORA-00920: invalid relational operator
SQL> select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1) (+);
select a.col1, b.col1 from taba a, tabb b where trim(a.col1) = trim(b.col1)
*ERROR at line 1:ORA-00933: SQL command not properly ended
SQL> select a.col1, b.col1 from taba a left outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD DB
SQL> select a.col1, b.col1 from taba a right outer join tabb b on trim(a.col1) = trim(b.col1) ;
COL1 COL1
-------------------- --------------------
A AC CD D E
As the title of this topic illustrates, i'm having trouble dumping relevant data from columns of different tables. I am using isql*plus. I have three tables appropriatly related. A 'course' table, 'student' and 'next_of_kin' tables. I have many students enrolled on various courses but only a hanfull of courses offer the module option 'Database Systems'. I have no 'module' table but i know the three course names which provide the module option. I intend on producing a report hich lists all students enrolled on the courses which provid the module option 'Database Systems'.
I have attempted the report but i keep getting a 'cartesian product' displaying all next_of_kin names instead of the appropriate. Also i am struggling to come up with the right WHERE statement to depict only the three courses which provide the module option 'Database Systems' as defined by 'courseNo' in both 'course' and 'student' tables.
Here is the most recent attempt:
--set echo off
--set pagesize 24
--set feedback off
--set linesize 78
col A format 99999999 heading 'Student No'
[code]...
The view below creates, however displays duplicate rows. Why is this may I ask?
CREATE OR REPLACE VIEW customer_order_vw
AS
SELECT
a.customer_id,
[Code]....
select rl.org_rollup_skey from (select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486')) p
INNER JOIN IA.DIM_ORG_HIER h
ON p.fc_org_skey = h.desc_org_skey
inner join IA.FCT_FCST_SLS_ORG_ROLLUP rl
on h.GPRNT_ORG_SKEY = rl.org_rollup_skey
Above join is taking is running forever even as subquery
(select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486'))
returns no rows and this subquery give result in 10 seconds according to me Full query should not take more tha 20 secs.
I have a select statement that selects all columns from the join of 3 oracle views. I would like to change it to select only the distinct rows, not sure how to code this. Here is my sql statement:
select *
from myschema.view_1 acct
Left JOIN myschema.view_2 freq
[Code].....
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
How Can I delete the returned two rows?
1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....
I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
Where filter middle_rows save before join and grop by operation?
It is save rows in PGA Private SQL Area or save blocks in SGA databuffer?
Sample data
col1 col2 col3
1 A someval1
2 A someval2
3 A someval3
2 B someval4
3 B someval5
In col1 there will be always 1 or 2 or 3 value not more than 3 I am using oracle 10g.Want the following output in a single query with using user defined function or stored proc
OUTPUT
newcol1 newcol2 newcol3
someval1 someval2 someval3
null someval4 someval5
My DB version is
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME)
WHEN 'ALERT' THEN
SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A
WHERE A.ALERT_ID = IN_PARENT_NODE_ID;
INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
[code]....
Getting error ORA-00932: inconsistent datatypes: expected NUMBER got CHAR Source row:
NOTE_IDCONTRACT_GRANT_IDPROSPECT_IDPROGRAM_CODE
1 1 1 786
2 2 2 786
Program:
SELECT
CASE
WHEN "PIVOT_ROW_GENERATOR"."ID" = 0 THEN
"PIVOT_SOURCE"."ID_NUMBER"
WHEN "PIVOT_ROW_GENERATOR"."ID" = 1 THEN
"PIVOT_SOURCE"."ID_NUMBER"
[code].........
Note: This is not a homework assignment, but rather, a technical bottleneck at work.
My dilemma is such: let's say that I have 1 teacher in an entity, and this teacher has 3 students in an associative entity. So if you did a select T.teacher, S.student from TEACHER T, STUDENT S, where T.teacher_id = S.teacher_id, you would get 3 rows:
Teacher 1, Student 1
Teacher 1, Student 2
Teacher 1, Student 3
How I would like to display it, is in 1 row:
Teacher 1, student 1/student 2/student 3
Is there a way to write a SQL to achieve the above?
I have this following data in a single table
Student
Semester
Subject
Marks
and I want to achieve the following.
I am asked to write a query with the students as Rows with thier subjects and marks as per thier semester which is the columns.
new to this type of queries...This is somewhat like pivot..
Those who have not appeared for a semester should be null just exactly as shown above.Is it posible ?
how to convert rows to columns ?
View 1 Replies View RelatedBelow is the schema of a table:
TableName : PropertyValue
Columns: PropertyID Number
Value varchar
ValueID Number
Phone Number
Requirement: Create a view based on the table"PropertValue'. There could be 4 different PropertyIDs for each phone.
e.g.
PropertyID Value ValueID Phone
1 'xyz' null 1234
2 null 11 1234
3 null 12 1234
4 null 13 1234
1 'pqr' null 5678
2 null 14 5678
3 null 15 5678
4 null 16 5678
Required View:
Phone Attrib1 Attrib2 Attrib3 Attrib4
1234 'xyz' 11 12 13
5678 'pqr' 14 15 16
I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.
select case when PropertyID=1 then VALUE end as Attrib1,
case when PropertyID=2 then ValueID end as Attrib2,
case when PropertyID=3 then ValueID end as Attrib3
case when PropertyID=4 then ValueID end as Attrib4
from (
select Phone, PropertyID, ValueID,Value
from PropertyValue
group by Phone, PropertyID, ValueID,Value
)
Table T having columns Name, TCode, TCount...Sample Data:
Name, TCode, TCount
Joe,X,5
Bloggs,X,7
Joe,Y,9
Smith,Y,2
Bloggs,Z,3
This data to be represented as:
Name,X,Y,Z
Joe,5,9,0
Bloggs,7,0,3
Smith,0,2,0
i have a table with this data :
id name
1 a
2 b
3 c
4 d
and i want the o/p like this,
col1 col2 col3 col4 col5
id 1 2 3 4
name a b c d
means i want to convert my columns into rows.
I have a table that has rows like below in one column. I want to parse the rows in different columns.
Column12
abc|yed| qas ert | hub cap | jak
agh|
bef|rfd
som|gfr|lup
I need to parse the above in different columns by splitting at the pipe.
so it will show something like this
col1 col2 col3 col4 col5
abc yed qas ert hub cap jak
agh
bef rfd
som gfr lup
is it possible to do it in PL/sql.
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
I have a table with the following structure and data.
create table testing_1 (column_1 varchar2(10),
heading_1 number ,
value_1 number);
insert into testing_1 (COLUMN_1, HEADING_1, VALUE_1)
values ('First', 10, 99);
[code].......
COLUMN_1 HEADING_1 VALUE_1
First 10 99
Second 50 50
Third 10 80
First 50 77
Third 50 70
First 100 55
Third 100 60
And i need the output like below. (i.e. distinct heading_1 (10, 50, 100) will become our new column headings and corresponding values for these headings will be filled in all the rows)
10 50 100
First 99
Second 50
Third 80
First 77
Third 70
First 55
Third 60
Need to convert columns into rows like expected
service_key consumer_key the_key fin_score_type granu rno
20100201 1 p_1 100MONTH1
20100201 1 p_0 100MONTH1
20100201 1 d_6 100MONTH1
20100201 1 t_2 200MONTH1
20100201 1 d_5 100MONTH1
But it should display like below,All the the_key types as differnet colmns instead of rows,Since need to insert those values in different tables.
expected o/p consumer_key p_code p_val d_code d_val t_code t_values granula
service_key
20100201 1 p_0 100 d_6 100 t_2 200 MONTH1
20100201 1 p_1 d_5 100 MONTH1
I have one issue with the converting columns into rows.
Employee table has manager , additional manager and some other columns. If the employee has additional manager, then i need those employee record with 2 rows : 1st row - manager with other data , 2nd row - additonal manager with other data .
Orginal data is like this:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ADDTIONAL_MGR ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -------------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7698
[code]....
I have tried this , using UNION ALL method, But it is taking more time.
SQL> ED
Wrote file afiedt.buf
1 SELECT DECODE (DUMMYTAB.NUM,'1',MGR, '2',addtional_mgr) MANAGER, empno, ename, job, hiredate, sal, comm, deptno
2 FROM emp E, (SELECT '1' NUM FROM DUAL UNION ALL SELECT '2' NUM FROM DUAL) DUMMYTAB
3* WHERE addtional_mgr IS NOT NULL
SQL> /