Need to test on a field to determine the value that is displayed in a result set. Can I do that in the SELECT...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
select memb.acct, memb.transamt, memb.name if memb.transactioncode < 100 then memb.transamt = '0' else memb.transactioncode = '100'
I realise that the syntax is not correct, but this is what I am trying to accomplish.
SQL includes the CASE statement, a powerful conditional structure in the SELECT list; however, not all databases support this yet.
I think there's a small error in your question; you are setting the transaction amount to 0 when the transaction code is less than 100, otherwise you are setting the transaction code to 100. I'm going to change this slightly just to illustrate the syntax --
select memb.acct , CASE WHEN memb.transactioncode < 100 THEN 0 ELSE 100 END as transamt , memb.name from memb
See also this previous answer for an example of conditional logic using the Microsoft Access Iif function.
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.