differs on below SQLs and where I need to use "MAX" and "CASE" together?
select trunc(sysdate),max(case when trunc(sysdate)='03jun2010' then 'correctdate' end) dates
from dual
output dates
-------------------------------
6/3/2010 6/3/2010
[code]....
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'm starting to Oracle and am having a doubt, need to check if a variable is null if I need to make an appointment if you do not need to make another appointment. I thought I was doing fine, but Oracle is pointing this error: "ORA-00900: invalid SQL statement."
- Case
CASE
WHEN 1 = 1
THEN
select *
from BANANA
WHEN 1 = 2
[code]...
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
i have small requirement
case when type=1 then max(col2) else end
case when type=2 then max(col2) else end
i wanted to have difference 2 timestamp
type(datatype- numeric)
col2(datatype-timestamp)
all it should be in one select condition
Im trying to make a query to get the grades of students, I already figured out how to get the average from the exams. Now I would like to make like a select case or if statements to get the actual grade.
Something like this:
average > 89 then grade = 'A'
average > 79 then grade = 'B'
average > 69 then grade = 'C'
average > 59 then grade = 'D'
else grade = 'F'
I have a query that will either return one record or zero records. When it returns zero records I want to replace my attributes with a sentinel, like 'N/A'. I tried the CASE statement but couldn't get anything to work
Sample (does not work):
select
(case when exists (select product from tbl_product where productid = '123') then product else 'N/A' end) product
from tbl_product
where productid= '123';
If one record exists it should produce: 'My Widget' (or whatever)
If zero records exist it should produce: 'N/A'
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].......
I would like to know does oracle_sid is case sensitive or not on Linux platform?also let me know on windows platform also?
View 14 Replies View Relatedselect 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.
I am trying to create a query that displays the given error message if the result of my COUNT(*) is smaller than 1, but displays the result of my first query (data) if the total count is bigger than 1 (read: the query found data, so it needs to display the rows according to the search).
What do I need to do to display 'data' if 'data2' contains rows?
WITH data
AS (SELECT a.order_id, a.session_id, a.log_id, b.date_of_order, a.operation,
b.funct_prod_code, b.sts_status_code, b.ost_order_situation_code, c.order_situation_oms,
[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)
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
Can we use select statement in case statment for an update?
Example
UPDATE x SET field = CASE WHEN
(SELECT field1
FROM Y
WHERE field3 = x.field3
IS NOT NULL
THEN 'B'
ELSE 'C'
where cny# = 112160
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.
is there a possible to use a case statement in insert clause?
View 5 Replies View RelatedI 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.
How to use CASE stmt in WHERE clause?
View 3 Replies View Related The query has a case statement in the where clause so that results can be filtered. If I pass "ut" for sso_id then the query returns 21 rows. If I remove the case statement and hard code "a.sso_id like lower('ut'||'%')" then the query returns 41 rows. The query should be returning 41 rows all the time.
Problem:
When passing "ut" as an SSOID parameter to the Procedure the query returns 21 rows.Taking the query and hard coding "a.sso_id like lower('ut'||'%')" the query returns 41 rows.
Result:
query should be returning 41 rows when "ut" is passed an an SSOID parameter.
Returns 21 rows
procedure SSO (SSOID in varchar2 default null,
Name in varchar2 default null,
Campus in varchar2 default null,
Department in varchar2 default null,
[code]...
Returns 41 rows
open Results for
select a.sso_id,
(a.name_last||', '||a.name_first) as name,
b.site,
[code]...
Test Data
CREATE TABLE ID
(
SSO_ID VARCHAR2(60 BYTE),
NAME_FIRST VARCHAR2(100 BYTE),
NAME_LAST VARCHAR2(100 BYTE),
[code]...
Test Data
CREATE TABLE NT
(
LOWER_NT_ID VARCHAR2(60 BYTE),
DEPARTMENT VARCHAR2(100 BYTE),
[code]....
Table 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 RelatedDATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.
DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.
DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
INSERT INTO TEMP_TEST
[code]...
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].....
select 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 RelatedI would like to know, if there is a solution under 10g for something like this
select case when "C" = 1
then
(select 1,2,3,4,5 from dual)
else
(select 6,7,8,9,0 from dual)
end
from dual,
(select 1 "C" from dual)
What I really need is the full result of (select 1,2,3,4,5 from dual) or (select 6,7,8,9,0 from dual) if a condition as a result of a different sql-statement is true or false. It doesn't matter if the solution uses "case when" or anything else.
The 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]....
I just wanted to know how the query will be executed in case of where clause and ROWNUM clause. e.g, consider below query.
SELECT * FROM emp
WHERE dept IN (20, 30, 21)
AND salary < 10000
AND rownum <2;
My question is that when rownum will be executed?
1. First all the data according to where clause will be fetched and rownum is assigned and then rownum will be executed on that data.
2. First complete data will be fetched, ROWNUM will be applied and then WHERE clause will be executed along with ROWNUM clause.
i executed the below code and found that output should be 5 but it shows 7(always else part). it is not comparing string in case.
declare
check_in_date date;
var_day varchar2(11);
rate_id number;
[code]....