Aritmectic Operator in Formula?

Hi all,

this formula doesn't work as calculated column but WHY?

Column [MAIN]: VARCHAR(150)

VALUE : 'blabla - zobzob'

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])) WORKS and return 'blabla -'

BUT

SUBSTRING([MAIN],1,CHARINDEX('-',[MAIN])-2) DOESN'T WORK in a Formula BUT well in a select clause...

Any Idea?

Thanks

In what way does it 'not work'...?

It seems to work ok... (SQL Server 2000)

create table #x (main varchar(20) not null)

insert #x select 'blabla - zobzob'

alter table #x add b as SUBSTRING(MAIN,1,CHARINDEX('-',MAIN)-2)

select * from #x

drop table #x

main b
-- --
blabla - zobzob blabla

/Kenneth

|||

Yes indeed, it works with a sql script but NOT manually in SQL Server 2000... Very Strange...

Tks a lot

|||

Hmm.. what do you mean by 'manually'..?

In my mind T-SQL code in QA is as 'manual' as you can get..?

/Kenneth

|||With the editor provided in SQL Server 2000...|||

You mean Enterprise Manager?

I'd suggest as a solution that you don't use it for other purposes than viewing.
For one thing it doesn't allow you any transactional control when you perform write operations, and some things it does in an awkward way, which adds up to that it takes unnecessary long time and/or large amount of resources to complete.

Make the wrong choice in EM and chances increase that you're looking for the latest good backup, instead of just typing ROLLBACK in QA, when something unexpected happened with the latest change. This ability is due to the fact that you can start your scripts with BEGIN TRAN (don't forget it) before actually performing the change. This is something that EM doesn't give you. =:o/

EM is imo not a fitting tool for database change management. (perhaps with the exception of user and roles management)

Do all your (data/schema) changes in Query Analyzer by controlled scripts instead.

/Kenneth

Arithmetic operator

I'm using Sql Server reporting tools and creating a report.
How can I display the difference of two fields on a report?

you have 3 text boxes on a form..

say you have two fields...

textbox1.value = fields!myfield1.value

textbox2.value = fields!myfield2.value

then in the value for textbox3 put

= fields!myfield1.value - fields!myfield2.value

QED

Daryl

Aren't IIF statements supposed to be straight forward...

Hi

IIF statements are simple and I have had them work on other reports. But for the first time I'm using "Like" operator for string comparison and I can't get this to work!

For ease, my boolean condition is in blue color, and the green color field value is to be returned incase the condition is TRUE and the red color field value is to be returned incase the condition is FALSE.

=IIF(((First(Fields!well_type.Value, "WellAttributes")Like First(Fields!code.Value, "LookUpCodeTblAttributes"))And (First(Fields!code_type.Value, "LookUpCodeTblAttributes")Like "well type")),First(Fields!description.Value, "LookUpCodeTblAttributes"),"N/A")

Instead, its giving me the following error:

[rsCompilerErrorInExpression] The Value expression for the textbox ‘well_type’ contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.

Build complete -- 1 errors, 2 warnings

If anyone has encountered a similar problem or an idea to resolve this. Please let me know - appreciate it.

Thanks

preps.

I used StrComp operator instead.

Syntax and usage for Like operator can be found at http://msdn2.microsoft.com/en-us/library/swf8kaxw.aspx

Cheers

preps