SQL & PL/SQL :: Data Type Consistency CASE And Decode

Apr 5, 2012

Data Type Consistency CASE and Decode...CASE expects data type consistency, DECODE not expecting.Obviously both functions handling data types are different..let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;
[color=red]RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;
[code]...

I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug! Oracle compares int variables to int variables, char variables to char variables, If any discrepancy between the two data types, then the query will fail but here not different data types.

Even if I refer reference books some concepts are blind to understand.

View 5 Replies


ADVERTISEMENT

Data Type Consistency CASE / Decode?

Apr 6, 2012

Data Type Consistency CASE and Decode

CASE expects data type consistency, DECODE not expecting.

Obviously both functions handling data types are different let it be

SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;

RESULT
---
2
SQL> select case 2
2 when 1 then 1
3 when 2 then 2
4 else 3
5 end "RESULT" from dual;

RESULT
2
SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

know cause of error here????I mean " every time case exp checking data type consistency"

my thought is

I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!

I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table...
obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,

I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

see this query

SQL> select case 2
2 when 1 then 1
3 when '2' then 2
4 else 3
5 end "RESULT" from dual;

// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.

View 2 Replies View Related

SQL & PL/SQL :: Case Vs Decode?

Feb 13, 2011

How to use decode and case in "where" and "from" clause of a select statement.

I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition

View 7 Replies View Related

SQL & PL/SQL :: DECODE And CASE In WHERE Clause

May 7, 2010

I am facing a problem while retrieving data from table using DECODE/CASE.

Table: PARAM_MSTR

MIN_VALMAX_VALPARAM_CODE DESCRIPTION DATATYPE
AB1000 HARD PARAMETERTEXT
CN1000 SOFT PARAMETERTEXT
0501001 CRYSTAL PARAMETERNUMBER
512001001 STONE PARAMETERNUMBER

Now I want to get the parameter description based upon the PARAM_CODE and a value passed which should be in range of MIN_VAL and MAX_VAL. Means when I pass PARAM_CODE=1000 and :parameter=A, then it should check the DATATYPE of the PARAM_CODE, in our case it is 'TEXT' so it should check the passed value between MIN_VAL and MAX_VAL like

:parameter BETWEEN MIN_VAL AND MAX_VAL and should return 'HARD PARAMETER'.
If I pass PARAM_CODE=1001, then the DATA_TYPE is 'NUMBER', so it will check the :parameter value as Number.
Like :parameter BETWEEN to_number(MIN_VAL) AND to_number(MAX_VAL)

For example:
PARAM_CODE :parametr Result
1000 A HARD PARAMETER
1000 C SOFT PARAMETER
1000 P NULL
1001 25 CRYSTAL PARAMETER
1001 99 STONE PARAMETER
1001 201 NULL

I have written a query using DECODE and CASE statement but it is not working properly.

SELECT * FROM param_mstr WHERE PARAM_CODE=1000 AND :parameter BETWEEN DECODE(DATATYPE,'NUMBER',CAST(MIN_VAL as NUMBER),MIN_VAL)AND DECODE(DATATYPE,'NUMBER',CAST(MAX_VAL as NUMBER),MAX_VAL)

View 3 Replies View Related

SQL & PL/SQL :: Decode / Case Functions?

Aug 19, 2010

I am having some records in the table. If the record num is

1--It should show the month as "Apr"
2--"May"
3--"Jun"
4--"July"
5--"Aug"
6--"Sept"

if it is having other than these 6 should show "0" for the remaining months.

View 10 Replies View Related

SQL & PL/SQL :: How To Convert Case To DECODE

Oct 20, 2011

I had written follwoing sql with CASE statments, but in oracle 8i i'm unable to use CASE in pl/SQL.

how to convert following sql to DECODE ? Or is there any option other than DECODE ?

SELECT ol.order_no,
ol.transaction_line_id,
ol.pd_actl_tonnage,
ol.real_tonnage_high,
ol.req_tonnage,
il.id,
il.transaction_ref,

[code].....

View 18 Replies View Related

PL/SQL :: Case When Statement In Decode

Dec 14, 2012

Here is one an example with CASE WHEN statement.

SELECT empno,ename,sal,
CASE
WHEN sal BETWEEN 800 AND 1200
THEN 'Lowest Pay'

[Code]...

