BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

[BOBJ 5.x] Usage of CASE statement with @prompt function
1 members found this topic helpful

 
Search this topic... | Search Desktop Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Author Message
pkbapatla
Forum Member
Forum Member



Joined: 15 Jan 2009

Posts: 7



PostPosted: Thu Jan 15, 2009 9:36 am 
Post subject: Usage of CASE statement with @prompt function

I have been facing problem in writing case statement in the Business objects query:

requirement is:
------------------
Here is a column called country which is having below values:

'UK'
'United Kingdom'
'UNITED KINGDOM'
'UNITEDKINGDOM'
'u kingdom'
'UNT KINGD'
'India'

now i want to show only 2 values in the prompts as 'UK' and 'India'.

If user selects 'UK', then it should fetch all the related records to UK i.e except india.
And if user selects 'India' it should fetch the records related to 'India'

To achieve this functionality, I have written the below query, which is not working. It is saying that there is a syntax error.
Code:
country.country_name in
case @Prompt('1.Enter country name','A',{'UK','India'},multi,free)
when 'UK' then ('UK','United Kingdom','UNITED KINGDOM','UNITEDKINGDOM','u kingdom','UNT KINGD')
when 'India' then ('India')
end


Your swift response is highly appreciated. Kindly help to sort out this issue. Thanks alot in advance.
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17996
Location: Bratislava


flag
PostPosted: Thu Jan 15, 2009 10:08 am 
Post subject: Re: Usage of CASE statement with @prompt function

Welcome to Bicon_mrgreen.gifB!

Give this a try:
Code:
CASE WHEN country.country_name IN ('UK','United Kingdom','UNITED KINGDOM','UNITEDKINGDOM','u kingdom','UNT KINGD') THEN 'UK'
     WHEN country.country_name IN ('India') THEN 'India'
END = @Prompt('1.Enter country name','A',{'UK','India'},mono,free)

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
pkbapatla
Forum Member
Forum Member



Joined: 15 Jan 2009

Posts: 7



PostPosted: Thu Jan 15, 2009 10:32 am 
Post subject: Re: Usage of CASE statement with @prompt function

Thanks for the response, Marek.

Your idea sounds good but unfortunately it is not working. It is giving some syntax related error which i have been attaching along with his response.

Can I have any other resolutions for this, please ?
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17996
Location: Bratislava


flag
PostPosted: Thu Jan 15, 2009 11:08 am 
Post subject: Re: Usage of CASE statement with @prompt function

OK, it looks that you use MS Access as a database. I don't think CASE WHEN syntax is supported in Access nonod.gif You need to find an equivalent function.
_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
pkbapatla
Forum Member
Forum Member



Joined: 15 Jan 2009

Posts: 7



PostPosted: Thu Jan 15, 2009 11:10 am 
Post subject: Re: Usage of CASE statement with @prompt function

No.

I have been using oracle database and not the MS Access.
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17996
Location: Bratislava


flag
PostPosted: Thu Jan 15, 2009 11:27 am 
Post subject: Re: Usage of CASE statement with @prompt function

Then why does the error message say something about ODBC Microsoft Access driver? Do you use such driver to connect to the Oracle database?

Check the universe connection what kind of middleware (ODBC, Oracle client) is used.

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
pkbapatla
Forum Member
Forum Member



Joined: 15 Jan 2009

Posts: 7



PostPosted: Mon Jan 26, 2009 9:40 am 
Post subject: Re: Usage of CASE statement with @prompt function

You are right !!! When i try the same on an oracle based universe, it is working.
Thanks for the resolution.
Back to top
businessobjects
Forum Member
Forum Member



Joined: 02 Jul 2004

Posts: 6



PostPosted: Fri Mar 13, 2009 11:19 pm 
Post subject: Re: Usage of CASE statement with @prompt function

I have similar requirement where my SELECT clause for test case object is defined as
(CASE WHEN (Product.STATUS = 'S') THEN 'Shipped' ELSE CASE WHEN (Product.STATUS ='A') THEN 'Arrived' ELSE CASE WHEN (Product.STATUS ='D') THEN 'Delievered' END END END)

I edited List of values to include ALL , then I defined a @prompt condition with below code
Code:
Product.STATUS  IN (CASE WHEN  @Prompt('Select  Status','A','Product\test case',Multi,Free)
='Arrived'  THEN 'A' ELSE
  CASE WHEN @Prompt('Select  Status','A','Product\test case',Multi,Free)
='Shipped'  THEN 'S' ELSE
   CASE WHEN @Prompt('Select  Status','A','Product\test case',Multi,Free)
='Delivered'  THEN 'D' ELSE END END END)
OR 'ALL' In @Prompt('Select  Status','A','Product\test case',Multi,Free)

@Prompt gives me invalid relational operator ora 00920 error banghead.gif Can someone provide a solution syntax to usage of CASE statement that includes @prompt with multi value?

thanks in advance
Back to top
Marek Chladny
Forum Advocate
Forum Advocate



Joined: 27 Nov 2003

Posts: 17996
Location: Bratislava


flag
PostPosted: Fri Mar 13, 2009 11:47 pm 
Post subject: Re: Usage of CASE statement with @prompt function

Hi,

Try doing it the other way around - first apply CASE to the Product.STATUS column and then compare it to the @prompt function:
Code:
CASE WHEN Product.STATUS = 'A' THEN 'Arrived'
     WHEN Product.STATUS = 'S' THEN 'Shipped'
     WHEN Product.STATUS = 'D' THEN 'Delivered'
END IN @Prompt('Select Status','A','Product\test case',Multi,Free)
OR
'ALL' IN @Prompt('Select Status','A','Product\test case',Multi,Free)

_________________
BO: BI 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x
DB: Oracle, MS SQL Server, DB2, Teradata, Netezza
HW: Win and Linux servers
www.perpendulum.com

Latest Blog Posts
• 2013-07-16 Number of reports in a WebI document
• 2013-04-01 Optional prompts in a universe
• 2012-06-08 Calendar table script for Oracle
Back to top
businessobjects
Forum Member
Forum Member



Joined: 02 Jul 2004

Posts: 6



PostPosted: Mon Mar 16, 2009 7:28 pm 
Post subject: Re: Usage of CASE statement with @prompt function

Thank You Marek, It worked!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0559 seconds using 17 queries. (SQL 0.0119 Parse 0.0345 Other 0.0095)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo