Q: DataColumn Expressions

  • Thread starter Thread starter G .Net
  • Start date Start date
G

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
 
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.
 
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.
 
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.
------------------------------------
 
F1 ... DataColumn ... Members ... Expression


RobinS said:
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.
Click to expand...
 
Thanks guys; actually Robin asnwered the question with his first response.

Merry Christmas!

G
 
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.
-----------------------
 
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.
 
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.
------------------------------
 
Back
Top