SQL & PL/SQL :: How To Join Two Columns Of Different Datatypes
May 3, 2012I want to join two columns, one with a Long and another with a Varchar2.
How to achieve that?
ADVERTISEMENT
SQL & PL/SQL :: Query Two Columns With Datatypes As Number And Varchar
Jul 17, 2012Have table with two columns with datatypes as number and varchar and the values in A column like 1,2,3 AND B column values like a,b,c. Now need to display data in a single column as 1,a,2,b,3,c.
View 4 Replies View RelatedSQL & PL/SQL :: Columns To Rows Using Case And Cross Join?
Aug 22, 2013Getting 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
PL/SQL :: Outer Join When Comparing Trimmed Columns
Jul 29, 2013Oracle 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
Dumping Relevant Data From Columns Of Different Tables - Report / Join
Nov 18, 2008As 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]...
SQL & PL/SQL :: IF / THEN Statements With Different Datatypes
Sep 30, 2011I have a column in a database that contains both numerical and char data. I would like to be able to do two different things (two different queries)
1. divide the numerical data in the column by 10 and leave the char data alone (just return it)
2. detect the numerical data in the column and treat is as a different value so I can run averages & counts on it while disregarding the char data
I'm not at all sure how to do number 2. I thought a CASE statement would work for number 1, but then I realized CASE doesn't like different datatypes:
select
case when '1234' = 'checked' then 'checked'
when '1234' = 'gen.nograde' then 'gen.nograde'
when '1234' = null then null
else '1234'/10 end as "GRADE"
from dual
Error report:
SQL Error: ORA-00932: inconsistent datatypes: expected CHAR got NUMBER
00932. 00000 - "inconsistent datatypes: expected %s got %s"
PL/SQL :: NVL Statement For All Datatypes
May 2, 2013On DB 11.2. I am dynamically building a DB after update trigger that compares old/new column values. I wanted to include a nvl statement in my if conditions as some field values may be blank.
Since I have many different datatypes (varchar,date,number,blob,clob), is there a single nvl comparison I can use that will work against all of these? I was hoping not to have to check the datatype and change my nvl comparison depending on what type it is. i.e.
nvl(x,'1') or nvl(mydate,sysdate)
SQL & PL/SQL :: Dealers Count - Inconsistent Datatypes
Oct 4, 2011I have a PLSQL block as below :
DECLARE
cursor c1 is select /*+ INDEX(NI04.NI_DPR_DEALER IX_DPR_DEALER) */ DEALER_CODE from ni_dpr_dealer where not exists (select null from dealer_processed where ni_dpr_deale
r.DEALER_CODE = dealer_processed.DEALER_CODE);
type cur_type is REF CURSOR;
[code].......
show errors;
I am getting errors as below
Entering
Dealers count ::13236
entering conditions
Dealer name at 1 => HOL202
DECLARE
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 27
SQL & PL/SQL :: ORA-00932 / Inconsistent Datatypes / Expected - Got -
Mar 6, 2012I have writen PL/SQL packages for data loging through pipe lined function for better peformance.The below packages has been compiled sucessfully but during the run time it shows an error
like "ORA-00932: inconsistent datatypes: expected - got -".
CREATE OR REPLACE PACKAGE pkg_mkt_hub_load
AS
PROCEDURE sp_final_load_mkt_hub;
FUNCTION fnc_pipe_tot_lvl_idx_mon_hub
(pi_input_cur IN SYS_REFCURSOR)
RETURN tot_lvl_idx_mon_tt
PIPELINED;
[code]...
SHOW ERRORS
Error:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 33
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 55
ORA-06512: at "GPAIHMKTDTA.PKG_MKT_HUB_LOAD", line 92
ORA-06512: at line 1
types scripts:
create or replace type tot_lvl_idx_mon_ot as object
(SSIA_INDEX_ID VARCHAR2(60),
start_date date,
CURRENCY VARCHAR2(10),
LEVEL1 NUMBER(31,11),
TYPE VARCHAR2(31) ,
[code].....
SQL & PL/SQL :: ORA-00932 - Inconsistent Datatypes - UTL-FILE?
Apr 13, 2011I am working on a data migration project where I am extracting data from Oracle8i database and writing it to a Text file using File_UTL utility. In one extract procedure, I need to write a LONG datatype with some VARCHAR2 datatype in extract file. Procedure compiled fine but I am getting the "ORA-00932: inconsistent datatypes" error while executing the final procedure to write data to a file.
below the code snipplet. I am trying t write Account_Primarykey, Account_name and it's AText (LONG datatype) together in a extract file:
create or replace procedure FORCE_W_ACCOUNT as
Begin
declare
file_handel UTL_FILE.FILE_TYPE;
[code]...
SQL & PL/SQL :: ORA-00932 Inconsistent Datatypes - Expected - Got CLOB
Jun 21, 2006There we have two tables: my_comment and sys_comment.And field comment_desc in table my_comment is CLOB, and in table sys_comment is VARCHAR.
And I can run following two SQL successsfully:
SELECT comment_desc FROM my_comment;
SELECT TO_CLOB(comment_desc) FROM sys_comment;
But when I do UNION of them, execute following SQL:
SELECT comment_desc FROM my_comment
UNION
SELECT TO_CLOB(comment_desc) FROM sys_comment;
Error occurs:
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB
SQL & PL/SQL :: Failed - ORA-00932 / Inconsistent Datatypes / Expected - Got -
Aug 19, 2013I am fetching more than one column data from a table repetedly(By different query string each time) through ref cursor using concatenate function successfully. But I can see if one concatenate function is getting missed due to human intervention, it's causing failure for entaire process and returning oracle exception as "Failed - ORA-00932: inconsistent datatypes: expected - got -".
View 3 Replies View RelatedPL/SQL :: ORA-00932 / Inconsistent Datatypes / Expected NUMBER Got -
Dec 26, 2012I am using below query for collect
select account_id_N,collect(connection_id_v) from My_test_table group by account_id_n;
but its giving me error as below.
ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
XML DB :: Variable Datatypes In Select Of Data From XML To Oracle
Jan 30, 2013I'm new to XML to Oracle data integration. I do have clob column that has the XML data and trying to bring it into the Oracle relational tables, as a part of it we are developing PL/SQL procedure that calls this CLOB column, I want to know if we can variablize the data types ( Just like tablename. column name%type) in the select of the XML data:
select v.instrid, v.endtoendid, v.txid, v.cd, v.ccy,
replace(v.intrbksttlmamt,'.',',') as intrbksttlmamt, v.chrgbr, v.bic1, v.nm1, v.adrline11, v.adrline12, v.ctry1, v.iban1,
v.bic2, v.bic3, v.nm2, v.adrline21, v.adrline22, v.ctry2, v.iban2, v.cdtrref, v.addtlrmtinf
from the_data w,
xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:pacs.008.001.01'),
[code]....
I do define the data types after every column name , the problem is incase there is change in datatype I do need to manually edit the procedure to change the datatype is there a way I can variablize them something like tablename.columname%type.
SQL & PL/SQL :: ORA-00932 Inconsistent Datatypes - Expected NUMBER Got CHAR
Aug 17, 2010My table looks like this ->
Dp_value
124325
2434
3536
3536
Code is ->
(case when CL.DECIMALPLACES = 0 and CL.FIELDTYPE = 'D' and cl.DATATYPE = 'N'
then trim(dp."Value")*1000
else dp."Value" end) as dp_value,
What I am trying to do ->
From another table cl I want to say if decimal place is 0, fieldtype is decimal (D/C) and datatype is number (N/T)then multiply the number with 1000.
If not then leave it as it is.
When I run my code I get the error:
ORA-00932 : inconsistent datatypes: expected NUMBER got CHAR
for else dp."Value" end)
How To Compare Data Of Clob And Long Datatypes Using DBLink
Aug 13, 2012I would like to run below query on all tables, however it doesnt work on clob and long datatypes
select * from owner.table_name
minus
select * from owner.table_name@remote_db;
from dba_tables
where owner in ( '....');
ORA-00932: inconsistent datatypes: expected - got CLOB
How can I compare the data of clob and long datatypes using dblink ?
Replication :: Oracle Streams - Column Datatypes In Some Of The Tables On Source And Destination Are Different?
Jul 20, 2009We are using Oracle Streams for replication.
Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).
Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.
SQL & PL/SQL :: Normal Join And Outer Join
Oct 19, 2013Lets say I have three tables t1 and t2 and t3.
SELECT * FROM T1;
Id
____
1
2
3
4
SELECT * FROM T2;
Id
____
1
SELECT * FROM T3;
Id
____
1
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4
One way of doing that is using the not exists clause
select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)
Is there a better way of doing this in sql ?
Reports & Discoverer :: Create A Report By Using One Field / Text As Columns Name In Layout But Display All Columns
Jun 16, 2010I want to create a report by using one field and one text as columns name in layout but display the all the columns. I mention the 5 column names in query.how can I write function in summary column.
View 4 Replies View RelatedPerformance Tuning :: Index 15 Columns When Cannot Predict Columns Will Be Used In Where Clause?
Apr 4, 2011I am running a fairly busy Oracle 10gR2 DB, one of the tables has about 120 columns and this table receives on average 1500 insertions per second. The table is partitioned and the partitioning is based on the most important of the two timestamp columns. There are two timestamps, they hold different times.
Out of these 120 columns, about 15 need to be indexed. Out of the 15 two of them are timestamp, at least one of these two timestamp columns is always in the where clause the queries.
Now the challenge is, the queries we run can have any combination of the 13 other columns + one timestamp. In reality the queries never have more than 7 or 8 columns in the where clause but even if we had only 4 columns in the where clause we would still have the same problem.
So if I create one concatenated index for all these columns it will not be very efficient because after the 4th or 5th column the sorting would no longer be very useful and I believe the optimiser would simply not use the rest of the index. So queries that use the leading columns of the index in sequence work well, but if I need to query the 10th column the I have performance issues.
Now, if I create multiple single column indexes oracle will have to work a lot harder to maintain all these indexes and it will create performance issues (I have tried that). Besides, if I have multiple single column indexes the optimiser will do nested loops twice or three times and will hit only the first few columns of the where clause so I think it will kind of be the same as the long concatenated index.
What I am trying to do is exactly what the Bitmap index would do, it would be very good if I could use the AND condition that a Bitmap index uses. This way I could have N number of single column indexes which the optimiser could pick from and serve the query with exactly the ones it needs. But unfortunately using the Bitmap index here is not an option given the large amount of inserts that I get on this table.
I have been looking for alternatives, I have considered creating multiple shorter concatenated indexes but this still would not address the issue since many queries would still not be served properly and therefore would take a very long time to complete.
What I had in mind would be some sort of multidimensional index, I am not even sure if such thing exists. But essentially it would be some sort of index that could serve a query efficiently regardless of the fact that the where clause has the 1st, 3rd and last columns of the index.
So considering how widely used Oracle is and how many super large databases there are out there, this problem must be common.
SQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query
Jan 21, 2011I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
SQL & PL/SQL :: Left Outer Join Versus Right Outer Join
Aug 14, 2009i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies View RelatedSQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?
Jul 12, 2012I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :
Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223
PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows
Aug 22, 2012what 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.
SQL & PL/SQL :: How To Join Two Where
Oct 15, 2012I want to create a query with only the junction of these two queries:
- select id_utilizador,nbeneficiario,nome from adm_utilizadores a WHERE exists ( select 1 from adm_util_grupos b where
a.id_utilizador = b.id_utilizador and b.id_grupo = '1') ;
- SELECT b.*,rownum as row_num FROM ADM_UTILIZADORES b ORDER BY $sidx $sord
WHERE row_num BETWEEN $start AND $end";
Virtually want id_utilizador, nome, and nbeneficiario, I can get the first query, ordered me returning the first 10.
Join Using First Match
Oct 2, 2011I am new to sql..
I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..
I cant use group by as well, since for group by hv to select all the columns which we need to display
SQL & PL/SQL :: Join On Two Tables
Jul 29, 2013I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.
Now, I need to compare the data of the two tables column-wise.
Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.
Attached are the table structures and the data it contains.
Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.
SQL & PL/SQL :: Query On Self Join
Nov 13, 2011The existing format of the records are
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
This should be modified as below.
MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107
[Code]..
Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.
SQL & PL/SQL :: Using Update With A Join?
Mar 16, 2011im trying to update a column in the employee table with the value "YES". Im getting an error message saying im missing a SET statement from this code below:
update e
SET e.review='YES'
from employee
inner join rentals r
on e.employee_id=r.employee_id
inner join job j
on e.job_id=j.job_id
where r.plate ='FY06WNT'
and j.function !='MANAGER'
and j.function !='PRESIDENT';
SQL & PL/SQL :: How To Update With Join
Jul 7, 2010drop table dev10 purge
/
drop table dev11 purge
/
drop table dev12 purge
/
create table dev10 as
select rownum c1, sys.dbms_random.string('U', 6) c2, trunc(sys.dbms_random.value(1, 7)) c3
from dual connect by rownum < 8
/
[Code]...
Now, Let us assume that, the record is
AAAAAA BBBBBB CCCCCC
DDDDDD EEEEEE FFFFFF
...
..
.
Now, we want dev12.c2 is 'FFFFFF' if dev11.c2 is 'BBBBBB', if we want to get this:
AAAAAA BBBBBB FFFFFF
DDDDDD EEEEEE FFFFFF
...
..
.
We can make this for SqlServer by coding:
UPDATE dev10
SET c3 = dev12.c1 FROM dev10 INNER JOIN dev11 ON dev11.c3 = dev10.c1 CROSS JOIN dev12
WHERE (dev11.c2 LIKE 'BBBBBB')
AND (dev12.c2 LIKE 'FFFFFF')
/
but, Oracle, what should we do new?