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 9 1784
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
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
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
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
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 >
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 >
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 >> > >
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 >> > >
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 >>> >> >> > > This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: J |
last post by:
Is there anyway to format a calculated datacolumn? I would like the
following column to display as money (formatted as $#,##0.00). Or how about
simply displaying the column formatted as a number...
|
by: Chris |
last post by:
I'm having trouble Serializing a System.Data.DataColumn
object. When I try to serialize it, I get the following:
System.NotSupportedException: Cannot serialize member...
|
by: Azkaban |
last post by:
Hi I succeded to add a DataColumn to DataSet but now I've one big problem.
The value of this column would be a personal function result, I try a lot of
time but the result is always empty.
Why?...
|
by: bordsby |
last post by:
Situation:
I am using an OleDbDataAdapter to fill a DataSet's DataTable with data
from an Access database. The DataSet's DataTable is bound to a
DataGrid. After the OleDbDataAdapter.Fill method...
|
by: Oscar Patarroyo Velasco |
last post by:
To calculate Logarithm within DataColumn.Expresion = "Log10(23)"
--
Oscar Patarroyo Velasco
Sistemas y Asesorias de Colombia
Bogotá, Colombia
|
by: mfunkmann |
last post by:
Hi,
I recently got an error and I don't know how to fix it:
Error 1 'System.Data.DataColumn' does not contain a definition for
'Windows' C:\c#\CsharpPRO\Form1.Designer.cs 304 77 CsharpPRO
I...
|
by: Matt F |
last post by:
I'm trying to do something that seems like it should be pretty simple, but
haven't found a solution. I am trying to add a datacolumn to a datatable
that adds or subtracts a number of days based on...
|
by: DaveP |
last post by:
im Trying to set a Datacolumn type for a new table
boolean is not working, What is the correct waty
//column.DataType = System.Type.GetType"System.Data.SqlTypes.SqlBoolean");
column.DataType...
|
by: SMH |
last post by:
Hi All,
I am currently learning .Net 2, studying for 70-528. I've hit a bit of
a brick wall with DataColumn.Expression. As I understand it, this can
be used to (For example) concatenate two...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |