How To Use CASE When Set Query Empty

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


ADVERTISEMENT

SQL & PL/SQL :: Query To Find Empty Column?

Aug 3, 2010

I have a table with thirty column for attendance like Day_A01,Day_A02......Day_A31.

I need a query that should return the column which is empty but its next and previous column is not empty.

View 13 Replies View Related

Query To Search Empty Room Between Date

Mar 13, 2009

i have project for hotel , i can't solve query to search empty room between date . i have table for room , when room record under reservation between date . then i want to found empty room between date, which is query to make do.

Table Room
Room_No
Type_Room
From_Date
To_Date
Status

Now i need to found empty room between date search room empty between

11/03/2009 - 13/03/2009

select Room_No from Room where From_Date Not between '11/03/2009' and '13/03/2009' i found hard to query.

View 3 Replies View Related

SQL & PL/SQL :: Query With CASE Where Clause?

May 14, 2012

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

View 3 Replies View Related

SQL & PL/SQL :: Update Query Using Case When Exists?

Jan 4, 2013

I am trying to use the below query

update t_emp set TTL_FLG =
CASE
WHEN EXISTS
(SELECT 1 from Schema1.T_STG_LW_EMP E
WHERE E.Employee = Schema2.T_emp.EMPLOYEE_NUMBER
AND E.JB_CODE like '%TP%' or E.JB_CODE like '%DGD%' or E.JB_CODE like '%PDD%'
or E.JB_CODE like '%YND%'
)
THEN 'Y'
ELSE 'N'
END;

View 1 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

PL/SQL :: Query Sort By Case Sensitive

Oct 1, 2013

I am using oracle database 11g.My use case is I do have a table with following valuesTable name -test

product id     productsortdescription
H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK  My query is  
select * from test order by productsortdescription;  this query gives result as is like
product id     productsortdescription
H58098        ACETA

product id     productsortdescription
H58098        ACETAMIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK
MIDOHYDROXYPHENYLTHIAZOLE
043994         Alloy .MM.INTHICK

but Expected output/result should be like below: 

product id     productsortdescription

043994     Alloy .MM.INTHICKH58098      ACETAMIDOHYDROXYPHENYLTHIAZOLE as All and ACE

in productsortdescriptionl is in small case than C. NLS Session parameters are as following

SELECT * from NLS_SESSION_PARAMETERS;
 NLS_LANGUAGE    AMERICANNLS_TERRITORY    AMERICANLS_CURRENCY    $NLS_ISO_CURRENCY    AMERICANLS_NUMERIC_CHARACTERS    .,
NLS_CALENDAR    GREGORIANNLS_DATE_FORMAT    DD-MON-RRNLS_DATE_LANGUAGE    AMERICANNLS_SORT    BINARYNLS_TIME_FORMAT    HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT    HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT    DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY    $NLS_COMP    BINARYNLS_LENGTH_SEMANTICS    BYTENLS_NCHAR_CONV_EXCP    FALSE 

View 5 Replies View Related

PL/SQL :: Values Not Coming Correctly Using Case In Query?

Oct 15, 2012

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version

I am having the data in following table -

drop table stud_fact;
create table stud_fact(stud_NM, LVL_CD,ST_DT_DIM_KEY,OVRNK) as select
'ABG Sundal','H','20110630','175' from dual union all select

[Code].....

View 8 Replies View Related

PL/SQL :: How To Use CASE And COUNT Statements In SELECT QUERY

Oct 13, 2012

I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,

SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN
0||#SDC||#PERIOD||#BATCH_ID
WHEN #SDC <10 AND #PERIOD <10 THEN
0||#SDC||0||#PERIOD||#BATCH_ID
WHEN #SDC >=10 AND #PERIOD <10 THEN
#SDC||0||#PERIOD||#BATCH_ID
ELSE
#SDC||#PERIOD||#BATCH_ID
END

View 11 Replies View Related

Query Is Returning Output For Both Case Statements

Jun 2, 2011

below query is returning two rows.The thing now happening is the query is returning the output for both the case statements.But what is need only when the first case staement is NULL then it should go for second case.

SELECT DISTINCT CASE
WHEN esc.x1 = Substr(inp.y, 0, 3)
AND esc.x2 = Substr(inp.y, 4, 2)THEN esc.cc
WHEN esc.mcc = Substr(inp.y, 0, 3)
AND esc.mnc = Substr(inp.y, 4, 3)THEN esc.cc
[code]....

I tried using rownum=1 but it filters out valid records.correcting the above query so that if the first case is null then only it should go for second case.

View 2 Replies View Related

Generate CASE WHEN Query With ORDER BY Involving Secondary Sort

Oct 31, 2012

Below Query I understand is improper wrt syntax. Need to modify query to fetch correct result for me.

select * from tableA order by
case when a is not null then
a DESC
else
b DESC, c DESC END

