SQL server statement error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
 
Are you using Access queries or SQL Server (as stated in your subject line)?
If Access, use IIf() rather than IF().
 
I am using SQL Server 2005 and I am getting error messages. Are you saying
it should work as it is?


Duane Hookom said:
Are you using Access queries or SQL Server (as stated in your subject line)?
If Access, use IIf() rather than IF().

--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
Click to expand...
Click to expand...
 
SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IIF([HomePhone] LIKE '0[1278]*',1,2) AS Expr2
FROM account


Or perhaps you will need to use % instead of * in the expression.

IF you are using native SQL SERVER syntax (a pass through query) then you
may need to look at building a case statement.

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left(homephone,2) as Expr1,
Case WHEN HomePhone Like '0[1278]%' Then 1 Else 2 End as TestValue
FROM account
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
If this is a SQL Server syntax question then you should really post in a SQL
Server NG. SQL Server use CASE WHEN....END syntax. Check SQL BOL.
--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I am using SQL Server 2005 and I am getting error messages. Are you saying
it should work as it is?


Duane Hookom said:
Are you using Access queries or SQL Server (as stated in your subject line)?
If Access, use IIf() rather than IF().

--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
Click to expand...
Click to expand...
Click to expand...
 
thanks.

I will have a search

Duane Hookom said:
If this is a SQL Server syntax question then you should really post in a SQL
Server NG. SQL Server use CASE WHEN....END syntax. Check SQL BOL.
--
Duane Hookom
Microsoft Access MVP


scubadiver said:
I am using SQL Server 2005 and I am getting error messages. Are you saying
it should work as it is?


Duane Hookom said:
Are you using Access queries or SQL Server (as stated in your subject line)?
If Access, use IIf() rather than IF().

--
Duane Hookom
Microsoft Access MVP


:


I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
Click to expand...
Click to expand...
Click to expand...
 
Your second code worked.

I didn't think SQL server was so different to Access SQL.

John Spencer said:
SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IIF([HomePhone] LIKE '0[1278]*',1,2) AS Expr2
FROM account


Or perhaps you will need to use % instead of * in the expression.

IF you are using native SQL SERVER syntax (a pass through query) then you
may need to look at building a case statement.

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left(homephone,2) as Expr1,
Case WHEN HomePhone Like '0[1278]%' Then 1 Else 2 End as TestValue
FROM account
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
Click to expand...
Click to expand...
 
The SQL language has many dialects - they are similar but not identical.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
Your second code worked.

I didn't think SQL server was so different to Access SQL.

John Spencer said:
SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IIF([HomePhone] LIKE '0[1278]*',1,2) AS Expr2
FROM account


Or perhaps you will need to use % instead of * in the expression.

IF you are using native SQL SERVER syntax (a pass through query) then you
may need to look at building a case statement.

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left(homephone,2) as Expr1,
Case WHEN HomePhone Like '0[1278]%' Then 1 Else 2 End as TestValue
FROM account
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
I am trying to classify valid and non valid phone numbers using a
classification. Can I be told what is wrong with the third line?

Cheers

SELECT ClientID, Surname, DateOfBirth, Postcode, HomePhone,
Left([homephone],2) as Expr1,
IF([expr1] in ('01','02','07','08'),1,2) AS Expr2
FROM account
Click to expand...
Click to expand...
Click to expand...
 
Back
Top