even DECODE can be a replacement for this scenario, but it cannot have as flexibility as CASE can have.

Now, I want Equivalent code for DECODE..

View 11 Replies View Related

PL/SQL :: Group By Using Decode And Case?

May 14, 2013

my query is some thing like this but having more column in select. when i am firing this query it is giving result but that is not proper,\.

my problem is , like if there are 3 more values for uh.sflowtype (0,1,2) then group by is not working for them and those are coming in different row , i need them to be combined

query is :
select substr(uh.sstartdatetime,1,8) DateTime,
( case
when uh.sflowtype=7 then 'sms'
when uh.sflowtype=9 then 'mms'
when uh.sflowtype=10 then 'gprs'

[code]....

result :

DATETIME FLOWTYPE
-------- --------
20130507 voice
20130507 voice
20130507 voice
20130507 sms
20130507 mms

but i need

20130507 voice
20130507 sms
20130507 mms

so what should i do?

View 8 Replies View Related

SQL & PL/SQL :: Getting Equivalent DECODE For CASE Statement?

Feb 21, 2013

case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age

the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode

View 10 Replies View Related

PL/SQL :: Using Function On Decode Or Case In Query

Mar 3, 2013

I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code

select CASE when length(name) > 39,substr(name,0,39)
else name
END

from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?

View 3 Replies View Related

Oracle Data Pump Dumps Consistency

Mar 8, 2011

By default the Oracle utility expdp will not guarantee data consistency for particular point in time when the dump was taken.

When importing such dump you can get errors like

CODEORA-39083: Object type REF_CONSTRAINT failed to create with error...
ORA-02298: cannot validate (...) - parent keys not found

To make your Oracle data pump dumps consistent use flashback_scn or flashback_time option.

Example:

CODEexpdp myschema/... DIRECTORY=expdp_dir DUMPFILE=mydb_20110308.dmp logfile=mydb_20110308.log SCHEMAS=myschema FLASHBACK_TIME=\"TO_TIMESTAMP\(TO_CHAR\(SYSDATE,\'YYYY-MM-DD HH24:MI:SS\'\),\'YYYY-MM-DD HH24:MI:SS\'\)\"

View 2 Replies View Related

Right Parameter Types Of ODCIIndexInsert In Case Of Creating Index-type WITH ARRAY DML?

Sep 17, 2013

During developing Domain Index for Oracle 11.2.0.1.0 (problem also appears in 12с) i was faced with misunderstanding of parameter types of function ODCIIndexInsert in case of creating indextype WITH ARRAY DML option.According to Oracle documentation [URL}...

In case of WITH ARRAY DML option Oracle will invoke ODCIIndexInsert with following signature

FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)
RETURN NUMBER

In my case indexed column has datatype NUMBER so i defined varray_of_column_type as SYS.ODCINumberList STATIC FUNCTION ODCIIndexInsert(ia in sys.ODCIIndexInfo, ridlist in sys.ODCIRidList, newvallist in sys.ODCINumberList, env in SYS.ODCIEnv) RETURN NUMBER

Indextype was created as

CREATE INDEXTYPE test_index_type
FOR
test_eq(number, number)
USING index_methods
WITH ARRAY DML(number, sys.ODCINumberList)

[code]...

(problem occurs in all cases)
CREATE TABLE test_table (id NUMBER (19,0));
CREATE INDEX test_index ON test_table(id) INDEXTYPE IS test_index_type;

When attempting to insert data in the table insert into test_table values (1);

oracle raise exception

CODEError starting at line 53 in command:
insert into test_table values (1)
Error at Command Line:53 Column:1
Error report:

[code]...

So my question is.Is it normal behavior of oracle (according to documentation)?What is correct signature of ODCIIndexInsert function in case of INDEXTYPE creation with 'WITH ARRAY DML' option and fact that indexed column has NUMBER datatype?

By the way if i define indextype without 'WITH ARRAY DML' option signature is clear, and working. But this approach doesn't satisfies our performance needs.Also if i define index type with option 'WITH ARRAY DML WITHOUT COLUMN DATA' and use signature static function ODCIIndexInsert(ia sys.odciindexinfo, ridlist sys.odciridlist, env sys.ODCIEnv) return number

Everything works too. But this approach doesn't satisfies our business needs.Is it a way to define ODCIIndexInsert parameter types (in case of indexing number column) so that batch inserting works according to documentation ?

