By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,022 Members | 1,398 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,022 IT Pros & Developers. It's quick & easy.

Q: DataColumn Expressions

P: n/a
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
Dec 13 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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

Dec 14 '06 #2

P: n/a
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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.com. ..
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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


Dec 14 '06 #3

P: n/a
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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.com. ..
>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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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



Dec 14 '06 #4

P: n/a
F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.com. ..
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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
>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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.com ...
>>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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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



Dec 14 '06 #5

P: n/a
Thanks guys; actually Robin asnwered the question with his first response.

Merry Christmas!

G

"Stephany Young" <noone@localhostwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.com. ..
>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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
>>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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.co m...
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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
>




Dec 14 '06 #6

P: n/a
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.
-----------------------
"Stephany Young" <noone@localhostwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.com. ..
>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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
>>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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.co m...
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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
>




Dec 14 '06 #7

P: n/a
I'm glad I could help. You have a merry Christmas too!

Robin S.
------------------
"G .Net" <no********@email.comwrote in message
news:tt******************************@pipex.net...
Thanks guys; actually Robin asnwered the question with his first
response.

Merry Christmas!

G

"Stephany Young" <noone@localhostwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
>F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.com ...
>>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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.c om...
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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
>>
>
>




Dec 14 '06 #8

P: n/a
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" <Ro****@NoSpam.yah.nonewrote in message
news:6N******************************@comcast.com. ..
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.
-----------------------
"Stephany Young" <noone@localhostwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
>F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.com ...
>>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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast.c om...
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.
--------------------
"G .Net" <no********@email.comwrote in message
news:rp*********************@pipex.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
>>
>
>




Dec 14 '06 #9

P: n/a
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.
------------------------------
"Stephany Young" <noone@localhostwrote in message
news:uy**************@TK2MSFTNGP04.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:6N******************************@comcast.com. ..
>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.
-----------------------
"Stephany Young" <noone@localhostwrote in message
news:%2***************@TK2MSFTNGP02.phx.gbl...
>>F1 ... DataColumn ... Members ... Expression
"RobinS" <Ro****@NoSpam.yah.nonewrote in message
news:66******************************@comcast.co m...
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" <noone@localhostwrote in message
news:Ox**************@TK2MSFTNGP03.phx.gbl...
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" <Ro****@NoSpam.yah.nonewrote in message
news:zd******************************@comcast. com...
>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.
>--------------------
>"G .Net" <no********@email.comwrote in message
>news:rp*********************@pipex.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
>>>
>>
>>
>
>




Dec 14 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.