hi! i have a table about basic information of faculties. it contains 
emp_code, department(CS, Biology, etc) and degree (BSCS, MBA, PhD CS, 
etc) i would like to create a statistics of all the faculties and those 
who have master's degree or ph d. 

here are the result fields i want:
department, total # of faculties, # of faculties w/ master's degree, # of 
faculties with Ph D.

this is my code:
"SELECT COUNT(*) as total, SUM(decode(degree, 1, 'P%',0)) as phd, SUM
(DECODE(degree, 1, 'M%',0)) as ma FROM basic GROUP BY department"

this is the error i got:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'decode' in 
expression. 

i'm using asp (www.brinkster.com)and ms access.

hope you can help me.
thanks!

Fina 

Within a SELECT statement, only built-in functions are allowed. You need to
replace DECODE with a combination of built-in functions (Jet/Access or SQL
Server, depending on you database source).

Here is a Jet/Access example replacement:

    SUM(   IIF( INSTR( ' '&degree, ' P' )>0, 1, 0 )   ) as phd,
    SUM(   IIF( INSTR( ' '&degree, ' M')>0, 1, 0 )   )as ma,

And here is another one:

    SUM(   SGN( INSTR( ' '&degree, ' P' ) )   ) as phd,
    SUM(   SGN( INSTR( ' '&degree, ' M') )   )as ma,

I hope this helps.

-- Z




----- Original Message -----
From: "Fina Mesina" <fina@m...>
To: "Access ASP" <access_asp@p...>
Sent: Sunday, February 23, 2003 4:11 AM
Subject: [access_asp] problem using decode function


> hi! i have a table about basic information of faculties. it contains
> emp_code, department(CS, Biology, etc) and degree (BSCS, MBA, PhD CS,
> etc) i would like to create a statistics of all the faculties and those
> who have master's degree or ph d.
>
> here are the result fields i want:
> department, total # of faculties, # of faculties w/ master's degree, # of
> faculties with Ph D.
>
> this is my code:
> "SELECT COUNT(*) as total, SUM(decode(degree, 1, 'P%',0)) as phd, SUM
> (DECODE(degree, 1, 'M%',0)) as ma FROM basic GROUP BY department"
>
> this is the error i got:
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Undefined function 'decode' in
> expression.
>
> i'm using asp (www.brinkster.com)and ms access.
>
> hope you can help me.
> thanks!
>
> Fina
>

> Within a SELECT statement, only built-in functions are allowed. You 
need to
replace DECODE with a combination of built-in functions (Jet/Access or SQL
Server, depending on you database source).

Here is a Jet/Access example replacement:

    SUM(   IIF( INSTR( ' '&degree, ' P' )>0, 1, 0 )   ) as phd,
    SUM(   IIF( INSTR( ' '&degree, ' M')>0, 1, 0 )   )as ma,

And here is another one:

    SUM(   SGN( INSTR( ' '&degree, ' P' ) )   ) as phd,
    SUM(   SGN( INSTR( ' '&degree, ' M') )   )as ma,

I hope this helps.

-- Z




----- Original Message -----
From: "Fina Mesina" <fina@m...>
To: "Access ASP" <access_asp@p...>
Sent: Sunday, February 23, 2003 4:11 AM
Subject: [access_asp] problem using decode function


> hi! i have a table about basic information of faculties. it contains
> emp_code, department(CS, Biology, etc) and degree (BSCS, MBA, PhD CS,
> etc) i would like to create a statistics of all the faculties and those
> who have master's degree or ph d.
>
> here are the result fields i want:
> department, total # of faculties, # of faculties w/ master's degree, # 
of
> faculties with Ph D.
>
> this is my code:
> "SELECT COUNT(*) as total, SUM(decode(degree, 1, 'P%',0)) as phd, SUM
> (DECODE(degree, 1, 'M%',0)) as ma FROM basic GROUP BY department"
>
> this is the error i got:
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC Microsoft Access Driver] Undefined function 'decode' in
> expression.
>
> i'm using asp (www.brinkster.com)and ms access.
>
> hope you can help me.
> thanks!
>
> Fina
>

thanks! the code works.