Syntax error 2

[CrystalStart]

I am getting error "incorrect syntax near 'then'

count(*) as Field1,
sum(case when (seqstep <> '0001' AND seqstep IS NOT NULL then ani end)) as Field2, --Total calls,

Please, help

 

[Catadmin]

Your END statement should be between the last two parens.

)END), not END))

And I'm not sure THEN works with a CASE statement.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Catadmin]

ACK! I take back the part about the THEN. I wasn't reading my code correctly.

But definately change the postition of the END.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[Pattycake245]

count(*) as Field1,
sum(case when (seqstep <> '0001' AND seqstep IS NOT NULL) then ani end) as Field2, --Total calls,

Tim

 

[Catadmin]

<Catadmin bangs head against desk repeatedly.>

Thanks for correcting me again, PattyCake245. I swear I left the smart half of me somewhere else today.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[donutman]

<Catadmin bangs head against desk repeatedly.>

Or is it this.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Where do you get those little emoticons? I LOVE them!

And yes, that's what it feels like this week.

@=)



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[donutman]

Read the stuff below your post when you preview it. And when you open the window with emotions, there is another link at the top.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[CrystalStart]

Why can't I see it? Is there "sytax disability" condition known to the world?
because I sure have it. I had never coded extensively and now I think I will learn because it is my job :-D

Ome more look and I will try myself...
THANKS a lot

ani,
campus,
call_master.dnis, call_master.compcode, cliref1, seqstep,
case when datepart((hour,timein) = '0' then 1 else 0 end) as cORD_00,
case when datepart((hour,timein) = '1' then 1 else 0 end) as cORD_01,

 

[CrystalStart]

Error: Incorrect syntax near ','.
Line number 55 which is
call_master.dnis, call_master.compcode, cliref1, seqstep,

TAHNKS

 

[vongrunt]

 -- is
case when datepart[COLOR=red][b]([/b][/color](hour,timein) = '1' then 1 else 0 end[COLOR=red][b])[/b][/color] as  cORD_01,
-- should be
case when datepart(hour,timein) = '1' then 1 else 0 end as cORD_01,

And DATEPART() returns int, so you don't need '' around 1.

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]

 

[CrystalStart]

I am confused now because I've added those paren because it was giving me an error...now I am taking it off and it is fine...
THANKS a lot

 

[Catadmin]

Remember each part of the statement is self contained. The only times you need to use parens is when your conditional/search value contains more than one conditional or search value:

(seqstep <> '0001' AND seqstep IS NOT NULL)

or when using a Statement that requires variable insertion such as Round(), Count(), datepart(hour,timein), etc.

If you feel the need to contain your case statements in parens otherwise, I would advise a structure more like

(case when datepart(hour,timein) = '1' then 1 else 0 end as cORD_01),

or left paren before the first part of the statement and right paren after the last word of the statement. Of course, I usually only do this in nested If/Then/Elses so I can tell the statements apart for neatness and to help with parsing. I'm not sure it should be recommended for Case statements.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[CrystalStart]

Hi!
I am back to original error in a middle raw
Incorrect syntax near ')'.

count(*) as Field1, --Total calls

count (distinct case when (seqstep <> '0001' AND seqstep IS NOT NULL) then ani end) as Field3, --Unique Valid Calls

CASE WHEN CONVERT(INT, SUBSTRING(miscdat1,4,1 )) = 1 THEN 1 ELSE 0 END ) AS Field4, --Total Men Qty

Thanks

 

[Pattycake245]

You have an extra )

Remove bold

CASE WHEN CONVERT(INT, SUBSTRING(miscdat1,4,1 )) = 1 THEN 1 ELSE 0 END ) AS Field4, --Total Men Qty

Tim