Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 25th, 2009, 02:53 AM
Registered User
Points: 1,897, Level: 17
Points: 1,897, Level: 17 Points: 1,897, Level: 17 Points: 1,897, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: Mumbai, Maharastra, India.
Posts: 352
Thanks: 14
Thanked 0 Times in 0 Posts
Send a message via Yahoo to rupen
Default CASE in where clause.

Hi,
wanted to include where clause conditionally (depends on variable value passed to procedure)

Code:
select main_acnt_code as code, main_acnt_name as name
from fm_main_account
where MAIN_FRZ_FLAG = 'N'
and case in_Code
        when 'CAPEX' THEN main_class_code_05 = 'Y';
        when 'MAIN_ACC2' THEN main_class_code_02 = 'Y';
     end case;
but error reads as "missing keywords " at line "when'CAPEX'THENmain_class_code_05 = 'Y';"

any idea how can we make select statement's where clause dynamic ??
  #2 (permalink)  
Old August 25th, 2009, 04:53 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

You can re-write the SQL as follows:
select main_acnt_code as code, main_acnt_name asname
from
fm_main_account
where MAIN_FRZ_FLAG ='N'
and
((in_Code ='CAPEX'AND main_class_code_05 ='Y')
OR(in_Code ='MAIN_ACC2'AND main_class_code_02 ='Y'))
__________________
Om Prakash Pant
Click the "Thanks" button if this post helped you.
The Following User Says Thank You to om_prakash For This Useful Post:
rupen (August 27th, 2009)
  #3 (permalink)  
Old August 27th, 2009, 04:39 AM
Registered User
Points: 1,897, Level: 17
Points: 1,897, Level: 17 Points: 1,897, Level: 17 Points: 1,897, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Apr 2005
Location: Mumbai, Maharastra, India.
Posts: 352
Thanks: 14
Thanked 0 Times in 0 Posts
Send a message via Yahoo to rupen
Default

What an idea sirji....Thank you...


Similar Threads
Thread Thread Starter Forum Replies Last Post
Case in Where Clause acko SQL Server 2000 12 September 12th, 2011 01:43 PM
CASE WHEN WHERE CLAUSE deontae45 SQL Server 2000 2 January 11th, 2011 10:03 AM
Case in Where Clause demiwolf SQL Server 2000 3 March 23rd, 2006 12:45 PM
case clause in query mateenmohd SQL Server 2000 2 February 22nd, 2005 05:49 AM
case clause in Access mateenmohd Access 19 May 6th, 2004 03:54 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.