Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

How do I convert Access queries to SQL Server 7

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
Rab
Asked:
Rab
  • 3
  • 3
  • 2
  • +1
1 Solution
 
FarewellIIArmsCommented:
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
 
FarewellIIArmsCommented:
Hi again Rab,

The 'cstr' in my answer should have said 'Cast' like so CAST(price AS varchar(12)).
0
 
RabAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
OllyWCommented:
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
 
FarewellIIArmsCommented:
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
 
RabAuthor Commented:
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
 
OllyWCommented:
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
 
Jeremy_DCommented:
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
 
RabAuthor Commented:
Thanks
0
 
Jeremy_DCommented:
Welcome
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now