UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Error On Nested Iif, SQL Server 2012    
 
   
bshfdan
post Dec 4 2017, 09:19 AM
Post#1



Posts: 64
Joined: 17-March 04



I get this error on the following code: I cant seem to get the nested IIF statement to work without the error. Thanks for your help

Msg 102, Level 15, State 1, Line 17
Incorrect syntax near ')'.

Insert into tbl_longevity_MUNIS_1
SELECT prem_proj,
prem_emp,
prem_lname,
prem_fname,
prem_loc,
prln_long,
prem_act_stat,
prem_p_bargain,
prem_hire,
prem_service,
prem_p_jclass,
prem_p_org,
prem_p_obj,
prem_d_proj,
years_of_service,
IIf([years_of_service] Between 6 And 9.99,125,IIf([years_of_service] Between 10 And 14.999,175,IIf([years_of_service] Between 15 And 19.999,250,IIf([years_of_service]>19.999,350)))) AS long_amt,
IIf([years_of_service] Between 6 And 9.99,185,IIf([years_of_service] Between 10 And 14.999,230,IIf([years_of_service] Between 15 And 19.999,310,IIf([years_of_service]>19.999,410)))) AS p_long_amt,
prep_ann_sal,
prem_inact,
prem_inact_date,
prem_term,
prem_term_date
FROM tbl_longevity_city_accts
WHERE (long_amt > 0) or (p_long_amt > 0)
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2017, 09:36 AM
Post#2


UA Admin
Posts: 31,237
Joined: 20-June 02
From: Newcastle, WA


I think the problem is that you are missing the final argument in the last Iif() in BOTH calculations:

IIf([years_of_service]>19.999,350, MISSING ARGUMENT HERE)

--------------------
Go to the top of the page
 
penfold098
post Dec 4 2017, 09:37 AM
Post#3



Posts: 131
Joined: 5-March 14



May I make a suggestion to simplify the IIF statements? Why don't you put your greater-than criteria in reverse order, so you don't have to use ranges.

Ex (first IIF):
IIf([years_of_service]>=20, 350, _
IIf([years_of_service]>=15, 250, _ ' Since [yos] wasn't >= 20, this test automatically tests if [yos] >=15, but less than 20.
IIf([years_of_service]>=10, 175, _ ' Tests if [yos] >=10, but less than 15
IIf([years_of_service]>=6 , 125, null)))) ' [yos]>=6 AND [yos]<10

I added the NULL to the final false-value for completeness. Change it to whatever the value should be for [yos]<6.

HTH
Go to the top of the page
 
GroverParkGeorge
post Dec 4 2017, 09:41 AM
Post#4


UA Admin
Posts: 31,237
Joined: 20-June 02
From: Newcastle, WA


Excellent idea regarding alternative logic. Thanks.

--------------------
Go to the top of the page
 
bshfdan
post Dec 5 2017, 06:09 AM
Post#5



Posts: 64
Joined: 17-March 04



Thank You for your help.
Go to the top of the page
 
bshfdan
post Dec 5 2017, 06:09 AM
Post#6



Posts: 64
Joined: 17-March 04



Thank You for your help.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:55 AM