Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    upsizing querys and iif (Access 2000 sp3)

    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? <img src=/S/please.gif border=0 alt=please width=31 height=23>
    Carpy Diem, it&#39;s .

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: upsizing querys and iif (Access 2000 sp3)

    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.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    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?
    Carpy Diem, it&#39;s .

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: upsizing querys and iif (Access 2000 sp3)

    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.
    Charlotte

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    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.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    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. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>
    Carpy Diem, it&#39;s .

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    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):
    <pre>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
    </pre>

    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:
    <pre>SELECT *, IIF([Color] = "Yellow" AND [Durability] = "Fragile", "Banana", "Not Banana")
    AS WhatIsIt
    FROM Product
    </pre>

    then the SQL Server query looks like this:
    <pre>SELECT *,
    CASE
    WHEN P.Color = 'Yellow' AND P.Durability = 'Fragile' THEN
    'Banana'
    ELSE
    'Not Banana'
    END
    AS WhatIsIt
    FROM Product P
    </pre>

    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  8. #8
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    Thanks Shane - I'm trying it out right now.

    It's starting to make sense now.....
    Carpy Diem, it&#39;s .

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: upsizing querys and iif (Access 2000 sp3)

    Shane,

    It worked! Thanks ! <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>!

    Now I can go on vacation with one less worry. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Carpy Diem, it&#39;s .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •