June 15, 2016 at 1:45 am
hi guys
I'm getting the following error...
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ';'.
Msg 102, Level 15, State 1, Line 43
Incorrect syntax near ')'.
I'm new to WITH and INSERT...what could be issue be please? I've taken out the semicolon at the front...I've taken it to the back...just keeps on with different errors
my statement is..
declare @date1 datetime='2016-06-01'
declare @date2 datetime='2016-06-20'
INSERT
INTO #dummy1 (tqm_call_data_id, std_questions_selected, Dept,call_reference_no)
;WITH MyOne as (
select DISTINCT
cd.tqm_call_data_id,
Convert(varchar(max),
std_questions_selected) std_questions_selected,
ou.firstname + ' ' + ou.surname [QC Consultant],
organ.Dept,
TLEAD,
agent_name,
cd.employee_number,
case when datediff(dd, organ.T_start_date, getdate())<92 then 'B' else 'M' End as Employee_type,
call_date,
call_reference_no,
service_perfomed,
coaching.benchmark,
Ct.name [Coaching Type],
st.name[Callback Status],
percentage,cdr.name [Call Direction]
FROM[CSI_NEW].[dbo].[tqm_call_data] cd LEFT JOIN
[CSI_NEW].[dbo].coaching ON
cd.tqm_call_data_id=coaching.tqm_call_data_id LEFT JOIN
[CSI_NEW].[dbo].coaching_type ct ON
coaching.coaching_type_id=ct.coaching_type_id LEFT JOIN
[CSI_NEW].[dbo].lookup_call_back_status st ON
cd.lookup_call_back_status_id=st.lookup_call_back_status_id LEFT JOIN
[eWFM].[dbo].[eWFM_organogram] organ ON
cd.employee_number COLLATE DATABASE_DEFAULT =organ.FNUM COLLATE DATABASE_DEFAULT LEFT JOIN
[CSI_NEW].[dbo].osty_user ou ON
cd.assigned_osty_user_id=ou.osty_user_id LEFT JOIN
[CSI_NEW].[dbo].call_direction cdr ON
cd.call_direction_id=cdr.call_direction_id --inner join
--CSI_NEW..HR_SB_QA z on ou.employee_number COLLATE DATABASE_DEFAULT =z.QAFNUM COLLATE DATABASE_DEFAULT
whereConvert(date,call_date) between @date1 AND @date2
AND STS_ID='C' AND percentage>=0 AND cd.lookup_call_back_status_id<>6
and cd.once_off_questions_selected is NULL AND Dept NOT IN ('MATERNITY WARD') and cd.once_off_work_type_id IS NULL
AND cd.std_questions_selected IS NOT NULL
)
all help will be appreciated.
thanks
June 15, 2016 at 2:00 am
;WITH MyOne AS (yada yada yada)
INSERT INTO #dummy1
(tqm_call_data_id, std_questions_selected, Dept, call_reference_no)
SELECT tqm_call_data_id, std_questions_selected, Dept, call_reference_no
FROM MyOne
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 15, 2016 at 2:06 am
First of all, you don't need a CTE for this. The CTE would be useful if you were transforming the data prior to inserting it (at a basic level think of the CTE as a temporary table, where you can manipulate the date prior to some other work). In this case it looks like the straightforward SELECT gives you the columns you need.
Secondly, when using INSERT...INTO you can only SELECT the same number of columns that you want to INSERT. So if you want to insert into TableA.Col1, Col2 and Col3 you can't SELECT Col1, Col2, Col3 and Col4 - there are a different number of columns selected than are required for the insert.
I can't vouch for the results, but the following code returns no syntax errors:
declare @date1 datetime='2016-06-01'
declare @date2 datetime='2016-06-20'
INSERT
INTO #dummy1 (tqm_call_data_id, std_questions_selected, Dept,call_reference_no)
select DISTINCT
cd.tqm_call_data_id,
Convert(varchar(max),
std_questions_selected) std_questions_selected,
organ.Dept,
call_reference_no
FROM[CSI_NEW].[dbo].[tqm_call_data] cd LEFT JOIN
[CSI_NEW].[dbo].coaching ON
cd.tqm_call_data_id=coaching.tqm_call_data_id LEFT JOIN
[CSI_NEW].[dbo].coaching_type ct ON
coaching.coaching_type_id=ct.coaching_type_id LEFT JOIN
[CSI_NEW].[dbo].lookup_call_back_status st ON
cd.lookup_call_back_status_id=st.lookup_call_back_status_id LEFT JOIN
[eWFM].[dbo].[eWFM_organogram] organ ON
cd.employee_number COLLATE DATABASE_DEFAULT =organ.FNUM COLLATE DATABASE_DEFAULT LEFT JOIN
[CSI_NEW].[dbo].osty_user ou ON
cd.assigned_osty_user_id=ou.osty_user_id LEFT JOIN
[CSI_NEW].[dbo].call_direction cdr ON
cd.call_direction_id=cdr.call_direction_id --inner join
--CSI_NEW..HR_SB_QA z on ou.employee_number COLLATE DATABASE_DEFAULT =z.QAFNUM COLLATE DATABASE_DEFAULT
whereConvert(date,call_date) between @date1 AND @date2
AND STS_ID='C' AND percentage>=0 AND cd.lookup_call_back_status_id<>6
and cd.once_off_questions_selected is NULL AND Dept NOT IN ('MATERNITY WARD') and cd.once_off_work_type_id IS NULL
AND cd.std_questions_selected IS NOT NULL
June 15, 2016 at 2:09 am
thank you for the reply.
I want to use a CTE because I need to do more 'additions' with the 'WITH' table
June 15, 2016 at 2:32 am
llouw1 (6/15/2016)
thank you for the reply.I want to use a CTE because I need to do more 'additions' with the 'WITH' table
llouw1 (6/15/2016)
thank you for the reply.I want to use a CTE because I need to do more 'additions' with the 'WITH' table
In which case you need the CTE part before the INSERT, as shown by ChrisM@Work. But don't put a semi-colon as part of the WITH or we'll have to stake you out on the ground and pour ants on you. The previous statement must end with ';' but if the WITH is the first statement in the code then the ';' isn't required.
June 15, 2016 at 3:31 am
Thank you Braindonor....
wow that I should come to such a abrupt end to my life here on earth..:-D
ok I've changed my code a bit (leaving the INSERT part for a bit later...)
firstly a bit about the data...
Per call_reference_no (we are a callcentre) some standard questions get asked.
e.g. for call Rr16060175070 the combination of standard questions asked are 6,7,18 etc
the expected responses for question 6 is yes, for 7 it is yes...etc. (we use to sum up how customers 'conformed' to certain questions.
my code looks like this..
with K as (
FIRSTPART BEGIN
select Dept,a.TLead,agent_name,employee_number,call_reference_no,ssq.question Question
,Items ,SUBSTRING(Items,0,CHARINDEX(':',Items)) Question_ID
--into #tmpTb1
from #tmpTQM a
CROSS APPLY [Email Management].dbo.SplitString(a.std_questions_selected,' ') ab
LEFT JOIN [CSI_NEW].[dbo].tqm_scoring_question ssq
ON SUBSTRING(Items,0,CHARINDEX(':',Items)) =ssq.tqm_scoring_question_id
where
std_questions_selected IS NOT NULL --FIRSTPART END
)
select Dept,
ds.Item as MY_test,
TLEAD,
agent_name,k.Question_ID
from K
cross apply dbo.DelimitedSplit8K(k.Items,':') ds;
just to explain....in the first part (marked FIRSTPART Begin and FIRSTPART end)...
(the SplitString function in the code splits this combination of answers (fieldname = 'std_questions_selected') in this manner...at least now I have the question number isolated.
the rest of the code (all) is supposed to take the field ITEMS....and split it further..
in the end I want...
question answer
6 yes
7 no
so that I can count all the replies per question.
however if I run the whole query, I get this...(see Output2.xls)..I understand in field MY_test I get both values, but I only want the 'NO' or 'YES' part.
hope this all makes sense. Probably something really stupid....
thanks
June 15, 2016 at 7:18 am
BrainDonor (6/15/2016)
First of all, you don't need a CTE for this. The CTE would be useful if you were transforming the data prior to inserting it (at a basic level think of the CTE as a temporary table, where you can manipulate the date prior to some other work). In this case it looks like the straightforward SELECT gives you the columns you need.
You should not think of this as a temporary table. It's a temporary view. The main difference is that if it were a temporary table, the CTE would be run and stored once and then read as many times as the CTE was referenced; with the temporary view it's run every time the CTE is referenced. So if the query in your CTE is expensive, it will need to pay that expense every time the CTE is referenced. If your CTE is non-deterministic, you may not get the results you expect. For instance if you have a ROW_NUMBER with a non-deterministic order, you may get different orderings when doing a self join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 15, 2016 at 7:50 am
I have to ask....why are you using two different splitters?
SplitString
&
DelimitedSplit8K
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 15, 2016 at 7:58 am
thank you
June 15, 2016 at 7:59 am
splitstring is an existing function on our server that I didnt want to mess with...
June 15, 2016 at 8:07 am
llouw1 (6/15/2016)
splitstring is an existing function on our server that I didnt want to mess with...
I would recommend using one or the other. Using multiple splitters is awfully confusing.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply