sql statement issue with inserting with a Case statemetn

Hi,
I'm having an issue with an insert statement.
I want to copy some records from one table to the other

when field "Action_name" is equal to credit I want to insert the negative of the amount. So if it says credit and the amount is 2, i want to insert -2.

I am using the following statement to do that, somehow it is giving me the message, incorrect syntax near '('

the code is as follows

insert into  [dbo].[tbl_visa_imports](
      
      [cc] ,
      [exp] ,
      [media_name] ,
      [trans_date] ,
      [transid] ,
      [ref_transid] ,
      (case
      when action_name = 'credit' then abs(([amount])*-1)
      else [amount] end) ,
      [action_name] ,
      [status_name] ,
      [name] ,
      [ticket],
      
      [billingid],
      [custid],
      [fail_name],

      [operator],
      [entry_mode] ,
      [USERID] ,
      [CASHDRAWERID] ,
      [DEPTABBR] ,
      [DEPTEXTNAME] ,


      [LOCABBR] ,
      [LOCID] ,
      [LOCNAME] ,
      [PATDAT] ,
      [PATID] ,
 
      [SERVICEARBAABBR] ,
      [SERVICEAREAID] ,
      [SERVICEAREANAME] ,

      [GUARACCTID] ,
      [HOSPACCTID] ,
      [WORKSTATIONID],
      [WORKSTATIONNAME],
      assigned )
      
      select       [cc] ,
      [exp] ,
      [media_name] ,
      [trans_date] ,
      [transid] ,
      [ref_transid] ,
      [amount] ,
      [action_name] ,
      [status_name] ,
      [name] ,
      [ticket],
      
      [billingid],
      [custid],
      [fail_name],

      [operator],
      [entry_mode] ,
      [USERID] ,
      [CASHDRAWERID] ,
      [DEPTABBR] ,
      [DEPTEXTNAME] ,
      
      [LOCABBR] ,
      [LOCID] ,
      [LOCNAME] ,
      [PATDAT] ,
      [PATID] ,

      [SERVICEARBAABBR] ,
      [SERVICEAREAID] ,
      [SERVICEAREANAME] ,

      [GUARACCTID] ,
      [HOSPACCTID] ,
      [WORKSTATIONID],
      [WORKSTATIONNAME],
      0
      from dbo.tbl_imports

thanks in advance
Vinnie

Addition: my main problem is in the Case Statement. If I was to take out the case statement, it works fine

   (case 
      when action_name = 'credit' then abs(([amount])*-1)
      else [amount] end) ,

Open in new window

damixaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

damixaAuthor Commented:
So to clarify, my main problem is in the case statement

   (case 
      when action_name = 'credit' then abs(([amount])*-1)
      else [amount] end) ,

Open in new window

0
jleenotloCommented:
Take out the abs() because that would give you a positive number instead of a negative number.

(case
      when action_name = 'credit' then ([amount]*-1)
      else [amount] end)
0
damixaAuthor Commented:
Right but I want a positive number. That way the outcome is always negative. So by adding the abb and multiplying it by neg 1 it assures me that the outcome is always the negative number.

I did try that though, and it still gave me the same error.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I am using the following statement to do that, somehow it is giving me the message, incorrect syntax near '('

The CASE statement is currently in the INSERT clause, which is causing the syntax error.  It belongs in the SELECT clause that lines up with a column where that CASE block is to insert values.
0
Kelvin SparksCommented:
The Case statement is in the wrong part. The insert statement must have the field name you are inserting to and the case statement should be in the select statement.


Kelvin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damixaAuthor Commented:
Kelvin, I'm so stupid. Thanks
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>when field "Action_name" is equal to credit I want to insert the negative of the amount. So if it says credit and the amount is 2, i want to insert -2.

Replace column_name with the name of the column that you're trying to insert the expression into, guessing it's amount.
INSERT INTO tbl_visa_imports (blah, blah, column_name)
SELECT blah, blah, 
 CASE WHEN Action_Name = credit then amount * -1 ELSE amount END
FROM Imports

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
damixa - Let me know if my first comment did not meet your needs.
0
duttcomCommented:
You can put the case statement in the select statement rather than the insert.

Your insert statement would only have [amount] rather than the case statement and the case statement would be in the select statement like so-

[ref_transid] ,
CASE WHEN action_name = 'credit'
then abs(([amount])*-1)
else [amount]
END,
 [action_name]

The whole case statement is the value being selected to insert.
0
Kelvin SparksCommented:
No problems - only recognised it as I'd spent a few hours a short while ago finding the exact same issue in my own code.

Glad I could help.


Kelvin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.