Need help converting MS Access Syntax

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Need help converting MS Access Syntax

Author

Topic

RodgerDJr
Starting Member

4 Posts

Posted - 2013-12-17 : 15:15:58

Hello all,

I have written my far share of SP in SQL. I am starting to convert our Access database to SQL. I am working on one that has a field that needs to display both a percent as well as a whole number with some text next to it.

Here is what I have in Access
NATIONAL: IIf([VAL_NATIONAL]*[DAT_NUMBER]>1,[VAL_NATIONAL]*[DAT_NUMBER] & " " & [DAT_DISPLAY],Format([VAL_NATIONAL],"0%"))

So here is my attempt to convert this over.
CAST(CASE v.VAL_NATIONAL
WHEN >1 THEN v.VAL_NATIONAL * dt.DAT_NUMBER & ' ' & dt.DAT_DISPLAY
ELSE CAST(v.VAL_NATIONAL AS VARCHAR(50))
END AS VARCHAR(50)) AS 'NATIONAL',

If the number is over 1 then it is a whole number so I want to include the display for the number. For this example we will say it is for minutes. So I want to append that to the field so it might read 42 min. Otherwise I know the number is a percent and want to display the number as a percent such as 97%.

Any help will be much appreciated.

TIA,
Rodger

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:27:11

Just output the raw data and use the application code to do the conversion.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

RodgerDJr
Starting Member

4 Posts

Posted - 2013-12-17 : 15:28:58

I am not sure what you mean by that what application code?

RodgerDJr
Starting Member

4 Posts

Posted - 2013-12-17 : 15:30:59

OK I see you mean format it when I create the web page/report.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-17 : 15:32:54

Yes. Your issue is a presentation layer issue and should not be done in T-SQL. Return raw data wherever possible.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

RodgerDJr
Starting Member

4 Posts

Posted - 2013-12-17 : 15:35:48

OK Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources