SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Incorrect syntax near the keyword 'IF'.


Incorrect syntax near the keyword 'IF'.

Author
Message
mashikoo
mashikoo
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 163
Incorrect syntax near the keyword 'IF'.
I'm a new SQLServer DBA while trying to wite the code below, I get this error message "Incorrect syntax near the keyword 'IF'." Please help.


declare @trans_num as varchar(10)
declare @student as varchar(10)

select @trans_num = 'E7281649' --Nomination E number
select @student = '45854' --Employee number

--First change the nominee

Update [nominees]
set
[nominee_no_emp]= @student
where id=@trans_num

select * from course_histories where no_sch=(select no_sch from nominations where id=@trans_num) and no_emp=(select nominee_no_emp from nominees where id=@trans_num)
select * from course_history_dtl where no_chd_po=@trans_num

--Second insert COURSE_HISTORIES entry
insert into course_histories (no_emp, cd_crs, no_ch_seq, no_sch, at_cd_fee, cd_ch_enrolby, dt_ch_compdt, qy_crs_ceu, cd_org, nm_lst_updt, dt_lst_updt, tx_ch_suser1, tx_ch_suser2, tx_ch_suser3, tx_ch_suser4, tx_ch_suser5, no_ch_nuser1, no_ch_nuser2, cd_seg)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.no_sch as 'no_sch',
(case
when nom.ob_tuition is null then '0.0'
else nom.ob_tuition
end) as 'at_cd_fee',
'SYSADM' as 'cd_ch_enrolby',
GETDATE() 'dt_ch_compdt',
nom.duty_hours as 'qy_crs_ceu',
e1.cd_org as 'cd_org',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
e2.tx_emp_suser1 as 'tx_ch_suser1',
e2.ad_emp_add1 + '/' + e2.ad_emp_add2 as 'tx_ch_suser2',
e2.ad_emp_email as 'tx_ch_suser3',
substring(e2.no_emp_phone1,1,3) + substring(e2.no_emp_phone1,5,3) + substring(e2.no_emp_phone1,9,4) as 'tx_ch_suser4',
a.contact_name + '/' + a.contact_phone as 'tx_ch_suser5',
null as 'no_ch_nuser1', --This field mirrors the class number and is vital for completion on training report
replicate('', 6-len(convert(varchar,e2.no_emp_nuser2))) + convert(varchar,e2.no_emp_nuser2) + '.' +
replicate('', 6-len(convert(varchar,e2.no_emp_nuser1))) + convert(varchar,e2.no_emp_nuser1) as 'no_ch_nuser2',
'DWD' as 'cd_seg'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join apprv_group a
on nom.apprv_group_id = a.apprv_group_id
where
nom.id=@trans_num

--Last insert COURSE_HISTORY_DTL entry
insert into course_history_dtl (no_emp, cd_crs, no_ch_seq, no_chd_po, cd_chd_bstat, cd_chd_btype, dt_chd_invoice, no_chd_regauth, cd_chd_pmeth, nm_lst_updt, dt_lst_updt, id_ch, dt_chd_edate)
IF EXISTS (select transaction_date from invoice_log where doc_no=@trans_num)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
i.transaction_date as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join invoice_log i
on nom.id = i.doc_no
where
nom.id=@trans_num
ELSE
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
null as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
where
nom.id=@trans_num
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13018 Visits: 4588
The syntax looks fine to me. You are using variables in your SQL, did you declare them first?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
mashikoo
mashikoo
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 163
Thanks. Yes I did actually here is the full syntax, but I still get the error code:

declare @trans_num as varchar(10)
declare @student as varchar(10)

select @trans_num = 'E7281649' --Nomination E number
select @student = '45854' --Employee number

--First change the nominee

Update [nominees]
set
[nominee_no_emp]= @student
where id=@trans_num

select * from course_histories where no_sch=(select no_sch from nominations where id=@trans_num) and no_emp=(select nominee_no_emp from nominees where id=@trans_num)
select * from course_history_dtl where no_chd_po=@trans_num

--Second insert COURSE_HISTORIES entry
insert into course_histories (no_emp, cd_crs, no_ch_seq, no_sch, at_cd_fee, cd_ch_enrolby, dt_ch_compdt, qy_crs_ceu, cd_org, nm_lst_updt, dt_lst_updt, tx_ch_suser1, tx_ch_suser2, tx_ch_suser3, tx_ch_suser4, tx_ch_suser5, no_ch_nuser1, no_ch_nuser2, cd_seg)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.no_sch as 'no_sch',
(case
when nom.ob_tuition is null then '0.0'
else nom.ob_tuition
end) as 'at_cd_fee',
'SYSADM' as 'cd_ch_enrolby',
GETDATE() 'dt_ch_compdt',
nom.duty_hours as 'qy_crs_ceu',
e1.cd_org as 'cd_org',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
e2.tx_emp_suser1 as 'tx_ch_suser1',
e2.ad_emp_add1 + '/' + e2.ad_emp_add2 as 'tx_ch_suser2',
e2.ad_emp_email as 'tx_ch_suser3',
substring(e2.no_emp_phone1,1,3) + substring(e2.no_emp_phone1,5,3) + substring(e2.no_emp_phone1,9,4) as 'tx_ch_suser4',
a.contact_name + '/' + a.contact_phone as 'tx_ch_suser5',
null as 'no_ch_nuser1', --This field mirrors the class number and is vital for completion on training report
replicate('', 6-len(convert(varchar,e2.no_emp_nuser2))) + convert(varchar,e2.no_emp_nuser2) + '.' +
replicate('', 6-len(convert(varchar,e2.no_emp_nuser1))) + convert(varchar,e2.no_emp_nuser1) as 'no_ch_nuser2',
'DWD' as 'cd_seg'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join apprv_group a
on nom.apprv_group_id = a.apprv_group_id
where
nom.id=@trans_num

--Last insert COURSE_HISTORY_DTL entry
insert into course_history_dtl (no_emp, cd_crs, no_ch_seq, no_chd_po, cd_chd_bstat, cd_chd_btype, dt_chd_invoice, no_chd_regauth, cd_chd_pmeth, nm_lst_updt, dt_lst_updt, id_ch, dt_chd_edate)
IF EXISTS (select transaction_date from invoice_log where doc_no=@trans_num)
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
i.transaction_date as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
inner join invoice_log i
on nom.id = i.doc_no
where
nom.id=@trans_num
ELSE
select
e1.no_emp as 'no_emp',
nom.cd_crs as 'cd_crs',
'1' as 'no_ch_seq',
nom.id as 'no_chd_po',
'I' as 'cd_chd_bstat',
'OS' as 'cd_chd_btype',
null as 'dt_chd_invoice',
replicate('', 8-len(convert(varchar,e2.no_emp_nuser3))) + convert(varchar,e2.no_emp_nuser3) as 'no_chd_regauth',
'SYSADM' as 'cd_chd_pmeth',
'SYSADM' as 'nm_lst_updt',
GETDATE() 'dt_lst_updt',
(select id_ch + 1 from course_history_dtl where id_ch=(select max(id_ch) from course_history_dtl)) as 'id_ch',
GETDATE() 'dt_chd_edate'
from
nominations nom inner join (nominees inner join employees e1 on nominees.nominee_no_emp = e1.no_emp)
on nom.id = nominees.id
inner join employees_dtl e2
on e2.no_emp = e1.no_emp
where
nom.id=@trans_num
John Rowan
John Rowan
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13018 Visits: 4588
Ah, now I see. You cannot use an IF construct with an INSERT statement. Look in BOL for INSERT; it must be INSERT INTO....VALUES or INSERT INTO.....SELECT.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
mashikoo
mashikoo
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 163
Ok, Thanks. I'll try it.
mashikoo
mashikoo
SSC-Enthusiastic
SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 Visits: 163
I took off the IF before the EXISTS and still got an error message. Any guidance will be appreciated.
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33426 Visits: 9518
Lookup the syntax for INSERT INTO..SELECT. You cannot put any external qualifications between the INSERT and the SELECT. If you want to filter it you should do that through either the WHERE clause or the ON clause (if it has JOINs).

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Garadin
Garadin
SSCertifiable
SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)SSCertifiable (6.7K reputation)

Group: General Forum Members
Points: 6669 Visits: 4107
You can also move the IF up a line to be before the insert statement. You'll need to add the insert again just below your else.
IE:

IF...
insert...
select...
else...
insert...
select...

That said, there's a good chance that isn't going to do what you want it to do. Hard to say without more information. That exists check is suspect.

Seth Phelabaum
Consistency is only a virtue if you're not a screwup. ;-)

Links: How to Post Sample Data :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search