460,022 Members | 1,398 Online
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
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"

 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" HiCan anybody help with a problem with Expressions for DataColumnsSuppose I have a table with three fields A, B and C.I want an expression in column C so thatC = 2 * A if A is not nullC = 3 * B otherwiseCan anybody help with the syntax?Thanks in advanceG 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" 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" >HiCan anybody help with a problem with Expressions for DataColumnsSuppose I have a table with three fields A, B and C.I want an expression in column C so thatC = 2 * A if A is not nullC = 3 * B otherwiseCan anybody help with the syntax?Thanks in advanceG Dec 14 '06 #4

 P: n/a F1 ... DataColumn ... Members ... Expression "RobinS" 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 itis not null) and B must be numeric, therefore you must use somenon-conflicting token to indicate a psuedo-value for A ('Z').This is because IsNull(expression, replacementvalue) returns either thevalue for A (if it is not null) or the specified replacement value (if itis 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 astring 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 isa string, but you need to remember that the result of the IsNull() may benumeric.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 theIIF() to ensure that it will always be evaluatable. "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.--------------------"G .Net"

 P: n/a Thanks guys; actually Robin asnwered the question with his first response. Merry Christmas! G "Stephany Young" So is this something that would be part of a query? Orto define a datacolumn in a dataset? Since apparentlyI missed the boat with my answer, can you tell me whatthis is used for? I'd appreciate it; I'm always lookingto learn something new.Thanks,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 itis not null) and B must be numeric, therefore you must use somenon-conflicting token to indicate a psuedo-value for A ('Z').This is because IsNull(expression, replacementvalue) returns either thevalue for A (if it is not null) or the specified replacement value (ifit 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 astring 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 onfalse (A is not null).It might seem strange to convert 'Z' to a string seeing as it already isa string, but you need to remember that the result of the IsNull() maybe 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 theIIF() to ensure that it will always be evaluatable. "RobinS" 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 nullC = 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" So is this something that would be part of a query? Orto define a datacolumn in a dataset? Since apparentlyI missed the boat with my answer, can you tell me whatthis is used for? I'd appreciate it; I'm always lookingto learn something new.Thanks,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 usesome non-conflicting token to indicate a psuedo-value for A ('Z').This is because IsNull(expression, replacementvalue) returns eitherthe value for A (if it is not null) or the specified replacementvalue (if it is null). You can't use to return some otherconditional 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' inthe IIF() which now allows you to return 3* B on true (A is null) or2 * A on false (A is not null).It might seem strange to convert 'Z' to a string seeing as italready is a string, but you need to remember that the result of theIsNull() 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 ofthe IIF() to ensure that it will always be evaluatable. "RobinS" 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 nullC = 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" F1 ... DataColumn ... Members ... Expression "RobinS" >So is this something that would be part of a query? Orto define a datacolumn in a dataset? Since apparentlyI missed the boat with my answer, can you tell me whatthis is used for? I'd appreciate it; I'm always lookingto learn something new.Thanks,Robin S.------------------------------------"Stephany Young" 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" 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" F1 ... DataColumn ... Members ... Expression "RobinS" >So is this something that would be part of a query? Orto define a datacolumn in a dataset? Since apparentlyI missed the boat with my answer, can you tell me whatthis is used for? I'd appreciate it; I'm always lookingto learn something new.Thanks,Robin S.------------------------------------"Stephany Young" 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" 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" Ouch!I tried that before posting the question back, but askedfor "expressions datacolumns" instead of "datacolumnsexpressions" and got a bunch of stuff about ReportingServices in SQLServer and MDX and XQuery and usingexpressions instead of Triggers and Expressionsin TransactSQL.I couldn't get anything out of MSDN (seems to be having aproblem).That's the only reason I posted the question. I figuredsome kind soul would give me a one-liner or just a "yesno yes" answer to my q's. I didn't realize it would beso much trouble. When I go back and search on the keywordsin the reverse order, I get more information. So never mind.Have a nice day.Robin S.-----------------------"Stephany Young" >F1 ... DataColumn ... Members ... Expression "RobinS" 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 usesome non-conflicting token to indicate a psuedo-value for A ('Z').>This is because IsNull(expression, replacementvalue) returnseither the value for A (if it is not null) or the specifiedreplacement value (if it is null). You can't use to return someother 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 isnull) or 2 * A on false (A is not null).>It might seem strange to convert 'Z' to a string seeing as italready is a string, but you need to remember that the result ofthe 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 partof the IIF() to ensure that it will always be evaluatable.>>"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.>-------------------->"G .Net" 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.