MS Access IFF to CASE in SQL Server -Please help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MS Access IFF to CASE in SQL Server -Please help

Author

Topic

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-28 : 10:08:30

Hello,

Thanks for reading my post.

I use ASP. I have been getting the Syntax error near keyword 'Then'. The query works fine with MS Access database. But when I upgraded Access to SQL Server 2000 as a back-end, it causes this error. I think Iff does not work on SQL so I converted it to CASE. It keeps showing the syntax error.

Can you please take a look at this code to see why I keep getting syntax error near keyword 'then'?

Thanks very much.


MS Access:
<%
" (Sum(IIf((tbl_A.AStart>'" & Request.Form("ERange") & "' " & _ 
" And tbl_A.AEnd>'" & Request.Form("ERange") & "') Or " & _ 
" (tbl_A.AStart<'" & Request.Form("SRange") & "' And " & _ 
" tbl_A.AEnd<'" & Request.Form("SRange") & "'),0, " & _ 
"IIf(tbl_A.AStart>='" & Request.Form("SRange") & "' And " & _ 
" tbl_A.AEnd>'" & Request.Form("ERange") & "', " & _ 
"DateDiff('d',tbl_A.AStart,'" & Request.Form("ERange") & "')+1,1))))))) AS Range"



SQL Server
"(Sum(CASE WHEN ((tbl_A.AStart>'" & Request.Form("ERange") & "' And " & _ 
"tbl_A.AEnd>'" & Request.Form("ERange") & "') Or" & _ 
"(tbl_A.AStart<'" & Request.Form("SRange") & "' And " & _ 
"tbl_A.AEnd<'" & Request.Form("SRange") & "') Then 0 END," & _ 
"CASE WHEN (tbl_A.AStart>='" & Request.Form("SRange") & "' And " & _ 
"tbl_A.AEnd>'" & Request.Form("ERange") & "' Then " & _ 
"DateDiff('d',tbl_A.AStart,'" & Request.Form("ERange") & "')+1,1))))))) AS Range END," & _ 
%>

Thanks very much.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-28 : 10:22:15

Could you send what the string looks like when you send it to the database? We need to see the actual statement that is causing the error.

Thanks,
Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-12-28 : 10:49:19

change the last part which reads

AS Range END,

END AS Range

That may be only one problem. In the code that you have shown, it seems like there are way too many right brackets than left brackets.

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-28 : 12:08:26

I changed the order of End but it still shows the same error.

Here are the string and the last part of the query, but I don't think they caused the syntax error so I din't post them at the beginning.

strRpt="Time from <BR>" & Request.Form("SRange") & " and " & Request.Form("ERange") & ""
If Request.QueryString("Action")="Rpt" Then
strTeam=Request.Form("ST")
If strST="All" Then
strST="%"
End If

Last part of query:

"FROM ((tbl_A INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID) INNER JOIN" & _
" tbl_S ON tbl_A.SID = tbl_S.SID) INNER JOIN tbl_P ON" & _
" tbl_T.PID = tbl_P.PID"
" WHERE ((tbl_S.st) LIKE '" & strSt & "') "
" AND ((tbl_S.PEnd) Is Null Or (tbl_S.PEnd)>=Date()) "
" GROUP BY tbl_A.SID, '" & Request.Form("SRange") & "', '" & Request.Form("ERange") & "'"
" ORDER BY " & strSort & ";"

rsReport.Open strSQL, strCon, 3

There is no stupid question.
Asian Girls at Free Online Dating

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-28 : 12:26:31

We still need to see what strSQL looks like when you send it to the database.

Jim

Everyday I learn something that somebody else already knew

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-28 : 12:44:29

This is a report to display data, not Insert statement.
Sorry for that.

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-28 : 12:51:50

I really, really want to see what strSQL looks like. Without that I don't see how we can help you without just guessing what might be wrong

Jim

Everyday I learn something that somebody else already knew

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-12-28 : 14:26:02

if you don't know what Jim means, then right b4 this line

rsReport.Open strSQL, strCon, 3

add this:

Response.Write "<p>" & strSQL & "</p>"

post the result here

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-29 : 08:38:23

Here it is:
(There are some more variables on this string because I removed some from the code I posted above).