FUNCTION ODCIIndexInsert(
ia ODCIIndexInfo,
ridlist ODCIRidList,
newvallist varray_of_column_type,
env ODCIEnv)

I am attaching full sql script to recreate environment and reproduce the problem.Type definition:

CREATE OR REPLACE TYPE index_methods AS OBJECT
(
step number,
STATIC FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
STATIC FUNCTION ODCIIndexCreate (ia SYS.ODCIIndexInfo, parms VARCHAR2, env SYS.ODCIEnv) RETURN NUMBER,
STATIC FUNCTION ODCIIndexAlter (ia sys.ODCIIndexInfo, parms IN OUT VARCHAR2, altopt number, env sys.ODCIEnv)

[code]...

View 1 Replies View Related

Server Administration :: Functions To Convert The Long Type Field Data To Varchar2 Type

Oct 17, 2011

Is there some functions to convert the long type field data to varchar2 type?

View 2 Replies View Related

Consistency Not Working In Audit Logs And Datafile?

Dec 14, 2012

We have audit logs of a transaction in audit files, however we do not see any changes in the table that the transaction affects.We use point-in-time recovery and flashback feature to figure out the changes in the table . DML Audit Granularity is "ACCESS".The transaction is java application transaction and we use hibernate.

How can this be possibble?

View 7 Replies View Related

PL/SQL :: ORA-06592 / CASE Not Found While Executing CASE Statement

Oct 16, 2013

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]....

View 23 Replies View Related

SQL & PL/SQL :: Arranging Data In Required Format Using Decode By Hardcoding All Values

Feb 26, 2012

I have a sample table where I am arranging data in required format using 'decode' by hardcoding all values, how can we do same using pivot method, can pivot be only used when we want aggregate values in oracle?Also can't we do what i did, using generic max(decode) with select query, if it were a large table (i don't know which would be pivot column here?) ?

Table is
SQL> CREATE TABLE NOWT
2 (COURSE VARCHAR2(20),MEMBER VARCHAR2(20));
[code]....

how can we do that using pivot in Oracle?

View 6 Replies View Related

Raw Datatype Changing The Case Of The Data?

Jul 31, 2013

I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the  uppercase .Is it the expected behavior of this datatype.   I am using Oracle 11g  and attaching the ddl and dml pertaining to the column 

CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) );  insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf'); 

But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF

View 4 Replies View Related

SQL & PL/SQL :: Filter Data Using CASE Statement In WHERE Clause?

May 29, 2013

Want to filter a data using CASE statement in WHERE clause for the following scenario.

Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.

where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END

View 3 Replies View Related

Windows :: Case Sensitive Column Names In Data Reader

Nov 14, 2010

it seems that oracle data reader on some client machine is case sensitive. We are using oracle 10g. 2 client machines connect to the same DB and run the same code - one works and another doesn't. they are both using odac 2.111.7.20.

this is my .NET code (which works fine on every environment it was installed in the last 2 years):

