|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Q: DataColumn ExpressionsHi
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. -------------------- Show quoteHide quote "G .Net" <nodamnspam@email.com> wrote in message news:rpadnaJ-z5QFpR3YRVnyiQA@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. Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message > news:rpadnaJ-z5QFpR3YRVnyiQA@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. ------------------------------------ Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message > news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >> news:rpadnaJ-z5QFpR3YRVnyiQA@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
Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message > news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>> news:rpadnaJ-z5QFpR3YRVnyiQA@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 Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:%23RhRJz0HHHA.420@TK2MSFTNGP02.phx.gbl... > F1 ... DataColumn ... Members ... Expression > > > "RobinS" <RobinS@NoSpam.yah.none> wrote in message > news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message >> news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >>> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>>> news:rpadnaJ-z5QFpR3YRVnyiQA@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. ------------------ Show quoteHide quote "G .Net" <nodamnspam@email.com> wrote in message news:tt6dneer_MrkYh3YnZ2dnUVZ8ta3nZ2d@pipex.net... > Thanks guys; actually Robin asnwered the question with his first > response. > > Merry Christmas! > > G > > "Stephany Young" <noone@localhost> wrote in message > news:%23RhRJz0HHHA.420@TK2MSFTNGP02.phx.gbl... >> F1 ... DataColumn ... Members ... Expression >> >> >> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >> news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message >>> news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >>>> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>>>> news:rpadnaJ-z5QFpR3YRVnyiQA@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. ----------------------- Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:%23RhRJz0HHHA.420@TK2MSFTNGP02.phx.gbl... > F1 ... DataColumn ... Members ... Expression > > > "RobinS" <RobinS@NoSpam.yah.none> wrote in message > news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message >> news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >>> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>>> news:rpadnaJ-z5QFpR3YRVnyiQA@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. Show quoteHide quote "RobinS" <RobinS@NoSpam.yah.none> wrote in message news:6N2dnZ4Uu_EolhzYnZ2dnUVZ_sapnZ2d@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@localhost> wrote in message > news:%23RhRJz0HHHA.420@TK2MSFTNGP02.phx.gbl... >> F1 ... DataColumn ... Members ... Expression >> >> >> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >> news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message >>> news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >>>> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>>>> news:rpadnaJ-z5QFpR3YRVnyiQA@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. ------------------------------ Show quoteHide quote "Stephany Young" <noone@localhost> wrote in message news:uyGUs61HHHA.4992@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" <RobinS@NoSpam.yah.none> wrote in message > news:6N2dnZ4Uu_EolhzYnZ2dnUVZ_sapnZ2d@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@localhost> wrote in message >> news:%23RhRJz0HHHA.420@TK2MSFTNGP02.phx.gbl... >>> F1 ... DataColumn ... Members ... Expression >>> >>> >>> "RobinS" <RobinS@NoSpam.yah.none> wrote in message >>> news:66adnb6JjfrIdB3YnZ2dnUVZ_v2nnZ2d@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@localhost> wrote in message >>>> news:OxeCFczHHHA.4068@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" <RobinS@NoSpam.yah.none> wrote in message >>>>> news:zdCdnRjGw6rrDh3YnZ2dnUVZ_oernZ2d@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" <nodamnspam@email.com> wrote in message >>>>>> news:rpadnaJ-z5QFpR3YRVnyiQA@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 >>>>>>> >>>>>> >>>>>> >>>>> >>>>> >>>> >>>> >>> >>> >> >> > > 
Other interesting topics
What is WebBrowser control called now?
Manipulating controls created by another thread Estimates on money lost because of VB.NET The below snippet does not work...anyone know how to reference a procedure? Form1.closing in VB2005??? DateTime Q: DataView with Table with large number of rows Services Grants Help referencing control backgorund image Great Reasons To Learn VB.NET - PART 1 |
|||||||||||||||||||||||