SELECT tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co,(Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') Then 0 END,CASE WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' then DateDiff('d','1/1/2009','12/29/2009')+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff('d',tbl_A.AStart,tbl_A.AEnd) END,CASE WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' Then DateDiff('d','1/1/2009',tbl_A.AEnd)+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff('d',tbl_A.AStart,'12/29/2009')+1,1))))))) END AS Range,(Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') Then 0 END,CASE WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' Then DateDiff('d','1/1/2009','12/29/2009')+1 END,CASE WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff('d',tbl_A.AStart,tbl_A.AEnd) END,CASE WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' Then DateDiff('d','1/1/2009',tbl_A.AEnd)+1 END, CASE WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff('d',tbl_A.AStart,'12/29/2009')+1,1)))))))/(DateDiff('d','1/1/2009','12/29/2009')+1) END AS Percent FROM ((tbl_A INNER JOIN tbl_T ON tbl_A.TID = tbl_T.TID) INNER JOIN tbl_S ON tbl_A.SID = tbl_S.SID) INNER JOIN tbl_P ON tbl_T.PID = tbl_P.PID WHERE ((tbl_S.ST) LIKE '%') AND ((tbl_S.Co) LIKE '%') AND ((tbl_S.PEnd) Is Null Or (tbl_S.PEnd)>=Date()) AND ((tbl_P.PID)<>262)) GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co, '1/1/2009', '12/29/2009' ORDER BY tbl_S.Surname , tbl_S.Nickname;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 09:49:49

Okay, there are a few errors, I'm not sure I cuaght them all, I'd be quite surprised if I did, but this is a start. First, for CASE statements the syntax is


  CASE 
      WHEN this THEN that
      WHEN theOther thatOther
      ...
      ELSE whatever
  END as [ColumnName]

Also, for the datediff, there are no '' around the part that you want
i., SELECT DATEDIFF(d,0,getdate()). Oh, and DATE() in access is getdate() in SQL


SELECT tbl_A.SID
     , tbl_S.Surname
     , tbl_S.Nickname
     , tbl_S.ST
     , tbl_S.Co
-------------------------------------------------------------------------------------------
    , Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') 
             Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') 
               Then 0  
      WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' 
           then DateDiff(d,'1/1/2009','12/29/2009')+1 
      
      WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' 
           Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd) 
      
      WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' 
           Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1 
       
      WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' 
           Then DateDiff(d,tbl_A.AStart,'12/29/2009')+1,1))))))) 
       END ) AS [Range]
------------------------------------------------------------------------------------------------
    , Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') 
              Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') 
               Then 0  
      WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' 
          Then DateDiff(d,'1/1/2009','12/29/2009')+1 
    
     WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' 
         Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd) 
    
    WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' 
        Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1 
    
    WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' 
          Then DateDiff(d,tbl_A.AStart,'12/29/2009')+1,1)))))))/(DateDiff(d,'1/1/2009','12/29/2009')+1) 
      END AS [Percent]
---------------------------------
FROM tbl_A INNER JOIN tbl_T ON 
                              tbl_A.TID = tbl_T.TID
         INNER JOIN tbl_S  ON 
                              tbl_A.SID = tbl_S.SID
         INNER JOIN tbl_P  ON 
                              tbl_T.PID = tbl_P.PID 

WHERE      (tbl_S.ST  LIKE '%'  AND  tbl_S.Co) LIKE '%'  AND  tbl_S.PEnd  Is Null) 
        Or (tbl_S.PEnd >=getDate()  AND  tbl_P.PID)<>262 
            ) 
GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co, '1/1/2009', '12/29/2009' 
ORDER BY tbl_S.Surname , tbl_S.Nickname;

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 10:05:35

This is better
Jim


SELECT tbl_A.SID
     , tbl_S.Surname
     , tbl_S.Nickname
     , tbl_S.ST
     , tbl_S.Co
-------------------------------------------------------------------------------------------
    , Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') 
                      Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') 
                    )
               Then 0  
      WHEN  tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' 
           then DateDiff(d,'1/1/2009','12/29/2009')+1 
      
      WHEN  tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' 
             Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd)
              
      
      WHEN  tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' 
           Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1 
       
      WHEN  tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' 
           Then DateDiff(d,tbl_A.AStart,'12/29/2009')+1  
       END 
 ) AS [Range]
------------------------------------------------------------------------------------------------
    , Sum(CASE WHEN  (tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') 
              Or(tbl_A.AStart <'1/1/2009' And tbl_A.AEnd <'1/1/2009') 
               Then 0  
      WHEN  tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' 
            Then DateDiff(d,'1/1/2009','12/29/2009')+1 
    
     WHEN  tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' 
         Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd) 
    
    WHEN  tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' 
        Then DateDiff(d,'1/1/2009',tbl_A.AEnd)+1 
    
    WHEN  tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' 
          Then (DateDiff(d,tbl_A.AStart,'12/29/2009')+1 )/(DateDiff(d,'1/1/2009','12/29/2009')+1) 
      END )AS [Percent]
