Solved

How do I convert Access queries to SQL Server 7

Posted on 2000-03-22
10
161 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now