using (OracleConnection sqlConn = new OracleConnection())
{
sqlConn.ConnectionString = connectionString;
using (OracleCommand sqlCmd = new OracleCommand())
{
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "getValues";
[code].......

this is the oracle procedure:

PROCEDURE getValues(
rc IN OUT sys_refcursor
)
IS
BEGIN
OPEN rc FOR
SELECT Name, Value FROM MySchema.MyTable;
END;

The problem that we saw was that on one of the client machines dr["Name"] threw exception :

Error Source: Oracle.DataAccess.Client.OracleDataReader.GetOrdinal
Error Message: System.IndexOutOfRangeException: Unable to find specified column in result set
at Oracle.DataAccess.Client.OracleDataReader.GetOrdinal(String name)
at Oracle.DataAccess.Client.OracleDataReader.get_Item(String columnName)

We modified the code and used NAME instead of Name and it worked!! (dr["NAME"] instead of dr["Name"])

How can it be? from oracle documentation:
A case-sensitive search is made to locate the specified column by its name. If this fails, then a case-insensitive search is made.

My question is what can cause such a behavior? Some ODAC/ODP definitions, .NET configuration?

View 4 Replies View Related

Store Data In CLOD Data Type - How To Create A Unique Index

May 20, 2013

We have been recommended to store data in CLOD data type.

Sample data: 1:2:2000000:20000:4455:000099:444:099999:....etc it will grow to a large number.

We want to create a Unique index, for functional reason. Is it advised to create a unique index on a CLOB datatype?

View 2 Replies View Related

Backup & Recovery :: How To Recover Unarchived Data In Case Of Media Failure

Jan 26, 2012

I have a database for which I use RMAN for backup & recovery. The database is in archivelog mode. Online redo log files are multiplexed and I have more than one destinations for archive logs. If, due to a disk crash, all data files and redo log files are gone - and I have archive logs, one member of each redo log group and a full database backup available, I can recover till the last archived log. But I want to ask that what about the transactions which were not archived at the time of media failure? How can I recover committed transactions which were not archived at the time of crash, and I have a mirrored copy of log group which was current at the time of failure?

View 5 Replies View Related

Data Guard :: Right Steps To Perform Failover In Case Primary Database Gets Fail

Jan 10, 2011

i have Oracle 10g data guard set up on windows environment.....i need to know the what are the right steps to perform failover in case the primary database gets fail.

View 4 Replies View Related

SQL & PL/SQL :: Extracting Data From LONG RAW Data Type Column

Oct 7, 2011

I have come one requirement where i need to extract data from a LONG RAW data type column.

View 7 Replies View Related

SQL & PL/SQL :: Getting Data From Record Type

Jul 19, 2010

I have one requirement. We have a Package which consists of 2 Stored Procedures which has a RecordType output Parameter.

FUNCTION GET_NFE_INFO(O_status_code IN OUT NUMBER,
O_error_message IN OUT VARCHAR2,
O_message IN OUT "OBJ_FM_NFE_DOCHDR_REC",
I_fiscal_doc_id IN FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE)
return BOOLEAN is

I am working on a one Stored Procedure which would display the output from the above function as,

set serveroutput on size 9000;

DECLARE

L_stg_status FM_FISCAL_DOC_HEADER.STATUS%TYPE := 'NFE_P';
L_fiscal_doc_id FM_FISCAL_DOC_HEADER.FISCAL_DOC_ID%TYPE := 12325;
L_dummy VARCHAR2(1) := NULL;
L_status_code NUMBER(1) := 0;
L_error_message VARCHAR2(255) := NULL;
L_message "OBJ_FM_NFE_DOCHDR_REC" := NULL;

[Code]..

Here for each and every data value i am Printing it using DBMS_OUTPUT, L_message record has more than 100 columns. Is there a easy way of handling it instead of using DBMS_OUTPUT ?

View 20 Replies View Related

SQL & PL/SQL :: What Should Be The Data Type For The Column

May 9, 2012

I have to create a table which should store data at Week level. The table have the following columns

Product id, Loc id, Business group id, FISCAL WEEK , Revenue,

Fiscal week column will have data as '2011-W01', '2011-W47' etc.

What should be the data type for fiscal week column. Based on this table i have to create a calculated column which should fetch trailing 12 weeks average for each row.

View 5 Replies View Related

User Defined Data Type

Jun 19, 2012

I am trying to create & use a new data type but keep getting a ora-0902 invalid data type error running on 10g express. The create type and select statements execute fine and select confirms the ssn_t is a type. The create table statement fails with the invalid data type error.

Here is an example.

CREATE TYPE ssn_t AS OBJECT ssn_type CHAR(11));
SELECT object_name, object_type
FROM user_objects WHERE object_type = 'TYPE';
CREATE TABLE Z (A CHAR(4), B SSN_T);

View 14 Replies View Related

SQL & PL/SQL :: How To Get Date From The Timestamp Data Type

Oct 13, 2011

How can i get just date from the timestamp data type.

Suppose i have a column timestamp with has data like "2011-05-16 16:19:22.579764-07" when i select from table i just want the date like 2011-05-16.

View 6 Replies View Related

SQL & PL/SQL :: How To Determine The Data Type Of A Variable

Sep 15, 2010

Code to determine the data type of a variable in oracle 10g.

View 5 Replies View Related

SQL & PL/SQL :: How To Insert Varying Type Of Data

Jul 14, 2010

I want to insert varying type of data from one table to another like i want to insert name from one table and salary from another,sysdate too.How should i consider doing it?

View 3 Replies View Related

Server Administration :: RAW Data-type?

Apr 3, 2013

RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.

View 5 Replies View Related







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