March 17, 2016 at 1:29 pm
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
;with cte as (select chppon, datediff(day,
case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112),
cast(CURRENT_TIMESTAMP as date)) end as Age
from clmhdr (nolock)
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON')
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte (nolock)
Group By chppon
March 17, 2016 at 1:32 pm
New error:
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Select'.
;with cte as (select chppon, datediff(day,
case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON')
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
March 17, 2016 at 1:40 pm
You're missing the closing parenthesis for your cte.
You also have an additional semi-colon at the start and a missing semi-colon at the end of your query.
March 17, 2016 at 1:44 pm
I tested that but it still doesn't like that second select for some reason
March 17, 2016 at 1:48 pm
rcooper 78099 (3/17/2016)
New error:Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'Select'.
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON')
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
change your BETWEEN to include quotes
BETWEEN '20121202' AND '20130730'
March 17, 2016 at 2:05 pm
rcooper 78099 (3/17/2016)
I tested that but it still doesn't like that second select for some reason
Try this:
with cte as (
select
chppon,
datediff(day, case when chrcdt between 19000101 and 30000101
then convert(date,cast(chrcdt as varchar(8)),112)
end, cast(CURRENT_TIMESTAMP as date)) as Age
from
clmhdr
where
chstat <> 'f' and
chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON')
)
Select
chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from
cte
Group By
chppon;
Helps if you format your code in a reasonable way. Also, if it errors, don't just tell use it failed, give us the full error message you receive.
March 17, 2016 at 2:07 pm
Solution Found:
Line: chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON') was missing second )
should of read: chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))
otherwise it was tossing the Select into the same bracket
;with cte as (select chppon, datediff(day,
case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
March 17, 2016 at 2:08 pm
rcooper 78099 (3/17/2016)
Solution Found:
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
Doesn't tell anyone what the problem was, or what you did to fix it.
March 17, 2016 at 2:12 pm
Lynn Pettis (3/17/2016)
rcooper 78099 (3/17/2016)
Solution Found:
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
Doesn't tell anyone what the problem was.
It was the same thing you and I mentioned, but magically didn't work before and now it does.
P.S. Do I need to use the sarcasm tag?
March 17, 2016 at 2:15 pm
Luis Cazares (3/17/2016)
Lynn Pettis (3/17/2016)
rcooper 78099 (3/17/2016)
Solution Found:
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
Doesn't tell anyone what the problem was.
It was the same thing you and I mentioned, but magically didn't work before and now it does.
P.S. Do I need to use the sarcasm tag?
So this:
rcooper 78099 (3/17/2016)
I tested that but it still doesn't like that second select for some reason
was a lie as he did NOT test what you told him.
You know (of course you, Luis, do) that proper formatting of code helps catch these types of errors.
March 17, 2016 at 2:19 pm
Really?
You said: You're missing the closing parenthesis for your cte.
You also have an additional semi-colon at the start and a missing semi-colon at the end of your query.
While you were correct there were other changes made between
the first post and the second post.
yours applied to the second post the word end, also had to be added to another line as well as (nolock) statements moved. No where did I add a second semi-colon because that did toss an error so yes I checked your method other things had to be changed as well.
Luis Cazares (3/17/2016)
Lynn Pettis (3/17/2016)
rcooper 78099 (3/17/2016)
Solution Found:
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
Doesn't tell anyone what the problem was.
It was the same thing you and I mentioned, but magically didn't work before and now it does.
P.S. Do I need to use the sarcasm tag?
March 17, 2016 at 2:32 pm
rcooper 78099 (3/17/2016)
Really?You said: You're missing the closing parenthesis for your cte.
You also have an additional semi-colon at the start and a missing semi-colon at the end of your query.
While you were correct there were other changes made between
the first post and the second post.
yours applied to the second post the word end, also had to be added to another line as well as (nolock) statements moved. No where did I add a second semi-colon because that did toss an error so yes I checked your method other things had to be changed as well.
Luis Cazares (3/17/2016)
Lynn Pettis (3/17/2016)
rcooper 78099 (3/17/2016)
Solution Found:
;with cte as (select chppon, datediff(day,case when chrcdt between 19000101 and 30000101 then
convert(date,cast(chrcdt as varchar(8)),112) end,
cast(CURRENT_TIMESTAMP as date)) as Age
from clmhdr
where chstat <> 'f' and chstst <> 'f' and
chrcdt BETWEEN 20121202 AND 20130730 and
[highlight="#ffff11"]chppon Not In ('OUT', 'NAB', 'NHB',' ', 'NON'))[/highlight]
Select chppon,
sum(case when Age <=15 then 1 else 0 end) as [Age 1-15 Days],
sum(case when Age between 16 and 28 then 1 else 0 end) as [Age 16-28 Days],
sum(case when Age between 29 and 45 then 1 else 0 end) as [Age 29-45 Days]
from cte
Group By chppon
Doesn't tell anyone what the problem was.
It was the same thing you and I mentioned, but magically didn't work before and now it does.
P.S. Do I need to use the sarcasm tag?
CTEs do not start with a semicolon, despite what you see in BOL. CTEs REQUIRE that the PRECEDING statement be terminated with a semicolon. Semicolons are terminators not begininators. They belong at the end of SQL statments. Microsoft has even deprecated NOT terminating SQL statements with a semicolon meaning at some future date this will become mandatory.
March 17, 2016 at 3:22 pm
rcooper 78099 (3/17/2016)
Really?You said: You're missing the closing parenthesis for your cte.
You also have an additional semi-colon at the start and a missing semi-colon at the end of your query.
While you were correct there were other changes made between
the first post and the second post.
yours applied to the second post the word end, also had to be added to another line as well as (nolock) statements moved. No where did I add a second semi-colon because that did toss an error so yes I checked your method other things had to be changed as well.
Yes, I was going to mention the nolock hints written incorrectly and the incorrect placement of your end keyword. However, I saw that you corrected those issues in your second post, the only problem left was the missing parenthesis.
I also mentioned the semi-colon because, as Lynn explained, they should terminate statements, not start them.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply