Q: DataColumn Expressions

[G .Net]

Hi

Can anybody help with a problem with Expressions for DataColumns

Suppose I have a table with three fields A, B and C.

I want an expression in column C so that

C = 2 * A if A is not null
C = 3 * B otherwise

Can anybody help with the syntax?

Thanks in advance

G

 

[RobinS]

I'm not sure what you mean "Expressions for DataColumns".

If you mean in a query, try using an IIF statement.

Select A, B, IIF(A IS NULL, B * 3, A * 2) as C
FROM ...

Is that what you were looking for?

Robin S.

 

[Stephany Young]

No. The OP means "Expressions for DataColumns".

IIf(Convert(IsNull(A, 'Z'), 'System.String')) = 'Z', 3 * B, 2 * A)

Because you are doing multiplication on either A or B then both A (if it is
not null) and B must be numeric, therefore you must use some non-conflicting
token to indicate a psuedo-value for A ('Z').

This is because IsNull(expression, replacementvalue) returns either the
value for A (if it is not null) or the specified replacement value (if it is
null). You can't use to return some other conditional value.

You then convert the result (which will either be a numeric or 'Z') to a
string and use an equality comparison of that against 'Z' in the IIF() which
now allows you to return 3* B on true (A is null) or 2 * A on false (A is
not null).

It might seem strange to convert 'Z' to a string seeing as it already is a
string, but you need to remember that the result of the IsNull() may be
numeric.

All this assumes, of course, that B must never be null.

If B is allowed to be null then you need to re-work the true part of the
IIF() to ensure that it will always be evaluatable.

 

[RobinS]

So is this something that would be part of a query? Or
to define a datacolumn in a dataset? Since apparently
I missed the boat with my answer, can you tell me what
this is used for? I'd appreciate it; I'm always looking
to learn something new.

Thanks,
Robin S.
------------------------------------

 

[Stephany Young]

F1 ... DataColumn ... Members ... Expression

So is this something that would be part of a query? Or
to define a datacolumn in a dataset? Since apparently
I missed the boat with my answer, can you tell me what
this is used for? I'd appreciate it; I'm always looking
to learn something new.

Thanks,
Robin S.

 

[G .Net]

Thanks guys; actually Robin asnwered the question with his first response.

Merry Christmas!

G

 

[RobinS]

Ouch!

I tried that before posting the question back, but asked
for "expressions datacolumns" instead of "datacolumns
expressions" and got a bunch of stuff about Reporting
Services in SQLServer and MDX and XQuery and using
expressions instead of Triggers and Expressions
in TransactSQL.

I couldn't get anything out of MSDN (seems to be having a
problem).

That's the only reason I posted the question. I figured
some kind soul would give me a one-liner or just a "yes
no yes" answer to my q's. I didn't realize it would be
so much trouble. When I go back and search on the keywords
in the reverse order, I get more information. So never mind.

Have a nice day.
Robin S.
-----------------------

 

[RobinS]

I'm glad I could help. You have a merry Christmas too!

Robin S.
------------------

 

[Stephany Young]

As you can now see, the subject is very complex and there is no way that I
was going to regurgitate what it in here because it is is far better that
you read it in it's context.

 

[RobinS]

Fair enough. It didn't occur to me to reverse the search
parameters. I keep forgetting that MS isn't as good at
searching as Google. Silly me!

Robin S.
------------------------------