By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

How can i convert this query into sql server whereas this is working in MS ACCESS

P: 1
SELECT tblProposals.District_Code, tblProposals.PFT_Code, tblProposals.Village_code, tblProposals.Activity_code, tblProposals.SubActivity_code, tblProposals.GSGrant, tblProposals.GSLoan, tblProposals.OwnContribution
FROM tblProposals WHERE (((tblProposals.Level)='1') AND (tblProposals.GSGrant=0) AND (tblProposals.GSLoan=0) AND tblproposals.Activity_Code in ('01','02','04','05','06','07','08','09','12','13' ,'14') and iif(tblproposals.Activity_Code='02',tblproposals.S ubActivity_Code in('01','02','04','05','06','07','08','11','12'), iif(tblproposals.Activity_Code='01',tblproposals.S ubActivity_Code in('01','02','04','05','06','08'),iif(tblproposals .Activity_Code='04',tblproposals.SubActivity_Code in('03','07','08'),iif(tblproposals.Activity_Code= '13',tblproposals.SubActivity_Code in('03','05','08'),iif(tblproposals.Activity_Code= '14',tblproposals.SubActivity_Code in('02'),iif(tblproposals.Activity_Code='08',tblpr oposals.Activity_Code in('08'),iif(tblproposals.Activity_Code='05',tblpr oposals.Activity_Code in('05'),iif(tblproposals.Activity_Code='06',tblpr oposals.Activity_Code in('06'),iif(tblproposals.Activity_Code='07',tblpr oposals.Activity_Code in('07'),iif(tblproposals.Activity_Code='09',tblpr oposals.Activity_Code in('09'),iif(tblproposals.Activity_Code='12',tblpr oposals.Activity_Code in('12')))))))))))))
Nov 12 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
I was reading your code and i stopped after I saw an IIF. Try using CASE..WHEN..ELSE..END instead.

Here's the full syntax.

Good Luck!!!

--- CK
Nov 12 '09 #2

Delerna
Expert 100+
P: 1,134
when posting code you should make it as easy as possible for us to read
This doesn't take long to do, is much easier to read and will encourage us to help you.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblProposals.District_Code, tblProposals.PFT_Code, 
  2.        tblProposals.Village_code, tblProposals.Activity_code, 
  3.        tblProposals.SubActivity_code, tblProposals.GSGrant, 
  4.        tblProposals.GSLoan, tblProposals.OwnContribution
  5. FROM tblProposals 
  6. WHERE (((tblProposals.Level)='1') 
  7.   AND (tblProposals.GSGrant=0) 
  8.   AND (tblProposals.GSLoan=0) 
  9.   AND tblproposals.Activity_Code in ('01','02','04','05','06','07','08','09','12','13' ,'14') 
  10.   AND iif(tblproposals.Activity_Code='02',
  11.           tblproposals.SubActivity_Code IN ('01','02','04','05','06','07','08','11','12'), 
  12.           iif(tblproposals.Activity_Code='01',
  13.               tblproposals.SubActivity_Code in('01','02','04','05','06','08'),
  14.               iif(tblproposals .Activity_Code='04',
  15.                   tblproposals.SubActivity_Code in('03','07','08'),
  16.                   iif(tblproposals.Activity_Code= '13',
  17.                       tblproposals.SubActivity_Code in('03','05','08'),
  18.                       iif(tblproposals.Activity_Code= '1',
  19.                           tblproposals.SubActivity_Code in('02'),
  20.                           iif(tblproposals.Activity_Code='08',
  21.                               tblproposals.Activity_Code in('08'),
  22.                               iif(tblproposals.Activity_Code='05',
  23.                                   tblpr oposals.Activity_Code in('05'),
  24.                                   iif(tblproposals.Activity_Code='06',
  25.                                       tblproposals.Activity_Code in('06'),
  26.                                       iif(tblproposals.Activity_Code='07',
  27.                                           tblproposals.Activity_Code in('07'),
  28.                                           iif(tblproposals.Activity_Code='09',
  29.                                               tblproposals.Activity_Code in('09'),
  30.                                               iif(tblproposals.Activity_Code='12',
  31.                                                   tblproposals.Activity_Code in('12')
  32.             )))))))))))) 
  33.  
Notice the IIF's are in this structure
Expand|Select|Wrap|Line Numbers
  1. IIF(condition,
  2.     true response,
  3.     false response
  4.    )
  5.  
and each nested IIF is indented to show that it is nested.


Looking at that I don't understand the point of all the IIF's
They look to me to be just breaking down this check
Expand|Select|Wrap|Line Numbers
  1. AND tblproposals.Activity_Code in ('01','02','04','05','06','07','08','09','12','13' ,'14') 
  2.  
and re-doing each one on an individual basis.

I could be wrong but arent all those iff's already covered with
Expand|Select|Wrap|Line Numbers
  1. SELECT tblProposals.District_Code, tblProposals.PFT_Code, 
  2.        tblProposals.Village_code, tblProposals.Activity_code, 
  3.        tblProposals.SubActivity_code, tblProposals.GSGrant, 
  4.        tblProposals.GSLoan, tblProposals.OwnContribution
  5. FROM tblProposals 
  6. WHERE (((tblProposals.Level)='1') 
  7.   AND (tblProposals.GSGrant=0) 
  8.   AND (tblProposals.GSLoan=0) 
  9.   AND tblproposals.Activity_Code IN ('01','02','04','05','06','07','08','09','12','13' ,'14') 
  10.  
although I do notice a couple of "SubActivity_Code" fields in amongst all of those IIF's so maybe you need those ones

As suggested by ck

CASE WHEN condition THEN true response ELSE false response END

is the way to go, the only issue is that you can only have 10 levels of nesting
Nov 12 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.