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

Register | Login 

Streamline and extend SAP BusinessObjects software 
Streamline and extend SAP BusinessObjects software (Opens a new window)  

General Notice: No events within the next 45 days.

Mod under SQL Server


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Sebastien Goiffon
Forum Fanatic
Forum Fanatic



Joined: 29 Sep 2004
ASUG Icon
Posts: 6374
Location: France


flag
PostPosted: Sat Jul 22, 2006 1:08 am 
Post subject: Mod under SQL Server

Hi all,

I use this line under an Oracle database :
Code:

decode(MOD(Number, 2),1,'True','False')


I thought the corresponding code under SQLServer was
Code:

CASE WHEN Number % 2 = 1 THEN 'True' ELSE 'False' END


But this don't work !

Thanks for answers !

Regards

_________________
360Suite: Security, backup, promotion, impact analysis, bursting, metadata solutions.
Spend 9 minutes to save HOURS. Alternative of CMC and LCM.
Back to top
MarcoR
Principal Member
Principal Member



Joined: 29 Mar 2006

Posts: 103
Location: Miami, FL


flag
PostPosted: Sat Jul 22, 2006 10:21 am 
Post subject: Re: Mod under SQL Server

It works for me (SQL Server 2000 SP4).
Code:
SELECT 4 % 2, CASE WHEN 4 % 2 = 1 THEN 'TRUE' ELSE 'FALSE' END -- returns 0, FALSE
SELECT 5 % 2, CASE WHEN 5 % 2 = 1 THEN 'TRUE' ELSE 'FALSE' END -- returns 1, TRUE
Can you be more specific on what kind of problem you are having with this code?
Back to top
Sebastien Goiffon
Forum Fanatic
Forum Fanatic



Joined: 29 Sep 2004
ASUG Icon
Posts: 6374
Location: France


flag
PostPosted: Sat Jul 22, 2006 11:14 am 
Post subject: Re: Mod under SQL Server

Hi

Thanks for feed back i'll check and tell you if i had a problem. I've got an error when i parse universe object. I'll try directly under my database.

Regards

_________________
360Suite: Security, backup, promotion, impact analysis, bursting, metadata solutions.
Spend 9 minutes to save HOURS. Alternative of CMC and LCM.
Back to top
Sebastien Goiffon
Forum Fanatic
Forum Fanatic



Joined: 29 Sep 2004
ASUG Icon
Posts: 6374
Location: France


flag
PostPosted: Sat Jul 22, 2006 9:05 pm 
Post subject: Re: Mod under SQL Server

Hi,

I had to cope with this error

Code:

Server: Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 1
Operand data type numeric is invalid for modulo operator.


just change this :
Code:

Case when Convert(int,Number) % 2 = 1 THEN 'True' ELSE 'False' END


Thanks

_________________
360Suite: Security, backup, promotion, impact analysis, bursting, metadata solutions.
Spend 9 minutes to save HOURS. Alternative of CMC and LCM.
Back to top
MarcoR
Principal Member
Principal Member



Joined: 29 Mar 2006

Posts: 103
Location: Miami, FL


flag
PostPosted: Sun Jul 23, 2006 11:36 am 
Post subject: Re: Mod under SQL Server

It works for, end-to-end. It parses on Designer and runs on FC (see attachment). It looks like you have some code error on your object definition. Can you post what is the data element is in the db, the exactly code on designer, including data type? What "Number" does mean? A placeholder for an integer object or the exactly code you are using? Please note that SQL Server only accepts integer as operand with modulo operator.
Code:
(From SQL Server Online Help):

% (Modulo)
Provides the remainder of one number divided by another.

Syntax
dividend % divisor

Arguments
dividend

Is the numeric expression to divide. dividend must be any valid Microsoft® SQL Server™ expression of the integer data type category. (A modulo is the integer that remains after two integers are divided.)

divisor

Is the numeric expression to divide the dividend by. divisor must be any valid SQL Server expression of any of the data types of the integer data type category.

Result Types
int

Remarks
The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

Examples
This example returns the book title number and any modulo (remainder) of dividing the price (converted to an integer value) of each book into the total yearly sales (ytd_sales * price).

USE pubs
GO
SELECT title_id,
   CAST((ytd_sales * price) AS int) % CAST(price AS int) AS Modulo
FROM titles
WHERE price IS NOT NULL and type = 'trad_cook'
ORDER BY title_id
Back to top
Sebastien Goiffon
Forum Fanatic
Forum Fanatic



Joined: 29 Sep 2004
ASUG Icon
Posts: 6374
Location: France


flag
PostPosted: Sun Jul 23, 2006 6:55 pm 
Post subject: Re: Mod under SQL Server

Hi,

Thanks for these informations. I'll check.
Now it works fine.

Thanks again

Regards

_________________
360Suite: Security, backup, promotion, impact analysis, bursting, metadata solutions.
Spend 9 minutes to save HOURS. Alternative of CMC and LCM.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  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.0464 seconds using 17 queries. (SQL 0.0032 Parse 0.0347 Other 0.0085)
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