• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

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")))))))))))))))))
0
Chris Michalczuk
Asked:
Chris Michalczuk
  • 7
  • 5
  • 2
1 Solution
 
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
 
PortletPaulCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
@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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
      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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now