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

[Prashant Pratap Kandari]

SELECT tblProposals.Di strict_Code, tblProposals.PF T_Code, tblProposals.Vi llage_code, tblProposals.Ac tivity_code, tblProposals.Su bActivity_code, tblProposals.GS Grant, tblProposals.GS Loan, tblProposals.Ow nContribution
FROM tblProposals WHERE (((tblProposals .Level)='1') AND (tblProposals.G SGrant=0) AND (tblProposals.G SLoan=0) AND tblproposals.Ac tivity_Code in ('01','02','04' ,'05','06','07' ,'08','09','12' ,'13','14') and iif(tblproposal s.Activity_Code ='02',tblpropos als.SubActivity _Code in('01','02','0 4','05','06','0 7','08','11','1 2'), iif(tblproposal s.Activity_Code ='01',tblpropos als.SubActivity _Code in('01','02','0 4','05','06','0 8'),iif(tblprop osals.Activity_ Code='04',tblpr oposals.SubActi vity_Code in('03','07','0 8'),iif(tblprop osals.Activity_ Code='13',tblpr oposals.SubActi vity_Code in('03','05','0 8'),iif(tblprop osals.Activity_ Code='14',tblpr oposals.SubActi vity_Code in('02'),iif(tb lproposals.Acti vity_Code='08', tblproposals.Ac tivity_Code in('08'),iif(tb lproposals.Acti vity_Code='05', tblproposals.Ac tivity_Code in('05'),iif(tb lproposals.Acti vity_Code='06', tblproposals.Ac tivity_Code in('06'),iif(tb lproposals.Acti vity_Code='07', tblproposals.Ac tivity_Code in('07'),iif(tb lproposals.Acti vity_Code='09', tblproposals.Ac tivity_Code in('09'),iif(tb lproposals.Acti vity_Code='12', tblproposals.Ac tivity_Code in('12')))))))) )))))

[ck9663]

I was reading your code and i stopped after I saw an IIF. Try using CASE..WHEN..ELS E..END instead.

Here's the full syntax.

Good Luck!!!

--- CK

[Delerna]

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.

[code=sql]
SELECT tblProposals.Di strict_Code, tblProposals.PF T_Code,
tblProposals.Vi llage_code, tblProposals.Ac tivity_code,
tblProposals.Su bActivity_code, tblProposals.GS Grant,
tblProposals.GS Loan, tblProposals.Ow nContribution
FROM tblProposals
WHERE (((tblProposals .Level)='1')
AND (tblProposals.G SGrant=0)
AND (tblProposals.G SLoan=0)
AND tblproposals.Ac tivity_Code in ('01','02','04' ,'05','06','07' ,'08','09','12' ,'13' ,'14')
AND iif(tblproposal s.Activity_Code ='02',
tblproposals.Su bActivity_Code IN ('01','02','04' ,'05','06','07' ,'08','11','12' ),
iif(tblproposal s.Activity_Code ='01',
tblproposals.Su bActivity_Code in('01','02','0 4','05','06','0 8'),
iif(tblproposal s .Activity_Code= '04',
tblproposals.Su bActivity_Code in('03','07','0 8'),
iif(tblproposal s.Activity_Code = '13',
tblproposals.Su bActivity_Code in('03','05','0 8'),
iif(tblproposal s.Activity_Code = '1',
tblproposals.Su bActivity_Code in('02'),
iif(tblproposal s.Activity_Code ='08',
tblproposals.Ac tivity_Code in('08'),
iif(tblproposal s.Activity_Code ='05',
tblpr oposals.Activit y_Code in('05'),
iif(tblproposal s.Activity_Code ='06',
tblproposals.Ac tivity_Code in('06'),
iif(tblproposal s.Activity_Code ='07',
tblproposals.Ac tivity_Code in('07'),
iif(tblproposal s.Activity_Code ='09',
tblproposals.Ac tivity_Code in('09'),
iif(tblproposal s.Activity_Code ='12',
tblproposals.Ac tivity_Code in('12')
))))))))))))
[/code]

Notice the IIF's are in this structure
[code=sql]
IIF(condition,
true response,
false response
)
[/code]
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
[code=sql]
AND tblproposals.Ac tivity_Code in ('01','02','04' ,'05','06','07' ,'08','09','12' ,'13' ,'14')
[/code]
and re-doing each one on an individual basis.

I could be wrong but arent all those iff's already covered with
[code=sql]
SELECT tblProposals.Di strict_Code, tblProposals.PF T_Code,
tblProposals.Vi llage_code, tblProposals.Ac tivity_code,
tblProposals.Su bActivity_code, tblProposals.GS Grant,
tblProposals.GS Loan, tblProposals.Ow nContribution
FROM tblProposals
WHERE (((tblProposals .Level)='1')
AND (tblProposals.G SGrant=0)
AND (tblProposals.G SLoan=0)
AND tblproposals.Ac tivity_Code IN ('01','02','04' ,'05','06','07' ,'08','09','12' ,'13' ,'14')
[/code]

although I do notice a couple of "SubActivity_Co de" 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