upsizing querys and iif (Access 2000 sp3)

Topic

New Reply

We’re trying to upsize to SQL 2000 and created an adp (project) to store the queries. Most of the queries went over from the A2K .mdb database. One query that didn’t involved the calculation of a code based on an IIF(something, ‘12345’, somethingelse). In recreating the stored procedure, the SQL check doesn’t like the IIf statement. Can anyone help me with an alternative here?

Reply | Quote

Replies

IIF doesn’t exist in T-SQL, so SQL Server can’t handle it. If you post the SQL of your query, someone should be able to help you modify it for SQL Server. If you need to do this in SQL, you’ll need a SQL user-defined function, but there may be alternatives.

Reply | Quote

Here’s part of the big, ugly query. I set up a view using CASE ELSE and was able to do the first IIF(customer = “999999”), but was unable to handle the nested IIF statement below::

SELECT CustHeader.SHIPDATE, ShipData.CUSTOMER, CustHeader.CUST_NAME, CustHeader.SHIP_VIA, CustHeader.NET_DAYS,
ShipData.SHIP_TO_NAME, qryHTS.HTSDescr, IIf(([ShipData].[CUSTOMER]=”999999″ And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90″,
IIf((Left([ShipData].[CUSTOMER],2)=”HO” And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90”,[qryHTS].[HTS])) AS HTS1,
…………………………..

Unfortunately, we rely on IIF a lot. So other than CASE’ing ourselves into an early grave, is there a better alternative?

Reply | Quote

CASE statements provide the If-then-else functionality in T-SQL, so that is what you have to use, however, they provide much more functionality in SQL Server than in Access SQL.

Reply | Quote

Sorry, Charlotte – I tried to find out where I could duplicate my nested iif statement using Case, but I’m at a loss.

Can you point me in the right direction? Basically, most of our IIF statements involve two fields that need to be evaluated before we want to make a change. For example, if the item is a fruit and it’s yellow, then it needs the “fragile, it’s a banana” code. But if it’s a fruit and it’s not yellow, then it doesn’t.

Since all the examples of Case I’ve seen involve just one field, I can’t figure out what to do.

Reply | Quote

Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

If the Access query looks like:

SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

then the SQL Server query looks like this:

SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P

Reply | Quote

Thanks Shane – I’m trying it out right now.

It’s starting to make sense now…..

Reply | Quote

Thanks Shane – I’m trying it out right now.

It’s starting to make sense now…..

Reply | Quote

Shane,

It worked! Thanks ! !

Now I can go on vacation with one less worry.

Reply | Quote

Shane,

It worked! Thanks ! !

Now I can go on vacation with one less worry.

Reply | Quote

Mmmmm…bananas! The trick is to embed the WHEN…ELSE statement withing the CASE statement. OK, let’s make a fake table to play with. Run this SQL script to make a table named Product that has 3 fields (an identity field named ID, and two fields to hold text values named Color and Durability):

CREATE TABLE [dbo].[Product] (
	[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
	[Color] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Durability] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

Populate the new table. In the first record, make Color be “Yellow” and Durability be “Fragile”. Now populate a few more records; go with: Yellow/Durable, Green/Fragile, and Green/Durable.

If the Access query looks like:

SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
  AS WhatIsIt
FROM Product

then the SQL Server query looks like this:

SELECT *, 
  CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
      'Banana'
    ELSE
      'Not Banana'
  END
AS WhatIsIt
FROM Product P

Reply | Quote

Sorry, Charlotte – I tried to find out where I could duplicate my nested iif statement using Case, but I’m at a loss.

Can you point me in the right direction? Basically, most of our IIF statements involve two fields that need to be evaluated before we want to make a change. For example, if the item is a fruit and it’s yellow, then it needs the “fragile, it’s a banana” code. But if it’s a fruit and it’s not yellow, then it doesn’t.

Since all the examples of Case I’ve seen involve just one field, I can’t figure out what to do.

Reply | Quote

CASE statements provide the If-then-else functionality in T-SQL, so that is what you have to use, however, they provide much more functionality in SQL Server than in Access SQL.

Reply | Quote

Adding to Charlotte’s comments, if you use CASE statements in SQL Server, you usually are talking about Stored Procedures rather than Views, which most Access SELECT queries can be converted to. And for someone who is used to the nice compact syntax of IIF statements, CASE statements seem positively ugly! But as Charlotte points out, at least through SQL Server 2000, they are the only game in town. That’s one of the reasons we typically use linked SQL Server tables rather than the ADP approach.

Reply | Quote

Adding to Charlotte’s comments, if you use CASE statements in SQL Server, you usually are talking about Stored Procedures rather than Views, which most Access SELECT queries can be converted to. And for someone who is used to the nice compact syntax of IIF statements, CASE statements seem positively ugly! But as Charlotte points out, at least through SQL Server 2000, they are the only game in town. That’s one of the reasons we typically use linked SQL Server tables rather than the ADP approach.

Reply | Quote

Here’s part of the big, ugly query. I set up a view using CASE ELSE and was able to do the first IIF(customer = “999999”), but was unable to handle the nested IIF statement below::

SELECT CustHeader.SHIPDATE, ShipData.CUSTOMER, CustHeader.CUST_NAME, CustHeader.SHIP_VIA, CustHeader.NET_DAYS,
ShipData.SHIP_TO_NAME, qryHTS.HTSDescr, IIf(([ShipData].[CUSTOMER]=”999999″ And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90″,
IIf((Left([ShipData].[CUSTOMER],2)=”HO” And [HTSDescr]=”MOUSE EARS -“),”1234.56.78.90”,[qryHTS].[HTS])) AS HTS1,
…………………………..

Unfortunately, we rely on IIF a lot. So other than CASE’ing ourselves into an early grave, is there a better alternative?

Reply | Quote

IIF doesn’t exist in T-SQL, so SQL Server can’t handle it. If you post the SQL of your query, someone should be able to help you modify it for SQL Server. If you need to do this in SQL, you’ll need a SQL user-defined function, but there may be alternatives.

Reply | Quote