---------------------------------
FROM tbl_A INNER JOIN tbl_T ON 
                              tbl_A.TID = tbl_T.TID
         INNER JOIN tbl_S  ON 
                              tbl_A.SID = tbl_S.SID
         INNER JOIN tbl_P  ON 
                              tbl_T.PID = tbl_P.PID 

WHERE      (tbl_S.ST  LIKE '%'  AND  tbl_S.Co  LIKE '%'  AND  tbl_S.PEnd  Is Null) 
        Or (tbl_S.PEnd >=getDate()  AND  tbl_P.PID <>262 
            ) 
GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co, '1/1/2009', '12/29/2009' 
ORDER BY tbl_S.Surname , tbl_S.Nickname;

Everyday I learn something that somebody else already knew

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-29 : 11:34:05

Thanks much for your help but it still shows the same errors.

here is the string:
SELECT tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co,Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009')Then 0WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' then DateDiff(d,'1/1/2009','12/29/2009')+1WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd)WHEN (tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<='12/29/2009' Then DateDiff(d,1/1/2009,tbl_A.AEnd)+1WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff(d,tbl_A.AStart,12/29/2009)+1,1))))))) END ) AS [Range],Sum(CASE WHEN ((tbl_A.AStart>'12/29/2009' And tbl_A.AEnd>'12/29/2009') Or(tbl_A.AStart<'1/1/2009' And tbl_A.AEnd<'1/1/2009') Then 0WHEN (tbl_A.AStart<='1/1/2009' And tbl_A.AEnd>='12/29/2009' Then DateDiff(d,1/1/2009,12/29/2009)+1WHEN (tbl_A.AStart>='1/1/2009'And tbl_A.AEnd<='12/29/2009' Then DateDiff(d,tbl_A.AStart,tbl_A.AEnd)WHEN (tbl_A.AStart<1/1/2009 And tbl_A.AEnd<='12/29/2009' Then DateDiff(d,1/1/2009,tbl_A.AEnd)+1 WHEN (tbl_A.AStart>='1/1/2009' And tbl_A.AEnd>'12/29/2009' Then DateDiff(d,tbl_A.AStart,12/29/2009)+1,1)))))))/(DateDiff(d,1/1/2009,12/29/2009)+1) END) AS [Percent] FROM tbl_A INNER JOIN tbl_TON tbl_A.Trip_ID = tbl_T.Trip_ID INNER JOIN tbl_S ON tbl_A.SID = tbl_S.SID INNER JOIN tbl_P ON tbl_T.PID = tbl_P.PID WHERE ((tbl_S.ST) LIKE '%') AND ((tbl_S.Co) LIKE '%') AND ((tbl_S.PEnd) Is Null Or (tbl_S.PEnd)>=GetDate()) AND ((tbl_P.PID)<>262)) GROUP BY tbl_A.SID, tbl_S.Surname, tbl_S.Nickname, tbl_S.ST, tbl_S.Co, '1/1/2009', '12/29/2009' ORDER BY tbl_S.Surname , tbl_S.Nickname;

Can IFF function work in sql server? Can I modify this in Access instead of using CASE?

" (Sum(IIf((tbl_A.AStart>'" & Request.Form("ERange") & "' " & _
" And tbl_A.AEnd>'" & Request.Form("ERange") & "') Or " & _
" (tbl_A.AStart<'" & Request.Form("SRange") & "' And " & _
" tbl_A.AEnd<'" & Request.Form("SRange") & "'),0, " & _
"IIf(tbl_A.AStart>='" & Request.Form("SRange") & "' And " & _
" tbl_A.AEnd>'" & Request.Form("ERange") & "', " & _
"DateDiff('d',tbl_A.AStart,'" & Request.Form("ERange") & "')+1,1))))))) AS Range"

thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 11:39:06

Copy and paste the second query I gave you directly in to sql server and see if it runs. It doesn't give me any syntax errors. Right now it looks like the problem is with all the parentheses not matching up.

Jim

Everyday I learn something that somebody else already knew

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-29 : 12:28:59

Please copy the query I gave you and see if it runs. The query you posted that is not working is different than the one I am asking you test. There is no IIF in SQL Server, just IF, which you can't use here.

Jim

Everyday I learn something that somebody else already knew

prettyjenny
Yak Posting Veteran

57 Posts

Posted - 2009-12-29 : 13:36:58

Perfect. It worked.

That's right. It is just the parentheses.

Big thanks, woh.

There is no stupid question.
Asian Girls at Free Online Dating

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources