From: "Mike Hackett" <sharmike@voicenet.com>
Subject: Re: Comparison of ANSI SQL and Access SQL keywords
Date: 1997/06/06
Message-ID: <01bc71ed$ae6d0760$0100007f@sharmike.voicenet.com>#1/1
References: <3386BAB1.465F@cableinet.co.uk> <01bc702f$c5b3ccc0$2d64a8c0@DAR.cu-online.com>
Organization: Voicenet - Internet Access - (215)674-9290
Newsgroups: comp.databases,comp.databases.ms-access,comp.databases.oracle.misc



The closest Access equivalents to DECODE are the immediate if function
which works like a spreadsheet IF function:

IIf (condition, value_if_true, value_if_false)
returns either value_if_true or value_if_false

Or the Switch function if there are multiple conditions to evaluate:

Switch (condition1, value_if_true1, [condition2, value_if_true2...])
returns one of the value_if_true values or Null if none are true

Also very useful in reproducing a decode would be the In function which
works like the IN function used in a WHERE clause.

In(item1 [, item2...)
Returns either True or False

Rather than an NVL function, Access has a function called IsNull():
IsNull(value)
returns Either True or False


A DECODE like the following:

DECODE(COUNTRY,
  'USA', 'N. America',
  'Canada', 'N. America',
  'UK', 'Europe',
  'France', 'Europe',
  NULL, 'Not specified'
  'Other')

Could be written in Access like

Switch([COUNTRY] In("USA", "Canada"), "N. America", [COUNTRY] In("UK",
"France"), "Europe", IsNull([COUNTRY), "Not Specified", True, "Other")

Where this approach is really useful is if you have to test a range of
values. Rather than having to do some sort of DECODE the SGN of the
difference of something, you can do something like this:

Switch([UnitsInStock]<20,"Low",[UnitsInStock] Between 21 And
40,"Medium",True,"High")

I'm not sure whether SQL Server supports the same functions as Access. 

-Mike

 
> David Spencer <dave.spencer@cableinet.co.uk> wrote in article
> <3386BAB1.465F@cableinet.co.uk>...
> > Hi
> > 
> > Does anyone know of a comparison of ANSI SQL and Access SQL.  I am
> > porting some VB code from using Oracle to Access/SQL Server and was
> > particulary interested in the Access equivalent of DECODE and NVL.
> > 
> > Thanks
> > 
> > DS
> > -- 
> >
 +-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+-=-+
> >  David Spencer  BSc (Hons)              Software Engineer,
 Wolverhampton 
> >  email - mailto:dave.spencer@cableinet.co.uk                         
> >  www   - http://www.cableinet.co.uk/users/dave.spencer
> > 
>