Convert Ms Access IIF to SQL Server Version 3

[Bomac8]

I am in the process of converting an msaccess application to SQL Server and I am having trouble converting this code to a case statement

IIf(([LOTOSMIS_RET_DAILY]![GM_CD]=6150 Or [LOTOSMIS_RET_DAILY]![GM_CD]>=12100),IIf(IsNull([GameXrefVar]![DCLBGame]),[LOTOSMIS_RET_DAILY]![GM_VAR],[GameXrefVar]![DCLBGame]),IIf(IsNull([GameXref]![DCLBGame]),[LOTOSMIS_RET_DAILY]![GM_CD],[GameXref]![DCLBGame])) AS GameID
i have tried
(CASE WHEN (P.GM_CD = 6150 OR P.GM_CD >= 12100) AND GameXrefVar.DCLBGame IS null
THEN P.GM_VAR = GameXrefVar.DCLBGame)
WHEN (P.GM_CD = 6150 Or P.GM_CD >= 12100) THEN (P.GM_VAR = GameXrefVar.DCLBGame
END) AS GameID
What am I doing wrong?
Thanks in advance

 

[gmmastros]

I think it's the parenthesism and you can't set things in the Then part

CASE WHEN (P.GM_CD = 6150 OR P.GM_CD >= 12100) AND GameXrefVar.DCLBGame IS null
     THEN [!]P.GM_VAR = GameXrefVar.DCLBGame <- This is a no no[/!]
     WHEN (P.GM_CD = 6150 Or P.GM_CD >= 12100) 
	 THEN P.GM_VAR = GameXrefVar.DCLBGame
     END AS GameID

case When [LOTOSMIS_RET_DAILY].[GM_CD]=6150 Or [LOTOSMIS_RET_DAILY].[GM_CD]>=12100
     Then Case When IsNull([GameXrefVar].[DCLBGame]) = 1
               Then [LOTOSMIS_RET_DAILY].[GM_VAR]
               Else [GameXrefVar].[DCLBGame]
               End
     Else Case When IsNull([GameXref].[DCLBGame]) = 1
               Then [LOTOSMIS_RET_DAILY].[GM_CD]
               Else [GameXref].[DCLBGame]
               End		
     End

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Andrzejek]

George,
I like the way you format your code / SQL statement.
It makes all the difference and it is very easily readable / understandable

I wish more people would have the same approach to what they present.


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

There is a SQL Format utility available that I use regularly. Below is the result of the SQL Server attempt. The tools colorizes.

(CASE 
WHEN ( 
    p.gm_cd = 6150 
    OR 
    p.gm_cd >= 12100 
  ) 
  AND 
  gamexrefvar.dclbgame IS NULL THEN 
  p.gm_var = gamexrefvar.dclbgame) 
WHEN ( 
    p.gm_cd = 6150 
    OR 
    p.gm_cd >= 12100 
  ) 
  THEN 
  (p.gm_var = gamexrefvar.dclbgame 
END 
  ) AS gameid

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[gmmastros]

Andy,

I format code the way I do for my benefit. Easier to read means easier to maintain.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bomac8]

THanks for all the help.The statement
"IIf(([LOTOSMIS_RET_DAILY]![GM_CD]=6150 Or [LOTOSMIS_RET_DAILY]![GM_CD]>=12100),IIf(IsNull([GameXrefVar]![DCLBGame]),[LOTOSMIS_RET_DAILY]![GM_VAR],[GameXrefVar]![DCLBGame]),IIf(IsNull([GameXref]![DCLBGame]),[LOTOSMIS_RET_DAILY]![GM_CD],[GameXref]![DCLBGame])) AS GameID,"
The real issue here is that the gamexrefvar table is actually the same table as gamexref. But its named differently. Ive never seen that before. Why are they using the same table with a different name? I dont understand.

 

[dhookom]

Can you provide the entire SQL statement and the specification for what you are attempting to accomplish?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Bomac8]

Sure. I have an access application tht I m trying to convert to SQL server. I have to get the data from an Oracle database accessed thru a link server. I bring some of the data in to two temp tables and I run this query to insert the data into an SQL server table. The table

BEGIN TRANSACTION
BEGIN TRY
DROP TABLE if exists #ACC_TYPETemp
CREATE TABLE #ACC_TYPETemp
(
LOTOS_CD bigint,
GROUP_CD int,
SING_IND int
)
--Get last load date
DECLARE @LoadDate Date
SET @LoadDate = '4/11/2011'

