SQL & PL/SQL :: Display Results Using CASE WHEN?
May 24, 2011
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]....
View 11 Replies
ADVERTISEMENT
Jan 31, 2012
The Ename SMITH should appear as Smith.
View 1 Replies
View Related
Apr 12, 2012
How do i execute and display results of a procedure declared with ref cursor OUT parameter.
I am using SQL Developer and Oracle 10.2.
View 4 Replies
View Related
Sep 5, 2013
I have a page with two regions the top region is an html region where users enter query criteria and generate an interactive report in the second region. The second region only displays if there are results from the query in the first region.
I would like to display a "No Results Found" message rather than a blank screen if there are no results found. However, I don't know exactly how to do that. I tried to do it in the Interactive Report region, however, once I added the Conditional display to only when exists rows in the Interactive Report from the query criteria.
View 4 Replies
View Related
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
Jun 3, 2010
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]....
View 6 Replies
View Related
Sep 22, 2010
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]...
View 8 Replies
View Related
Apr 4, 2007
i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?
View 1 Replies
View Related
Feb 7, 2012
I am getting duplicate results..I have been at it for hours now.This is what I have:
DECLARE
v_course_id classes.course_id%TYPE := :course_id;
v_instr_id classes.instr_id%TYPE := :instructor_id;
[code]...
The output I get is this (There is only 1 of each in the database)
Class ID: 1 Status: Enrolled Name: John O'Reilly
Class ID: 1 Status: Enrolled Name: Natacha Hansen
Class ID: 1 Status: Enrolled Name: Reed Jetto
Class ID: 1 Status: Enrolled Name: Janis Greenberg
Class ID: 1 Status: Enrolled Name: Vishal Singh
Class ID: 1 Status: Enrolled Name: Francis Hamilton
[code] ...
View 2 Replies
View Related
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
Nov 21, 2010
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
View 17 Replies
View Related
Feb 7, 2007
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'
View 2 Replies
View Related
Apr 20, 2007
I am testing a supposedly very simple stored procedure in XE. It compiles with no errors but when I enter the following command in the SQL Command window I get an ORA-00911: invalid character.
call get_all_customer_orgs;
The get_all_customer_orgs procedure is defined as.
create or replace procedure get_all_customer_orgs (p_recordset out sys_refcursor)
is
begin
open p_recordset for
select * from customerorgs;
end get_all_customer_orgs;
I don't understand what the invalid character could be.
View 5 Replies
View Related
Apr 23, 2007
Is there anyway to pivot the results of a query?
so if i have:
SELECT GROUP, count(*)
FROM GROUP
GROUP BY GROUP
And it give the following output:
A 10
B 50
c 24
Is there anyway to put into this format?
A B C
10 50 24
I am doubting that there is and that i am going to have to handle this in my code later, but it never hurts to ask!
View 1 Replies
View Related
Feb 26, 2008
I need to select a count of records where a field (call it widget) is the same, so i need all records where widgets are distinct. So it would be like asking for a distinct in a where clause. Not having much SQL experience this is a difficulty for me.
lets see .. so a count of records where widget = widget or something along those lines.
View 9 Replies
View Related
Aug 27, 2010
I would like to give back to the our application user a page of results for a given query along with the total result count, something like: "Showing 1-25 of 650 total results".
Currently I am doing this by submitting a second query:
select count(*) from (<previous query criteria>)
Is there a better performing approach I could be using?
View 4 Replies
View Related
Mar 18, 2011
Result are different after adding the Order By clause in below query.
Query
select sum(wpsv1.primary_qty) detail_qty,
wpsv1.from_subinventory subinventory,
wdd.revision revision,
[Code]....
View 7 Replies
View Related
Jun 6, 2013
SELECT
pas_code,
pas_profile,
count(sutp_id),
sum(sutp_price),
[code]...
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex
2664good stuff310069< because pbk_price is like 67 from that period
2664good stuff310071< because pbk_price is like 50 from other period
how to get all results in a single line like:
pas_codepas_profilesutp_idsutp_pricex
2664good stuff6200140
View 3 Replies
View Related
Nov 25, 2012
My table structure is like this
CREATE TABLE Tab1(LVL1_NM VARCHAR2(20), LVL2_NM VARCHAR2(20), LVL3_NM VARCHAR2(20), LVL4_NM VARCHAR2(20))
and the values will be for one value of LVL1_NM there will be different LVL2_NM values like wise for each and every value of LVL2_NM column there will different values in LVL3_NM column like wise for LVL4_NM is there any way to get the results as per their relation ships.
View 7 Replies
View Related
Oct 21, 2010
I have this table,
Create table TBL_OK_HIST
(
DATE_KEY NUMBER,
A_N VARCHAR2(22 BYTE),
R_DUR VARCHAR2(8 BYTE),
CH_DUR VARCHAR2(8 BYTE),
REV VARCHAR2(20 BYTE)
)
insert into TBL_OK_HIST
values (20101010,123456768,5,20,2);
insert into TBL_OK_HIST
values (20101010,123496568,15,20,2);
insert into TBL_OK_HIST
values (20101012,122235768,25,25,3);
[Code] ......
Thus, applying the following would yeld:
Select * from TBL_OK_HIST
DATE_KEYA_N R_DUR CH_DUR REV
201010101234567685202
2010101012349656815202
2010101212223576825253
201010112345676819202
2010101234567681252527
2010101323456768136365
2010101056768123411202
2010101134681256717202
2010101068123456755559
generate the following results:
range_start_rdur range_end_rdur no_of_an sum_of_rdur sum_of_chdur sum_of_rev
1 5 1 5 20 2
6 10 1 9 20 2
11 15 2 26 40 4
16 20 1 17 20 2
21 25 1 25 25 3
26 30 0 0 0 0
31 35 0 0 0 0
36 40 1 36 36 5
41 45 0 0 0 0
46 50 0 0 0 0
51 55 2 107 107 16
I thought I would make use of the following query, but I am not getting the proper results when applying it to a real table with more than 20 mln records:
SELECT trunc(R_DUR/6)*5+1 as range_start_rdur,
trunc(R_DUR/6)*5+5 range_end_rdur,
sum(noofan) as no_of_an,
sum(sumofrdur) as sum_of_rdur,
sum(sumofchdur) as sum_of_chdur,
[Code] ...........
View 1 Replies
View Related
Nov 11, 2010
I'm trying to select from table "A" where value1 and value2 match. If the values are not in table "A", try table "B". If the values exist in BOTH tables - only look at the results from table "A".
Table "A" may or may not have "new" data coming into the system.
Table "B" may or may not have "existing" data.
The code I am writing needs to find the "newest" data row for value1 and value2. Eventually the data in table "A" gets "Merged" into table "B" further on in the process.
Supposedly this can be done in a single query using a left outer join and the NVL function, but the person I inherited this from isn't available.
I've been able to get the row back if it exists in "A" or "B", but end up with two rows or no rows if the row exists in both... Arrrg...
View 9 Replies
View Related
Jan 31, 2013
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
union all
select 'initial val' param_name, 'mb256_type' param_type,'30' param_value from dual)
select param_name,param_type,param_value from t1;
desired output:
need output in a row in three different columns
param_value
01-01-1970 31-12-9999 30
I tried below query
SELECT *
FROM (
with t1 as
(
select 'eff_date' param_name, 'mb256_type' param_type,'01-01-1970' param_value from dual
union all
select 'disc_date' param_name, 'mb256_type' param_type,'31-12-9999' param_value from dual
[code]...
and am getting output as
param_type eff_date_param_value disc_date_param_value initial_ignoring_param_value
mbn256_type <null> <null> <null>
View 2 Replies
View Related
Jun 14, 2012
I am not getting added hours from results.
SELECT audittimestamp + interval (SELECT EXTRACT(TIMEZONE_HOUR
FROM systimestamp) FROM DUAL) hour from tab1I want to add Timezone_hour to my timestamp.
View 7 Replies
View Related
Mar 23, 2009
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'
View 3 Replies
View Related
Feb 1, 2012
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].......
View 1 Replies
View Related
Jul 21, 2010
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 Related
Aug 10, 2011
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.
View 2 Replies
View Related
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
Oct 11, 2011
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
View 4 Replies
View Related
Oct 30, 2013
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
View 3 Replies
View Related
