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


ADVERTISEMENT

Case Statement In Where Clause

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

SQL & PL/SQL :: Case Statement In Where Clause

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

PL/SQL :: How To Use CASE Statement In Where Clause

May 16, 2013

How to use CASE stmt in WHERE clause?

View 3 Replies View Related

SQL & PL/SQL :: Syntax - Case Statement In Where Clause?

Mar 12, 2013

I am trying to use the following case statement in my where clause. My problem here is, I get no rows.

tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end

View 13 Replies View Related

SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause

Mar 28, 2011

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

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

Semantic Technologies :: User-defined Function In FILTER Clause?

Apr 28, 2013

can i create the user-defined functions and use them in the FILTER clause in the sem_match function? there are some built-in functions for the FILTER clasue. however, only one function (DATATYPE(literal)) support for date/time in the built-in functions. i want to implement some user-defined funcitons in the FILTER clause which can check time intervals in ontology. there are some functions about valid time in the WorkSpace Manager such as WM_OVERLAPS, WM_CONTAINS,WM_MEETS, etc. so, can i write some functions using the these valid time functions in WM and use them in the FILTER clause?

View 2 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 :: Case In Insert Clause?

Aug 30, 2011

is there a possible to use a case statement in insert clause?

View 5 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 :: Filter Each Row In A Select Statement Based Upon A List?

May 18, 2011

Is it possible to filter each row in a select statement based upon a list that you loop through. For instance in a where clause having an in statement but rather than selecting any value in the in statement looping the main select through each value in the "in" filter.

Eg:

CREATE TABLE TRANSAC(
Item_id number ,
Transaction_Date DATE,
Category_id number
)

You could then write

SELECT MAX(Transaction_Date)
from TRANSAC
where category_id = '141'

and then where category_id = '142' then '143' etc.

I understand i could use an inline view instead of a where clause but the issue is that i already have the list of values I want to compare against.

View 5 Replies View Related

Ignore Case In SQL Select With Where Clause

Oct 11, 2010

We have and Upper and a Lower function.Is there any function or way in which I can issue a select while ignoring the case(like IgNoReCaSe) in a where clause? I don't want to use like

select * from emp where upper(job)= upper('dba');
or
select * from emp where lower(job)= lower('dba');

I just want to know if there is any way in which I could do it without using upper and lower.

View 2 Replies View Related

Application Express :: Filter SQL Statement With Value From Session State

Feb 27, 2013

I need to filter a SQL statement with an application item value retrieved from session state. I am assuming I could do this using a Dynamic Action on a Page Load event but I'm not sure where to go from here. How could I append the filter to an existing report page SQL statement?

View 7 Replies View Related

Update With Case Clause Auto Commits

Nov 26, 2012

i tried the following update on one table:

update siebel.s_contact
set marital_stat_cd =
case
when (marital_stat_cd = 'Casado') then 'Married'
when (marital_stat_cd = 'Solteiro') then 'Single'
when (marital_stat_cd = 'Divorciado') then 'Divorced'
end

As you can see i forgot the else, so my update is wrong.

I thought i could rollback the update issuing the rollback statement, but when i have issue the rollback, the i query the table to confirm that the update was rollbacked and for my suprise the update is commited.

I didn�t issue the commit statement after the update and i confirmed that the auto-commit feature to worksheets is disabled, so i don�t understand whit the update was commited.

View 5 Replies View Related

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

View 5 Replies View Related

SQL & PL/SQL :: Using Sequence In CASE Statement

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

SQL & PL/SQL :: Select In Case Statement

Feb 11, 2011

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.

View 4 Replies View Related

SQL & PL/SQL :: Deduping Using CASE Statement

Jan 4, 2012

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

View 5 Replies View Related

SQL & PL/SQL :: NESTED CASE Statement

Jul 13, 2010

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.

View 8 Replies View Related

Forms :: Case Statement In 6i

Mar 24, 2011

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 Related

SQL & PL/SQL :: How To Use CASE In SELECT STATEMENT

Jun 24, 2011

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

View 4 Replies View Related

PL/SQL :: Case Statement - Getting Error?

Oct 24, 2013

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 Related

PL/SQL :: Case When Null Statement

Oct 26, 2012

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

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

SQL & PL/SQL :: Trying To Get Two Values From Case Statement

Jul 10, 2013

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

View 4 Replies View Related

SQL CASE STATEMENT With Wildcard Character

Feb 2, 2010

I am very new to Oracle APEX and having difficult with a SQL statement in a report page. I have the following sql code in the page region source.

Essentially, there are two drop down lists connected with the report. I want the first one (:P2_Retailer_ID) to be mandatory, i.e., the user has to select a value from the list, but the second one (:P2_JOBTYPES) to be optional.

So, if the user leaves :P2_JOBTYPES as "No Job Type Chosen" then the SQL returns all records where the retailer ID = P2_RETAILER_ID whereas, if the user also selects a value for P2_JOBTYPES then the SQL returns all records matching both on Retailer ID and Job Type.

However, for some reason, if the user leaves P2_JOBTYPES as "No Job Type Chosen" then no records are returned as it appears to be taking the '%' as a literal value rather than a wildcard.

SELECT * FROM RETPERFFW
Where "Retailer_No"= :P2_RETAILER_ID
AND (CASE
WHEN :P2_JOBTYPES <> 'No Job Type Chosen'
THEN :P2_JOBTYPES
ELSE '%'
END )LIKE "Job_Type_Schema"

View 2 Replies View Related

SQL & PL/SQL :: How To Enter Value At Run Time In Case Statement

Jun 3, 2010

The code written in PL SQL.I am using '&' operator to enter the values at run time in nested case statements.But when I chosen a value for case statement even though it is asking all the values which are not part of the selected case statement.I need only the values related to my choice in case statement.

choice1:= &CHOICE_NO1;
CASE(choice1)
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('** ENTER YOUR CHOICE TO PERFORM THE OPERATION: **');
DBMS_OUTPUT.PUT_LINE('** (1)INSERTION (2)DELETION (3) UPDATION **');
[code]....

that is my exact code.when I enter value 1 for choice1 and 2 for choice2 It should ask the values only for student_ scorecard. delete_ record (& STUDENTID); &studentid. But it is asking all the functions (student_scorecard.existing_marks(&ID,&SUBJECTNAME));(student_ scorecard. std_ major_ name (&STD_ID)) and procedures.I dont want that.

View 4 Replies View Related

SQL & PL/SQL :: Case Statement With Multiple Parameters?

Apr 12, 2012

I am trying to put transactions into 3 different "buckets" for each month of the year.

I am trying to figure out if the syntax for the "else" part of this code is correct... this will basically appear 12 times for each month of the year changing the value for month:

case when floor(sum(total_adjusted_net_amount)/100) < 0 and month in (01) then 0
when floor(sum(total_adjusted_net_amount)/100) > 29 and month in (01) and then 30
else when floor(sum(total_adjusted_net_amount)/100) and month in (01) end Groupin_01

View 3 Replies View Related

SQL & PL/SQL :: Adding Conditions In CASE Statement?

Oct 28, 2010

I am trying to use a CASE statement

CASE WHEN (EP.ORDER_NUMBER IS NOT NULL AND ATD.IS_DISCONNECT_CREDIT = 1 ) THEN
.........
......
END

In my case when I club two conditions after WHEN in case statement , everytime the first condtion is satisfied (i.e EP.ORDER_NUMBER IS NOT NULL) it is entering into the loop.

Irrespective of using AND or OR after the first condition I am getting the same results.

I want to enter the CASE statement only when both the conditions (1st and 2nd )are satisfied

View 10 Replies View Related







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