Note: DESC or ASC is dynamic coming from java-code.

View 3 Replies View Related

Reports & Discoverer :: Case Statement Inside Oracle Query - Missing Right Parenthesis

Jun 13, 2013

Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.

My Report Query looks like this,

select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number

:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.

But the CASE statement is throwing Error

ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)

View 6 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

10g - Some VARCHAR2 Fields Empty?

Jul 21, 2011

Once a year in the application we have a specific query that gets used a lot. It's an UPDATE that updates a single record in a single table with a few different datatypes, but the issue is happening with one of the VARCHAR2 fields. It updates one VARCHAR2(2000) and three VARCHAR2(4000) fields at the same time.

This year, 9 of the 95 times it was used resulted in one of the VARCHAR2(4000) fields as null in the database. The users would not want this field to be null and 5 of the 9 have told us they entered something (the form they're filling out is a research proposal and leaving this field empty would be pointless because it's part of the funding request, so they're not doing it). The application isn't doing it because it's not consistent. I've checked the application and these fields can't be nulled any other way.

We just found the issue so I looked back over the past years back to 2005. Last year it didn't happen at all. In 2010 it happened a handful of times. Some years there were even more times. It's not always the same field but it's always a VARCHAR2 of at least 2000 characters.

I have a lot more information but it's all just details (let me know if you need to know more). I'm wondering if there is a bug in 10g with these types of fields. I don't believe it's malicious behavior on an individual's part but I suppose that's always possible.

how to research something like this. I tried to get access to Oracle Support and the Knowledge Base I heard they have but it doesn't look like I can do that

View 2 Replies View Related

Temporary Tablespace Is Empty

May 10, 2011

While I was exporting ( schema level ) ,I am getting following errors :

vaughn$ exp parfile=qar808par.txt
Export: Release 11.1.0.7.0 - Production on Tue May 10 10:52:34 2011
Copyright © 1982, 2007, Oracle. All rights reserved.
Username: system
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
Export done in WE8ISO8859P9 character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users .

. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TOXFIRE_TO_TSFDA
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TOXFIRE_TO_TSFDA
About to export TOXFIRE_TO_TSFDA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 25153 encountered
ORA-25153: Temporary Tablespace is Empty
EXP-00000: Export terminated unsuccessfully

'Parfile' contents :

owner=TOXFIRE_TO_TSFDA
file=exp_TOXFIRE_TO_TSFDA_QAR808.dmp
consistent=y
direct=y
recordlength=65535
log=exp_TOXFIRE_TO_TSFDA_QAR808.log

What is the reason, and It is saying that temporary tablespace is empty ( temp tablespace have almost free space ) why it is saying like that? And users default temp tablespace is that tablespace only ( database have only on temp tablespace

View 1 Replies View Related

SQL & PL/SQL :: Empty Space Creation

May 29, 2013

While i am inserting data into a specific table that table assigns empty spaces.

For example:
CREATE TABLE DEALER_MST
("DEALER_CODE" CHAR(10) NOT NULL ENABLE,
"REGION_CODE" CHAR(3),
"SUB_REGION_CODE" CHAR(3),
"DEALER_NAME" VARCHAR2(80),

[Code]....

But it is giving result as DS with 8 spaces. It assigns spaces in db automatically while fetching.

View 8 Replies View Related

Forms :: Empty Element In Item_list

Jan 13, 2013

I used a dynamic item_list. This list should contain only the result of the query but I found an empty element in the list. So, how to eliminate this element empty list?

see the picture:

[URL]........

View 4 Replies View Related

Forms :: Loop Insert Empty Row?

Jul 3, 2010

there this is my insert loop but the problem is there is row added more than the normal count ..

DECLARE
var_record_count NUMBER;
var_p_request NUMBER;

[Code]....

View 4 Replies View Related

SQL & PL/SQL :: Load Data Into Empty Table?

Jul 25, 2011

There is one table with data in ORCL1 database.I have created the table using create table statement in ORCL2 database.Now i want to insert only the data into table.

I know one method ... drop the table i created using drop table statement and then create the table with data using export/import.

Is there any other way we can load data into empty table?

View 29 Replies View Related

SQL & PL/SQL :: How To Check If Result Of Cursor Is Empty

May 20, 2010

How can i check if the result of a defined cursor is empty?

View 6 Replies View Related

SQL & PL/SQL :: Update Remaining Empty Columns

Dec 20, 2010

My requirement is that under table name like A I have to update remaining empty columns as A and under B remaining null columns as B and so on.

View 14 Replies View Related

PL/SQL :: External Table With Empty File

Oct 9, 2013

My db version: Oracle 11g I have an empty csv file.I created a external table for the empty csv file.When I run:select count(*) from externaltblname;It returns 1. It should return 0 right. In the definition, I specified "SKIP 1"But still it returns 1. When I use this external table to load into a target table. It loads a single row with null values.How to fix this.

View 2 Replies View Related

Empty Blocks In First Place On Loading A Table

Jan 4, 2011

what empty blocks are, and how to remove them.What I'd like to do is not have empty blocks in the first place on loading a table. I load a lot of "static" tables and would like to not have any wasted space at the end, with minimal shinanigans.

I've set pctfree 0
I"ve set initial to close to the end table size
I've set next to 1M
I've set pctincrease 0
blocksize is 8k

Yet I still need to at least do an alter table deallocate unused

View 8 Replies View Related

SQL & PL/SQL :: Empty Text File With Data In UNIX

Apr 14, 2010

I have text file with data in UNIX. I want to clear only records(make empty the file) using UTL_FILE..

I know we can remove the text file from UNIX and create file again with same name...

Is there any other way??

View 5 Replies View Related

SQL & PL/SQL :: Checking If Cursor Is Returning Empty List?

Sep 24, 2010

I am select list of ids;

cursor c1 is
select c_id from ctab1 where login = l_id
union
select c_id from ctab2 where login = i_id;

now I need to return err_code and err_message if this combined list is empty ! Can not use exit%notfound.

View 5 Replies View Related

Server Administration :: How To Get Number Of Empty Block

Mar 31, 2012

①SQL> SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME='T2012';
OBJECT_ID
---------
57082

②SQL> SELECT HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME = 'T2012';
HEADER_BLOCK BLOCKS
------------- --------
683 8

③SQL> SELECT DBMS_ROWID.rowid_block_number(ROWID)USED_BLOCK_NUMBER FROM SCOTT.T2012;
USED_BLOCK_NUMBER
----------------
684

④SQL> SHUTDOWN IMMEDIATE;

⑤SQL> STARTUP;

⑥SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD = '57082';
no data found

⑦SQL> SELECT * FROM SCOTT.T2012;
ID
-----
1

⑧SQL> SELECT BLOCK#,CLASS# FROM V$BH WHERE OBJD='57082';
BLOCK# CLASS#
------- ----------
686 1
684 1
687 1
685 1
688 1
683 4

⑨SQL> SELECT EMPTY_BLOCKS FROM DBA_TABLES WHERE TABLE_NAME='T2012';
EMPTY_BLOCKS
------------
3

QUESTIONS ONE:
in the ⑧ step,why block#685,block#686,block#687,block#688 in the buffer cache after i query data from scott.T2012?

QUESTIONS TWO:
in the ⑨ step,what's the block number of the empty block?just like DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID).

View 2 Replies View Related

Forms :: 10g Show Empty Screen In Browser?

Jun 29, 2012

When I try to run the form by clicking Program/Run form, it opens a webpage(explorer) and displays ?ORACLE FORMS? label, but does not show the form only blank screen appear. When I try to run the form by clicking Program/Run form, it opens a webpage(Firefox), but does not show the form only blank screen appear.

I have noticed that in Start OC4J Instance it has some thing like as below:

SYSDATE TIME FormsServlet init():
configFileName: E:DevSuiteHome_2/forms/server/formsweb.cfg
testMode: false

pop-ups is not blocked in my browser I have allow active content to run in files on your computer.directory name (where the form is stored) also doesn't contain any spaces.

Also I have change the

baseHTMLjinitiator=basejini.htm
to this
baseHTMLjinitiator=basejpi.htm.

I have done all the things mention in this forum to overcome this error.But, still the same problem is there.Also please take a note that I am using JRE not JINITIATOR. Beacuse when I am using the JINITIATOR it give me the problem of browser crash in both IE and firefox.Also I am using Windows XP and oracle form 10g.

View 9 Replies View Related

Forms :: Checking To See If A Text Item Is Empty

Aug 8, 2010

I was wondering can i check if a text item in another block is empty when a user try to create a record using a button in another block.

/*creating a program that will check to see if the customer id in the customer block is null it will then display the the stop alert and tell the user that he/she cant create a new order without having a selected a customer*/

--this is the block_item im checking to see if it null
IF (:customer.c_id) = NULL THEN
DISPLAY_ORD_STOP_ALERT;
GO_ITEM('CUSTOMER.C_ID');
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Email With Attachment / How To Remove Empty Line

Jun 22, 2010

I need to send email notifications with attachments. Below is my testing codes.

declare
text varchar2(32000);
crlf VARCHAR2(2) := chr(13)||chr(10);
begin
text := 'Test only'||crlf||'test 2'||crlf||'test 3';
UTL_MAIL.SEND_ATTACH_VARCHAR2(
[code]......

I notice that, there is an empty line at the top of the content of this file. I don't want the empty line to be included in my file.

View 4 Replies View Related

Drop Index Tablespace - File Is Non-empty

Oct 15, 2012

db 10.2.0.4
AIX 5.2

I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.

SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

try to resize

SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Tablespace size is 280gb

View 32 Replies View Related







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