--Populate #ACC_TYPETemp table
INSERT INTO #ACC_TYPETemp(A.LOTOS_CD, A.GROUP_CD, A.SING_IND)
SELECT A.LOTOS_CD, A.GROUP_CD, A.SING_IND FROM OPENQUERY(LOTOS,'SELECT LOTOSMIS.ACC_TYPE.LOTOS_CD, LOTOSMIS.ACC_TYPE.GROUP_CD, LOTOSMIS.ACC_TYPE.SING_IND
FROM LOTOSMIS.ACC_TYPE') A

DROP TABLE IF EXISTS #RET_DAILYTemp
CREATE TABLE #RET_DAILYTemp
(
RET_CD bigint,
GM_CD bigint,
GM_VAR int,
DRAW_CD bigint,
LOTOS_CD bigint,
CMS money,
GRS_AMN money,
CPNS money,
DT datetime
)

--populate #RET_DAILYTemp Table
INSERT INTO #RET_DAILYTemp(S.RET_CD, S.GM_CD, S.GM_VAR, S.DRAW_CD,S.LOTOS_CD, S.GRS_AMN,S.CMS,S.CPNS, S.DT)
select S.* from openquery(LOTOS, 'SELECT RET_CD, GM_CD, GM_VAR, DRAW_CD, LOTOS_CD, GRS_AMN, CMS, CPNS, DT FROM LOTOSMIS.RET_DAILY WHERE RET_CD = 50921') S

--select * from #RET_DAILYTemp where GM

--Populate SalesByDate
INSERT INTO SalesByDate(LOCATION_NUMBER,ACTIVITY_DATE,GameID, Net,Commission,Gross,Cancels, Tix, RegSales, MPlierSales)

SELECT P.RET_CD,P.DT,
( CASE
WHEN(
P.GM_CD = 6150
OR
P.GM_CD >= 12100)
AND GameXrefVar.DCLBGame IS null THEN
P.GM_VAR
WHEN ( P.GM_CD = 6150
OR
P.GM_CD >= 12100) THEN gamexrefvar.dclbgame
END
) AS GameID,

Sum(T.SING_IND * P.GRS_AMN) AS Net,
Sum(P.CMS) AS SumOfCMS,
Sum(IIf(T.SING_IND =1, P.GRS_AMN,0)) AS Gross,
Sum(IIf(T.SING_IND= -1, P.GRS_AMN *-1,0)) AS Cancels,
Sum(P.CPNS *T.SING_IND) AS Tix,
Sum(CASE WHEN P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253 ) THEN T.SING_IND * P.GRS_AMN else 0 end) as RegSales,
Sum(T.sing_ind * P.grs_amn * CASE WHEN P.gm_cd in (1105, 2123, 2124, 2150, 2152, 2191, 2192, 5143, 5145, 5146, 5245, 5253)
THEN P.GM_VAR else 0 end) as MPlierSal
FROM((#RET_DAILYTemp P LEFT JOIN #ACC_TYPETemp T on P.LOTOS_CD = T.LOTOS_CD)
LEFT JOIN GameXRef ON P.GM_CD = GameXRef.IntralotGame)
LEFT JOIN GameXref AS GameXrefVar ON P.GM_VAR = GameXrefVar.IntralotGame
WHERE(((T.GROUP_CD)=2) AND ((T.LOTOS_CD)=2 Or (T.LOTOS_CD)=4 Or (T.LOTOS_CD)=13 Or (T.LOTOS_CD)=16))
GROUP BY P.RET_CD, P.DT, ( CASE
WHEN(
P.GM_CD = 6150
OR
P.GM_CD >= 12100)
AND GameXrefVar.DCLBGame IS null THEN
P.GM_VAR
WHEN ( P.GM_CD = 6150
OR
P.GM_CD >= 12100) THEN gamexrefvar.dclbgame
END
)
HAVING P.DT = @LoadDate -- and P.DT < '4/11/2011';
COMMIT
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE() AS ErrorMessage;
ROLLBACK TRANSACTION
END CATCH

END

 

[gmmastros]

The real issue here is that the gamexrefvar table is actually the same table as gamexref. But its named differently. Ive never seen that before. Why are they using the same table with a different name? I dont understand.

This is done because the table is used twice in the same query. If you want to use the same table twice, you need to alias the table name.

For example, i have a student table with HomeAddressId and MailingAddressId. Both columns reference a common Address table. So...

Select StudentName,
       [red]HomeAddress.Address1[/red],
       [blue]MailAddress.Address1[/blue]
From   Student
       Inner Join Address [red]As HomeAddress[/red]
         On Student.HomeAddressId = [red]HomeAddress.AddressId[/red]
       Inner Join Address [blue]As MailAddress[/blue]
         On Student.MailingAddressId = [blue]MailAddress.AddressId[/blue]

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Bomac8]

Thanks really helpful.