How to convert MS access IFF statement to SQL server query..any help will be really appreciated.

Question

0.00/5 (No votes)

See more:

IIf([group_code]="NA","Not Discountable",
"Discountable - "+[GROUP_DESCRIPTION])
AS [SOC_DISCOUNT_GROUP Desc],
"Active (A)" AS [STATUS Desc],

IIf([data_bucket_mb] Is Null,[DATA_INCLUDED],
IIf([data_bucket_mb]=9999999,"Unlimited",
IIf([data_bucket_mb]=0,"0",
IIf([data_bucket_mb]<1024,
Trim([DATA_BUCKET_MB])+"MB",Trim(Round([DATA_BUCKET_MB]/1024,2))

What I have tried:

I need to convert it with Case statement.

Posted 21-Feb-20 9:20am

Member 14751855

Updated 21-Feb-20 10:41am

Add a Solution

Comments

ZurdoDev 21-Feb-20 16:05pm

Very simple. Case, or COALESCE, or whatever. And they are not all the same. Where are you stuck?

Member 14751855 21-Feb-20 16:12pm

IIf([group_code]="NA","Not Discountable",
"Discountable - "+[GROUP_DESCRIPTION])
AS [SOC_DISCOUNT_GROUP Desc],

In Sql Query--
Case
when group_code]="NA"
then "Not Discountable"
Else "Discountable - "+[GROUP_DESCRIPTION]
end
[SOC_DISCOUNT_GROUP Desc]
Is this correct? I have confusion only at Else part...where to add +[GROUP_DESCRIPTION]? what plus sign indicate?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2020-02-21T10:42:00

I would rely on two things:
- Documentation
- Trying to run the statement in SSMS.
This way you can see what is the expected format and functionality on each statement and have a try with it

What comes to documentation, have a look at
- CASE (Transact-SQL) - SQL Server | Microsoft Docs[^]
- + (String Concatenation) (Transact-SQL) - SQL Server | Microsoft Docs[^]
or CONCAT (Transact-SQL) - SQL Server | Microsoft Docs[^]
- ROUND (Transact-SQL) - SQL Server | Microsoft Docs[^]
- CAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Docs[^]

So based on those your query should be correct what comes to the CASE part, something like

SQL

SELECT
...
   CASE 
      WHEN [group_code]='NA' THEN 'Not Discountable'
      ELSE 'Discountable - ' + [GROUP_DESCRIPTION]
   END AS [SOC_DISCOUNT_GROUP Desc],
   'Active (A)' AS [STATUS Desc],
   CASE
      WHEN [data_bucket_mb] IS NULL THEN [DATA_INCLUDED]
      WHEN [data_bucket_mb] = 9999999 THEN 'Unlimited'
      WHEN [data_bucket_mb] = 0 THEN '0'
      WHEN [data_bucket_mb] < 1024 THEN CAST([DATA_BUCKET_MB] AS varchar(100)) + "MB"
      ELSE CAST(ROUND( ROUND([DATA_BUCKET_MB]/1024,2)) AS varchar(100)
   END
...