Re: Comparison of ANSI SQL and Access SQL keywords

From: Mike Hackett <sharmike_at_voicenet.com>
Date: 1997/06/06
Message-ID: <01bc71ed$ae6d0760$0100007f@sharmike.voicenet.com>#1/1

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_at_cableinet.co.uk> wrote in article
> <3386BAB1.465F_at_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_at_cableinet.co.uk
> > www - http://www.cableinet.co.uk/users/dave.spencer
> >
>
  Received on Fri Jun 06 1997 - 00:00:00 CDT