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
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..
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
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]....
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)
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.
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].....
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?
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 ?
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.
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.
Is there a save exceptions clause or an equivalent for an Insert as select* statement ?
How do I trap the errors in the below statement -
INSERT INTO copy_emp
SELECT * FROM emp;
Is it an all or nothing scenario ?
I have used a decode statement in my query instead of using select * from <table_name> where value in <>
Instead I have used decode(value,<value1>, <value2>)
Which would be a better performance code?
I'm having some trouble writing this query using a decode statement within a join. First, lets say I have two tables. Table A has the fields 'Baseline', 'NAD', 'Null', and 'Harn'. Table B has the fields 'Null', 'NAD', and 'Harn'. I want to join these tables together by these fields to return their ID's. Because table B doesn't have 'Baseline' to even have a match between the two tables, in Table A, we want to treat 'Baseline' as 'Harn', hence a decode statement. My query is below
Select
...
...
...
From
...
inner join oracle_srid_xref B on A.horizontal_datum = B.horizontal_datum and
decode(A.horizontal_epoch, 'BASELINE', 'HARN', loc.horizontal_epoch ) = B.horizontal_epoch
I'm not getting the ID's where the horizontal_epoch was initially 'Baseline'
Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?
View 4 Replies View RelatedI have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-
Test Table create scripts
CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20),
reason_code_stage_2 varchar2(20), opportunity_date date )
INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate)
INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)
[code]....
how to work out the update statement to use the conditional statement for columns.
I am trying to use decode function in sql and inside decode function can I use select statement ?
here is my sql
select we.wf_entity_id, decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
) from wf_entity we
where
[code]....
see this
decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
)
will this work? Its not working for me?
I was trying to write nested decode statement with multiple searches. For example
Case when seq = 1 and date1 > date2 then Record_Name End
How can i modify this to decode.
Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.
SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......
select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and
( employee_status in(Case employee_status when {?Status}=1 then 'A'
when {?Status}= 2 then 'T'
When {?Status}= 3 then 'A'||','||'T'))
OR ( end_date >= {?START_DATE}
AND end_date <= {?END_DATE}
)
)
Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.
4 records for employee_status 'A'
3 RECORDS for employee_status 'T'
7 records for employee_status 'A' AND 'T'
How I should write a query to get 7 records.
Can we use the sequence.nextval,sequence.currval inside case block.
CREATE TABLE EQUALITY_TEST(NUM1 NUMBER, NUM2 NUMBER, SEQ NUMBER);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,0 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 2 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,2 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
INSERT INTO EQUALITY_TEST VALUES ( 12 ,12 , NULL);
CREATE SEQUENCE SEQ_TEMP START WITH 100 INCREMENT BY 1;
Now i need to update SEQ column with SEQ_TEMP sequence. When NUM1,NUM2 values are unequal sequence should be incremented otherwise need to use the same sequence number(CURRVAL)
I have tried like this
UPDATE EQUALITY_TEST
SET SEQ=
CASE WHEN NUM1=NUM2
THEN SEQ_TEMP.NEXTVAL
ELSE
SEQ_TEMP.CURRVAL
END ;
SELECT * FROM EQUALITY_TEST;
Output
NUM1NUM2SEQ
120100
222101
322102
4122103
51212104
61212105
But Required Output
NUM1NUM2SEQ
120100
222100
322100
4122101
51212101
61212101
I have the following tables:
create table lookups (code varchar2(20), amount number);
insert into lookups values ('Rent' , 500);
insert into lookups values ('Breakpoint' , 10);
create table products (id number, cost number, year varchar2(4));
insert into products values (1, 1000, '2011');
insert into products values (1, 2000, '2011');
insert into products values (2, 100, '2011');
insert into products values (3, 50, '2011');
commit;
I want to write a query which lists the IDs and the sum(cost), and a Y/N indicator which is set to 'Y' IF sum(cost) > ( (lookups.rent value) * (100 - lookups.breakpoint value))/100
I have written this query:
SELECT id,
sum(cost)cost,
year,
CASE
WHEN cost >
((SELECT amount
[code]....... ORDER BY id;
This returns
ID COST YEAR YN
--------- ---------- ---- -
1 1000 2011 Y
1 2000 2011 Y
2 100 2011 N
3 50 2011 N
The YN is correct, but it needs to sum the amounts. So there should only be one row for id1 = 3000.e.g.
ID COST YEAR YN
--------- ---------- ---- -
1 3000 2011 Y
2 100 2011 N
3 50 2011 N
I am not sure how to do this. Or is there a better way of doing this than using CASE.
How to use CASE stmt in WHERE clause?
View 3 Replies View RelatedTable X includes multiple transactional records per household.I need to create a mailing list pulling only one record per household, choosing the one with the most recent date.
Each record within this table will have a Household_ID and a Date, so as an example, the table could have
Household_ID Date
000001 1/1/2011
000001 3/1/2011
000001 12/30/2011
000002 3/15/2011
000002 6/30/2011
000002 9/15/2011
I would want my results to include:
Household_ID Date
0000001 12/30/2011
0000002 9/15/2011
I'm working on a nested case statement, can't seem to get it right.We have a table that has injury_codes. What I'm trying to do is come with a nested case statement that will put the codes in a specific_cat, and based on the specific_Cat, assign a Generic_cat.
Example.
I have codes 11,12,13,14,15, 15, 17, 18, 19, 20, 21, 22
Codes 11, 12, 13 have a specific_cat "Head Injury"
Codes 14, 15, 16 have a specific_cat "Spinal Injury"
Codes 17, 18 have a specific_cat "Burns".
All 3 of these specific_cat come under the generic_cat "Trauma"
Codes 19, 20 have a specific_Cat "High Risk Pregancy"
Codes 21, 22 have a specific_cat "Premature Birth".
All 2 of these specific_cat come under the generic cat "OBGYN"...So my case stement should return :
CODESSPECIFIC_CATGeneric_Cat
11Head InjuryTrauma
12Head InjuryTrauma
13 Head InjuryTrauma
14Spinal InjuryTrauma
15Spinal InjuryTrauma
16Spinal InjuryTrauma
[code]...
just a small sample of codes, specific_Cat and generic_cat. I hundreds of these codes I need to categorize.
When I tried to have a Cursor with SELECT CASE statement in Forms 6i , it is not working. But the same query is working in SQL PLUS . We cannot use case in Forms 6i ?
View 5 Replies View Relatedselect GAM.FORACID,
SOL.SOL_DESC,
GAM.ACCT_NAME,
LHT.LIM_SANCT_DATE,
LHT.SANCT_LIM,
to_char( GAM.CLR_BAL_AMT,'9,99,999.99'),
[code]...
i am getting error like this
ERROR at line 9:
ORA-00911: invalid character
select iloan_code,inst_due_date,paid_flag,late_fee,case late_fee when sysdate-inst_due_date between 1 and 10 then 10 when sysdate-inst_due_date > 10 and late_fee <>10 then 5 when sysdate-inst_due_date > 10 and late_fee = 10 then 15 else 0 end as new_late_fee from st_il_schedule where paid_flag='N'; i am getting error
View 3 Replies View RelatedThe line highlighted in Bold is where I have the issue. I want it to pick up NULL or Blanks in the method type has well as types E,G,T. I've tried NULL is the statement, "", and '' put none of these work. What am I doing wrong? I'm using 11g by the way
select specialty,
member_id,
sp_id,
service_date,
sum(case when method_type = 'C' then (payment_total) end) "CONSULTATION",
sum(case when method_type = 'M' then (payment_total) end) "MOT",
sum(case when method_type = 'D' then (payment_total) end) "TESTS",
[Code]....
In the following query WHEN
supplier_name = 'IBM' and supplier_type = 'Hardware' THEN
only 'North office' is being retrieved but how to get 'South office' also along with it.Is there a way to write multiple conditions in THEN clause of case stmt something like THEN 'North office' AND 'South office'.
create table suppliers_temp(supplier_id number(5),supplier_name varchar2(10),supplier_type varchar2(10));
table suppliers_temp created.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(1,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(2,'IBM','Hardware');
1 rows inserted.
insert into suppliers_temp(supplier_id,supplier_name ,supplier_type)values(3,'TCS','Software');
1 rows inserted.
[code]....