ERROR
-
HI
CAN ANY LET ME KNOW where iam going wrong
SELECT CN.TPA_ID,
CN.EMPE_KEY,
CN.[step_id],'+
'CASE CN.[msg_delivery_options]'+''''+' WHEN 3
THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed')'+''''+' WHEN 2
THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.mail_dte,101),'Not Mailed')'+''''+' WHEN 1
THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed')'+''''+' WHEN 0
THEN '+ ''''+' ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'None')'+''''+'END as notification_dte,
CASE CN.[msg_delivery_options]
WHEN 3
THEN '+ ''''+'Letter & Emai'+''''+' WHEN 2
THEN '+ ''''+'Letter'+''''+' WHEN 1
THEN '+ ''''+'E-Mail'+''''+' WHEN 0
THEN'+ ''''+'None'+''''+' END as notification_method 'FROM FX.[txn_amt]
error
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CASE'.
Server: Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'CASE'.
-
Alot... the very first '+ is followed on the next line by a single ' which ends the string literal which is followed by CASE so SQL Server see's it as '+'CASE and it has no idea what to do with it.
It's a bit difficult to tell what you're actually trying to do because of the backwards way you have used CASE (lot's of lines with THEN... WHEN...) but here's a shot at it...
SELECT CN.TPA_ID,
CN.EMPE_KEY,
CN.[step_id],
CASE CN.[msg_delivery_options]
WHEN 3 THEN '''' + ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed') + ''''
WHEN 2 THEN '''' + ISNULL(CONVERT(VARCHAR,CN.mail_dte ,101),'Not Mailed') + ''''
WHEN 1 THEN '''' + ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'Not Mailed') + ''''
WHEN 0 THEN '''' + ISNULL(CONVERT(VARCHAR,CN.email_dte,101),'None') + ''''
END AS notification_dte,
CASE CN.[msg_delivery_options]
WHEN 3 THEN '''' + 'Letter & Email' + ''''
WHEN 2 THEN '''' + 'Letter' + ''''
WHEN 1 THEN '''' + 'E-Mail' + ''''
WHEN 0 THEN '''' + 'None' + ''''
END as notification_method
FROM FX.[txn_amt] --<<THIS NEEDS TO BE A TABLE NAME!!!! YOU NEED TO CHANGE THIS!!!!
The first letter of "SQL" stands for "Structured". Since it appears that you're very new at this, lemme help save your future life as a programmer... if you actually take the time to structure you code for very easy readability, you will avoid 95% of these types of problems, cut down on more than 50% of the time it's takes to modify code (especially when others have to read your code), and will give you something to be proud of because people will say "Wow... nicely written code" instead of "Did you see that crap? I had to reformat everything just to figure out what it did!"
It's especially important when you're asing for help on a forum 
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. Lookup QUOTENAME in Books OnLine if you need to do lot's of stuff like this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply