Solved

How do I convert Access queries to SQL Server 7

Posted on 2000-03-22
10
162 Views
Last Modified: 2010-05-02
I have a Access query with A IIF statement in the SELECT part of the query I need to convert this query to a Store procedure in SQL 7 How?
e.g.

SELECT IIf(IsNull(MoveNumber),0,MoveNumber) AS MovementNumber,
A.MDate AS MovementDate,
IIf(IsNull(MoveNumber),'** AUDIT **',
IIf(IsNull(C.CNumber),'** MISSING **',
C.CNumber & ' - ' & C.dsName)) AS CustomerFrom,
IIf(IsNull(D.CNumber),'** MISSING **',
D.CNumber & ' - ' & D.dsName) AS CustomerTo
FROM ((tblCabinetMovements AS A INNER JOIN tblCabinets AS B ON
 A.CabinetID = B.CabinetID) LEFT JOIN tblCustomers AS C ON
 A.CustomerIDFrom = C.CustomerID) LEFT JOIN tblCustomers AS D ON A.CustomerIDTo = D.CustomerID
WHERE (((B.PPNumber)=[strCriteria]))
ORDER BY A.MDate DESC
0
Comment
Question by:Rab
  • 3
  • 3
  • 2
  • +1
10 Comments
 

Expert Comment

by:FarewellIIArms
ID: 2644096
Hi Rab,

Easy to do(less text), use the 'IsNull' function of SQL only problem you will have is your IIF statement appears to return a different datatype than the checked field(IIf(IsNull(MoveNumber),'** AUDIT **') You might have to caste the checked value to string like so - IsNull(cstr(MoveNumber),'** AUDIT **')

Text from Transac-SQL help below.

ISNULL Function
Replaces NULL with the specified replacement value.
Syntax

ISNULL(check_expression, replacement_value)

Arguments

check_expression

Is the expression to be checked for NULL. check_expression can be of any type.

replacement_value

Is the expression to be returned if check_expression is NULL. replacement_value must have the same type as check_expresssion.

Return Types

Returns the same type as check_expression.

Remarks

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.

Examples
A.    Use ISNULL with AVG

This example finds the average of the prices of all titles, substituting the value $10.00 for all NULL entries in the price column of the titles table.

USE pubs

GO

SELECT AVG(ISNULL(price, $10.00))

FROM titles

GO
0
 

Expert Comment

by:FarewellIIArms
ID: 2644107
Hi again Rab,

The 'cstr' in my answer should have said 'Cast' like so CAST(price AS varchar(12)).
0
 

Author Comment

by:Rab
ID: 2644166
The problem is with the IIF statement
IS THERE A IIF STATEMENT IN SQL IF SO HOW DO I USE IT? WHAT OTHER OPTION IS THERE FOR ME TO USE.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Expert Comment

by:OllyW
ID: 2644253
Hi Rab,

There are at least two possible options. The easiest is the ISNULL function. This is more powerful then access' version. It has the syntax:

ISNULL(check_expression, replacement_value)

Therefore you can replace all your null values with '** AUDIT **', etc.

Another option is to use the CASE statment like this:

CASE WHEN Movenumber IS NULL THEN '** AUDIT **'
ELSE Movenumber

The case function can be used inline with SELECT statments. The Transact-SQL online help should be of help and give examples.

Hope this is of help
0
 

Expert Comment

by:FarewellIIArms
ID: 2644367
Hi Again Rab,

Along with the Case & IsNull there is also an If/Else construct or a Coalese

If/Esle has exactly the same functionality as IIF - it just more typing, Coalese will select the first non-null value from a list of fields.
0
 

Author Comment

by:Rab
ID: 2644386
Thanks you guys but How do I use the IIF or If/Esle with this problem of myne I have tride but no success
0
 

Expert Comment

by:OllyW
ID: 2644405
FarewellIIArms is right, there is IF/ELSE and Coalese, but a word of caution: I am pretty sure the If/Else won't work in inline SQL, only in SQL statement blocks. Therefore, your stored procedure would be a lot more complex and less efficent.
0
 
LVL 4

Accepted Solution

by:
Jeremy_D earned 75 total points
ID: 2644640
Hi Rab, this is OTTOMH, so if you get errors or unexpected results, let me know and we'll review it.
This should be it I think:

=====================================
CREATE PROCEDURE sp_myproc (@criteria varchar(8000)) AS
SELECT
  COALESCE(MoveNumber, 0) AS MovementNumber,
  A.MDate AS MovementDate,
  CustomerFrom =
    CASE WHEN MoveNumber IS NULL
      THEN
        '** AUDIT **'
      ELSE
        CASE WHEN C.CNumber IS NULL
          THEN
            '** MISSING **'
          ELSE
            C.CNumber & ' - ' & C.dsName
        END
    END,
  CustomerTo =
    CASE
      WHEN D.CNumber IS NULL
        THEN
          '** MISSING **'
        ELSE
          D.CNumber & ' - ' & D.dsName
    END
FROM
  ((tblCabinetMovements A INNER JOIN tblCabinets B ON A.CabinetID = B.CabinetID)
  LEFT JOIN tblCustomers C ON
 A.CustomerIDFrom = C.CustomerID)
  LEFT JOIN tblCustomers D ON A.CustomerIDTo = D.CustomerID
WHERE B.PPNumber = @criteria
ORDER BY A.MDate DESC

GO
=====================================



0
 

Author Comment

by:Rab
ID: 2644683
Thanks
0
 
LVL 4

Expert Comment

by:Jeremy_D
ID: 2644694
Welcome
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

823 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question