convert excel formular to sql statement 2012

I have an excel spreadsheet and I'm now moving everything to a sql 2012 environment.
However there are some formulars in the spreadsheet that need converting to update statements

Invoice_or_credit is a cell A1 for example in excel this is the update
 
IF([INVOICE_OR_CREDIT]="INVOICE",[@[NET_AMOUNT]],(IF([@[NET_AMOUNT]]>0,[@[NET_AMOUNT]]*-1,[@[NET_AMOUNT]])))
 
In SQL I tried this but it fails

update [[dbo].[SageSalesnEW]
  set STG_VALUE =IF([INVOICE_OR_CREDIT]="INVOICE",[@[NET_AMOUNT]],(IF([@[NET_AMOUNT]]>0,[@[NET_AMOUNT]]*-1,[@[NET_AMOUNT]])))

this one might be a bit more tricky?

Cell AY6029 equates to field STOCK_CODE

=IF((RIGHT([@[STOCK_CODE]],3))="ALF","ALF",(IF(RIGHT([@[STOCK_CODE]],7)="RENEWAL","ALF",(IF([@[STOCK_CODE]]="ACCRED/REN/OEM","ALF",(IF((RIGHT([@[STOCK_CODE]],15))="TEL/REM/SUPPORT","ALF",(IF(RIGHT([@[STOCK_CODE]],6)="SU-REN","ALF",(IF(RIGHT([@[STOCK_CODE]],3)="REN","ALF",(IF(RIGHT([@[STOCK_CODE]],2)="SU","ALF",(IF([@[STOCK_CODE]]="LAB-TEL-REM-SUPPORT","ALF",(IF(AY26029="4250","EXPENSE","SALE")))))))))))))))))
Chris MichalczukConsultantAsked:
Who is Participating?
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.

Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

You have to use Case statement

SELECT CASE WHEN (RIGHT(@STOCK_CODE,3))='ALF' THEN 'ALF'
		ELSE 
		CASE WHEN RIGHT(@STOCK_CODE,7)='RENEWAL' THEN 'ALF'
			ELSE
			CASE WHEN @STOCK_CODE='ACCRED/REN/OEM'THEN 'ALF'
				ELSE
				CASE WHEN RIGHT(@STOCK_CODE,15)='TEL/REM/SUPPORT' THEN'ALF'
					ELSE
					CASE WHEN RIGHT(@STOCK_CODE,6)='SU-REN' THEN 'ALF'
						ELSE
						CASE WHEN RIGHT(@STOCK_CODE,3)='REN' THEN 'ALF'
							ELSE
							CASE WHEN RIGHT(@STOCK_CODE,2)='SU'THEN 'ALF'
								ELSE
								CASE WHEN @STOCK_CODE='LAB-TEL-REM-SUPPORT' THEN 'ALF'
									ELSE
									CASE WHEN AY26029='4250' THEN 'EXPENSE'ELSE 'SALE' END
									END END END END END END END END from table

Open in new window

0
Chris MichalczukConsultantAuthor Commented:
how do I use this is an update statement ie

Update table

set fieldname = then do I just paste the example above?
0
PortletPaulfreelancerCommented:
tip: it helps to divide/indent:

Although IF can be used in (some) SQL the more conventional SQL method is CASE WHEN ELSE END, along these lines
IF(
    [INVOICE_OR_CREDIT]="INVOICE",[@[NET_AMOUNT]],
    (
        IF([@[NET_AMOUNT]]>0,[@[NET_AMOUNT]]*-1,[@[NET_AMOUNT]]
        )
    )
)


case when [INVOICE_OR_CREDIT] = 'INVOICE' then [@[NET_AMOUNT]]
     when [@[NET_AMOUNT]] > 0 [@[NET_AMOUNT]] * -1
     else [@[NET_AMOUNT]]
end

Open in new window

=IF((RIGHT([@[STOCK_CODE]],
3))="ALF",
"ALF",
(IF(RIGHT([@[STOCK_CODE]],
7)="RENEWAL",
"ALF",
(IF([@[STOCK_CODE]]="ACCRED/REN/OEM",
"ALF",
(IF((RIGHT([@[STOCK_CODE]],
15))="TEL/REM/SUPPORT",
"ALF",
(IF(RIGHT([@[STOCK_CODE]],
6)="SU-REN",
"ALF",
(IF(RIGHT([@[STOCK_CODE]],
3)="REN",
"ALF",
(IF(RIGHT([@[STOCK_CODE]],
2)="SU",
"ALF",
(IF([@[STOCK_CODE]]="LAB-TEL-REM-SUPPORT",
"ALF",
(IF(AY26029="4250",
"EXPENSE",
"SALE")))))))))))))))))


CASE RIGHT([@[STOCK_CODE]],3)
     WHEN 'ALF' THEN 'ALF'
     WHEN 'ACCRED/REN/OEM' THEN 'ALF'
     WHEN 'TEL/REM/SUPPORT' THEN 'ALF'
     WHEN 'SU-REN' THEN 'ALF'
     WHEN 'REN' THEN 'ALF'
     WHEN 'SU' THEN 'ALF'
     WHEN 'LAB-TEL-REM-SUPPORT' THEN 'ALF'
     WHEN '4250' THEN 'EXPENSE'
     ELSE 'SALE'
END

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vikas GargBusiness Intelligence DeveloperCommented:
UPDATE TABLE

SET FIELD = (SELECT CASE WHEN (RIGHT(@STOCK_CODE,3))='ALF' THEN 'ALF'
		ELSE 
		CASE WHEN RIGHT(@STOCK_CODE,7)='RENEWAL' THEN 'ALF'
			ELSE
			CASE WHEN @STOCK_CODE='ACCRED/REN/OEM'THEN 'ALF'
				ELSE
				CASE WHEN RIGHT(@STOCK_CODE,15)='TEL/REM/SUPPORT' THEN'ALF'
					ELSE
					CASE WHEN RIGHT(@STOCK_CODE,6)='SU-REN' THEN 'ALF'
						ELSE
						CASE WHEN RIGHT(@STOCK_CODE,3)='REN' THEN 'ALF'
							ELSE
							CASE WHEN RIGHT(@STOCK_CODE,2)='SU'THEN 'ALF'
								ELSE
								CASE WHEN @STOCK_CODE='LAB-TEL-REM-SUPPORT' THEN 'ALF'
									ELSE
									CASE WHEN AY26029='4250' THEN 'EXPENSE'ELSE 'SALE' END
									END END END END END END END END from table)

Open in new window

0
PortletPaulfreelancerCommented:
sorry I missed something...
CASE RIGHT([@[STOCK_CODE]],3)
     WHEN 'ALF' THEN 'ALF'
     WHEN 'ACCRED/REN/OEM' THEN 'ALF'
     WHEN 'TEL/REM/SUPPORT' THEN 'ALF'
     WHEN 'SU-REN' THEN 'ALF'
     WHEN 'REN' THEN 'ALF'
     WHEN 'SU' THEN 'ALF'
     WHEN 'LAB-TEL-REM-SUPPORT' THEN 'ALF'
     WHEN '4250' THEN 'EXPENSE'
     ELSE 
         CASE WHEN AY26029='4250' THEN 'EXPENSE'
                  ELSE 'SALE' 
         END
END

Open in new window

0
PortletPaulfreelancerCommented:
It isn't necessary to have so many indented level of case...

If a row satisfies any WHEN, then processing of the case expression stops for that row

so if row 1 has value of 'REN'
these rows are evaluated:
CASE RIGHT([@[STOCK_CODE]],3)
     WHEN 'ALF' THEN 'ALF'
     WHEN 'ACCRED/REN/OEM' THEN 'ALF'
     WHEN 'TEL/REM/SUPPORT' THEN 'ALF'
     WHEN 'SU-REN' THEN 'ALF'
     WHEN 'REN' THEN 'ALF'
& stops at that point
0
Chris MichalczukConsultantAuthor Commented:
guys how do I use this in the update statement

so Paul thanks but

update table
set stg_value = case when [INVOICE_OR_CREDIT] = 'INVOICE' then [@[NET_AMOUNT]]
     when [@[NET_AMOUNT]] > 0 [@[NET_AMOUNT]] * -1
     else [@[NET_AMOUNT]]
end

is this right - also @[Net_Amount]  - this refers to a field called NET_AMOUNT so does this need changing?
0
Vikas GargBusiness Intelligence DeveloperCommented:
hi,

UPDATE TABLE
SET stg_value = CASE WHEN INVOICE_OR_CREDIT = "INVOICE" THEN NET_AMOUNT 
				ELSE 
				CASE WHEN NET_AMOUNT > 0 THEN (NET_AMOUNT)*(-1) ELSE NET_AMOUNT END END 

Open in new window

0
PortletPaulfreelancerCommented:
@Vikas: you do not need so many ELSE's!  
Each WHEN is evaluated, if it is true that is where the case expression  stops.
If it is false the next WHEN is evaluated, until all WHEN's are done
Then the ELSE is performed (if present)
-------------

@chrismichalczuk

@[Net_Amount]  - this refers to a field called NET_AMOUNT so does this need changing?
YES!

This should work.

update table
set stg_value = case when [INVOICE_OR_CREDIT] = 'INVOICE' then [NET_AMOUNT]
     when [NET_AMOUNT] > 0 [NET_AMOUNT] * -1
     else [NET_AMOUNT]
end
0
Vikas GargBusiness Intelligence DeveloperCommented:
@PortletPaul,

I think you are over confident and while commenting you forgot that in below query

when [NET_AMOUNT] > 0 [NET_AMOUNT] * -1 does not have then after when

" update table
set stg_value = case when [INVOICE_OR_CREDIT] = 'INVOICE' then [NET_AMOUNT]
     when [NET_AMOUNT] > 0 [NET_AMOUNT] * -1
     else [NET_AMOUNT]
end "

It's good to be confident but over confident is KILLER
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
PortletPaulfreelancerCommented:
Thanks VIkas, I am merely trying to help, but there is no syntactical reason to introduce an ELSE after each condition.


CASE (Transact-SQL)   (From that page)
Examples:
A. Using a SELECT statement with a simple CASE expression
USE AdventureWorks2012;
GO
SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product
ORDER BY ProductNumber;
GO

Open in new window

0
PortletPaulfreelancerCommented:
vikas is correct I forgot a then

update table
set stg_value = case when [INVOICE_OR_CREDIT] = 'INVOICE' then [NET_AMOUNT]
     when [NET_AMOUNT] > 0 THEN [NET_AMOUNT] * -1
     else [NET_AMOUNT]
end
0
Vikas GargBusiness Intelligence DeveloperCommented:
@PortletPaul,

in your comment

CASE RIGHT([@[STOCK_CODE]],3)
     WHEN 'ALF' THEN 'ALF'
     WHEN 'ACCRED/REN/OEM' THEN 'ALF'
     WHEN 'TEL/REM/SUPPORT' THEN 'ALF'
     WHEN 'SU-REN' THEN 'ALF'
     WHEN 'REN' THEN 'ALF'
     WHEN 'SU' THEN 'ALF'
     WHEN 'LAB-TEL-REM-SUPPORT' THEN 'ALF'
     WHEN '4250' THEN 'EXPENSE'
     ELSE 
         CASE WHEN AY26029='4250' THEN 'EXPENSE'
                  ELSE 'SALE' 
         END
END

You followed wrong and take RIGHT([@[STOCK_CODE]],3) as single case
but in the question of author

=IF((RIGHT([@[STOCK_CODE]],3))="ALF","ALF",(IF(RIGHT([@[STOCK_CODE]],7)="RENEWAL","ALF",(IF([@[STOCK_CODE]]="ACCRED/REN/OEM","ALF",(IF((RIGHT([@[STOCK_CODE]],15))="TEL/REM/SUPPORT","ALF",(IF(RIGHT([@[STOCK_CODE]],6)="SU-REN","ALF",(IF(RIGHT([@[STOCK_CODE]],3)="REN","ALF",(IF(RIGHT([@[STOCK_CODE]],2)="SU","ALF",(IF([@[STOCK_CODE]]="LAB-TEL-REM-SUPPORT","ALF",(IF(AY26029="4250","EXPENSE","SALE"))))))))))))))))) 

Open in new window


You can see that case condition changes from
RIGHT([@[STOCK_CODE]],3)
RIGHT([@[STOCK_CODE]],7)
RIGHT([@[STOCK_CODE]],15) and so on

So please try to correct yourself first
0
PortletPaulfreelancerCommented:
      CASE
            WHEN RIGHT([STOCK_CODE], 3)   = 'ALF' THEN 'ALF'
            WHEN RIGHT([STOCK_CODE], 7)   = 'RENEWAL' THEN 'ALF'
            WHEN [STOCK_CODE]             = 'ACCRED/REN/OEM' THEN 'ALF'
            WHEN RIGHT([STOCK_CODE], 15)  = 'TEL/REM/SUPPORT' THEN 'ALF'
            WHEN RIGHT([STOCK_CODE], 6)   = 'SU-REN' THEN 'ALF'
            WHEN RIGHT([STOCK_CODE], 3)   = 'REN' THEN 'ALF'
            WHEN RIGHT([STOCK_CODE], 2)   = 'SU' THEN 'ALF'
            WHEN [STOCK_CODE]             = 'LAB-TEL-REM-SUPPORT' THEN 'ALF'
            ELSE CASE
                  WHEN AY26029 = '4250' THEN 'EXPENSE'
                  ELSE 'SALE' END 
      END

Open in new window

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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.