Convert Access Query to SQL

[Lstclair55]

Hi,

I am looking into to swapping one of access queries to a SQL view for better integration into our database viewer software app on the iPAD's.

However I am not familiar with the sql language.

How would I convert the following to a SQL view:

AskingPrice: IIf([pricequalify]="asking Price",Format([Price],"£#,##0"),[QualifyAb] & " " & Format([Price],"£#,##0"))

and

Viewer: IIf([tblPurchaser]![PPLastname]=[tblPurchaser]![LASTNAME],[tblPurchaser]![TITLE] & " and " & [tblPurchaser]![PPTitle] & " " & [tblPurchaser]![LASTNAME],[tblPurchaser]![TITLE] & " " & [tblPurchaser]![LASTNAME] & IIf(IsNull([tblPurchaser]![PPLastname]),""," and " & [tblPurchaser]![PPTitle] & " " & [tblPurchaser]![PPLastname])) & " / " & [mobiletel] & IIf(IsNull([PPMobile]),""," / " & [PPMobile])

Any help would be greatly appreciated.

[johnhutchison]

With any Access query you can look at it as SQL just by looking at the query in SQL view. How you do that varies a bit with the version of Access.

Here is where it is in Access 2010.
ViewSQL.gif

[WendellB]

I presume by "SQL view" you mean you are using data from another database engine than Access. In ANSI SQL you don't have the IIF() function that Access provides, so the typical solution is to use the CASE statement. Unfortunately the syntax may vary from vendor to vendor. So we need to know whether you are working with mySQL, Oracle, SQL Server or some other database backend.

[Lstclair55]

I am using Ms SQL 2005

[WendellB]

In order to convert those expressions to T-SQL, you need to use CASE statements in SQL Server 2005, but you will need to consult with the SQL Server Books Online to see the syntax for doing formatting currency as £ Sterling. The CASE statement is similar to the Select Case construct in Access VBA.

[iansavell]

If I may jump in here, I have spent ages converting Access SQL to the T-SQL used by MS SQL Server and I feel your pain Lstclair! As WendellB says, the big thing is to use CASE statements instead of IIF, but there are a load of common conversion issues illustrated by your question. So here goes with a long answer.

Your first expression is

AskingPrice: IIf([pricequalify]="asking Price",Format([Price],"£#,##0"),[QualifyAb] & " " & Format([Price],"£#,##0"))

And that can be put into a SQL view like this:

SET QUOTED_IDENTIFIER ON;

GO

CREATE VIEW vwSomethingOrOther AS
  SELECT
    CASE WHEN "pricequalify" = 'asking Price' THEN
      Format("Price",'£#,##0')
    ELSE
      "QualifyAb" + ' ' + Format("Price",'£#,##0')
    END AS AskingPrice,
    other_field_or_expression AS other_result_field
  FROM source_table_or_join_expression
  WHERE selection_criteria;

GO

That still won't work though, because T-SQL has no Format() function. See later.

The above illustrates several things:

1. The syntax for creating a view. Note that once created the view lives in the database like an Access query. If you want to change it later, replace CREATE with ALTER.

2. T-SQL uses double quotes not square brackets to "escape" identifiers. You only need to do that if your identifiers have spaces or other characters T-SQL doesn't allow. As yours don't, you could omit the double quotes and the SET QUOTED_IDENTIFIER.

3. If QUOTED_IDENTIFIER is ON, you must use single quotes for string literals. That is good practice anyway and it also works in Access. Note you can put double quotes inside a single quoted string literal without escaping them, but if you include an apostrophe you must escape it by using two apostrophes.

4. +, not &, is the string concatenation operator in T-SQL

5. Not illustrated above is the ISNULL problem you would hit in your second expression. In T-SQL there is a function ISNULL() but it actually replaces a NULL value with some other value, like Access NZ(). To get the effect of Access SQL IIF(IsNull(field),expression1, expression2) you need something like

CASE WHEN field IS NULL THEN expression1 ELSE expression2 END

Also, if you need to qualify a field name with a table name use a dot, like table1.field1 or, if you need to quote it, "table 1"."field 1".

(digression: I think it really pays to make field names unique, by adopting a convention of using short table-name prefixes on field names, like tblAddresses.adrStreet. That "adr"is faster to type and much shorter than "tblAddresses." but still allows you (and SQL) to work out which table contains the field. When displaying results you can put "user friendly" names in the front-end form)

Now the Format(value, codes) problem.

To quote someone else on the subject "why would you want to format text? T-SQL is for storage, use a front-end for formatting". That quote isn't the whole story, because I use the database for formatting all the time when using MySQL, which has a plethora of formatting functions to help me do that, but it does seem to be the T-SQL philosophy.

T-SQL has a function CONVERT which changes datatypes and has limited string-formatting ability. You could use something like this:

'£' + substring(CONVERT(varchar(100), CONVERT(money, numFloat), 1), 1, len(CONVERT(varchar(100), CONVERT(money, numFloat), 1)) - 3)

You wouldn't want to type that very often, so you could write a user defined function in T-SQL to do it

create function FormatMoney (@Float float) returns varchar(100)
  begin
    return '£' + substring(CONVERT(varchar(100), CONVERT(money, @Float), 1), 1, len(CONVERT(varchar(100), CONVERT(money, @Float), 1)) - 3);
  end;
go

What the above does incidentally is to convert numFloat, which is a float type, to a money type, then convert that to a varchar string using format 1 which is like 1,234.56 and finally strip off the .56 using substring. If your money values are already stored as money types you have an easier time of it. There may be an easier way to do it, I always format in the front end when using MS SQL.

And finally (!) a big disclaimer. In the above examples I've skipped over issues around schemas. I know that when I tested the "create view" with the FormatMoney function T-SQL insisted I use dbo.FormatMoney, there may be other issues in your setup.

Hope I have helped, not confused, you

Ian.