Solved

How do I convert Access queries to SQL Server 7

Posted on 2000-03-22
10
160 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

707 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

12 Experts available now in Live!

Get 1:1 Help Now