Convert Access Query to Transact SQL 2000 IIF to CASE

I need to convert a MS Access 2000 query into SQL Server 2000. Since
the IIF statements are not allowed in Transact-SQL, I 'm trying to
use the CASE Statement
I have two tables, Results and Sections. I need to import the section
records into the Results table. I tried to do a CASE statement, but so
far I cant' get the second IIF right.
In a nutshell, this is what this update query does:
Update the TotalScore in the tblResults table. If the tblResults.type
field is A, B, or C, then make it null. Otherwise, check if the
tblSections.MainScore1 field is null. If it is, then use the
tblResults.TotalScore value. If is not null, then use the
tblSections.MainScore1 value.
Here is the actual Access query syntax :
UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
[tblSections].TestID
SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
[Type]="C",Null,
IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
I have several other fields to update in this query, but they're very
similar to this one.
Any help would be appreciated it.
Thanks.let's see..
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145560609.392893.303600@.e56g2000cwe.googlegroups.com...
>I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.
> Any help would be appreciated it.
> Thanks.
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You can't use Access SQL syntax in SQL'r. Use Standard SQL syntax.:
UPDATE tblResults
SET TotalScore =
CASE WHEN [Type] IN ('A','B','C') THEN NULL
ELSE CASE WHEN MainScore1 IS NULL THEN TotalScore
ELSE (SELECT MainScore1 FROM tblSections
WHERE TestID = tblResults.TestID)
END
END
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBREfx1oechKqOuFEgEQLAkgCfZlAFT1+TbFfW
cP+zDhv5c5hCnqgAnAji
grqbw9lVWW3Mm+3fC54YuPJj
=Rv7/
--END PGP SIGNATURE--
ILCSP@.NETZERO.NET wrote:
> I need to convert a MS Access 2000 query into SQL Server 2000. Since
> the IIF statements are not allowed in Transact-SQL, I 'm trying to
> use the CASE Statement
> I have two tables, Results and Sections. I need to import the section
> records into the Results table. I tried to do a CASE statement, but so
> far I cant' get the second IIF right.
> In a nutshell, this is what this update query does:
> Update the TotalScore in the tblResults table. If the tblResults.type
> field is A, B, or C, then make it null. Otherwise, check if the
> tblSections.MainScore1 field is null. If it is, then use the
> tblResults.TotalScore value. If is not null, then use the
> tblSections.MainScore1 value.
> Here is the actual Access query syntax :
> UPDATE [tblResults] INNER JOIN [tblSections] ON [tblResults].TestID =
> [tblSections].TestID
> SET [tblResults].TotalScore = IIf([Type]="A" Or [Type]="B" Or
> [Type]="C",Null,
> IIf(IsNull([tblSections].[MainScore1]),[tblResults].[TotalScore],[tblSections].[MainScore1]))
>
> I have several other fields to update in this query, but they're very
> similar to this one.|||Hello Guys, thanks for replying. I've been trying both suggestions and
both give me the same results. I'm able to get some of the total
scores, but not all of them.
I can get some of the total scores for not all of them. My guess is
that the error happens when the query is evaluating for null or perhaps
the declared join for the 2 tables is wrong.
the Table tblResults has 1 instance of TestID. The Table tblSections
has several instances of a TestID and they can have something in the
MainScore1 or be Null.
Here's an example of the tblSections table. In this example, I'm able
to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
When running the old Access 2000 update query, I do get the 3
TotalScores.
Type TestID MainScore1
M 214209195 58
L 214371148 43
U 214217823 74
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
M 214209195
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
L 214371148
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823
U 214217823|||Hi,
Could you please post the DDL for the tables in question, INSERTs for the
sample data, and the expected results?
Thanks
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145640771.510008.142980@.v46g2000cwv.googlegroups.com...
> Hello Guys, thanks for replying. I've been trying both suggestions and
> both give me the same results. I'm able to get some of the total
> scores, but not all of them.
> I can get some of the total scores for not all of them. My guess is
> that the error happens when the query is evaluating for null or perhaps
> the declared join for the 2 tables is wrong.
> the Table tblResults has 1 instance of TestID. The Table tblSections
> has several instances of a TestID and they can have something in the
> MainScore1 or be Null.
> Here's an example of the tblSections table. In this example, I'm able
> to get the 43 TotalScore for the 214371148 ID, but not for the other 2.
> When running the old Access 2000 update query, I do get the 3
> TotalScores.
> Type TestID MainScore1
> M 214209195 58
> L 214371148 43
> U 214217823 74
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> M 214209195
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> L 214371148
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
> U 214217823
>|||Here's the DDLs
CREATE TABLE [dbo].[tblResults] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TestDate] [datetime] NULL ,
[TotalScore] [float] NULL ,
[MainScore1] [float] NULL ,
[MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore2] [float] NULL ,
[MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainScore3] [float] NULL ,
[MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[tblSections] (
[InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
[TestID] [int] NULL ,
[MainScore1] [float] NULL ,
[MainScore2] [float] NULL ,
[MainScore3] [float] NULL ,
[MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[Sec1c] [float] NULL ,
[Sec1i] [float] NULL ,
[Sec2c] [float] NULL ,
[Sec2i] [float] NULL
) ON [PRIMARY]
GO
Since I already posted a short example of the tblSections table, here's
the one for the tblResults table
TestID SSN ExamType TotalScore
214209195 123456789 M
214371148 987654321 L
214398090 234567890 B
214398102 098765432 A
214217823 590380445 U
214430778 567890123 C
Based on the update query, I should get the totalscore for the records
with the TestID: 214209195, 214371148, and the 214217823. However, I
only get the results for the 214371148. The other 2 total scores are
still null.
Hope this helps.|||Hi,
Thanks for the DDL. And if you've posted the actual INSERT script, would it
look something like this?
insert tblResults (TestID, SSN, Type)
select
214209195, '123456789', 'M'
union all
select
214371148, '987654321', 'L'
union all
select
214398090, '234567890', 'B'
union all
select
214398102, '098765432', 'A'
union all
select
214217823, '590380445', 'U'
union all
select
214430778, '567890123', 'C'
insert tblSections (TestID, MainScore1)
select
214209195, 58
union all
select
214371148, 43
union all
select
214217823, 74
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214209195, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214371148, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
union all
select
214217823, null
If so, the update I posted yesterday should give the expected result. Once
again:
update tblResults
set TotalScore = case when r.type in ('A', 'B', 'C') then null else
coalesce(s.MainScore1, r.TotalScore) end
from tblResults r, tblSections s
where r.testID = s.testID
select TestId, TotalScore from tblResults
TestId TotalScore
-- ----
214209195 58.0
214371148 43.0
214398090 NULL
214398102 NULL
214217823 74.0
214430778 NULL
(6 row(s) affected)
Dean
<ILCSP@.NETZERO.NET> wrote in message
news:1145644726.162771.207080@.g10g2000cwb.googlegroups.com...
> Here's the DDLs
> CREATE TABLE [dbo].[tblResults] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [SSN] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Type] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [TestDate] [datetime] NULL ,
> [TotalScore] [float] NULL ,
> [MainScore1] [float] NULL ,
> [MainStatus1] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore2] [float] NULL ,
> [MainStatus2] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainScore3] [float] NULL ,
> [MainStatus3] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
>
>
> CREATE TABLE [dbo].[tblSections] (
> [InactivePrimary] [int] IDENTITY (1, 1) NOT NULL ,
> [TestID] [int] NULL ,
> [MainScore1] [float] NULL ,
> [MainScore2] [float] NULL ,
> [MainScore3] [float] NULL ,
> [MainStatus1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [MainStatus3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [Sec1c] [float] NULL ,
> [Sec1i] [float] NULL ,
> [Sec2c] [float] NULL ,
> [Sec2i] [float] NULL
> ) ON [PRIMARY]
> GO
>
> Since I already posted a short example of the tblSections table, here's
> the one for the tblResults table
> TestID SSN ExamType TotalScore
> 214209195 123456789 M
> 214371148 987654321 L
> 214398090 234567890 B
> 214398102 098765432 A
> 214217823 590380445 U
> 214430778 567890123 C
>
> Based on the update query, I should get the totalscore for the records
> with the TestID: 214209195, 214371148, and the 214217823. However, I
> only get the results for the 214371148. The other 2 total scores are
> still null.
> Hope this helps.
>