Solved

Variables in SQL

Posted on 2011-02-17
13
395 Views
Last Modified: 2012-05-11
Fairly new to SQL but I have not worked out how to use variables in my queries.
For example I want to reuse this calculated value again and again throughout my query.
How and where do I declare this as a variable to use calcval instead.

calcval= CAST(((dbo.[UK$Sales Line].[Amount]-dbo.[UK$Sales Line].[Unit Cost (LCY)])/dbo.[UK$Sales Line].[Amount])*100 as decimal(10,2) )

Thanks.
0
Comment
Question by:kcoxon
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 34915186
declare @calval decimal(10,2)

select @calcval =  CAST(((dbo.[UK$Sales Line].[Amount]-dbo.[UK$Sales Line].[Unit Cost (LCY)])/dbo.[UK$Sales Line].[Amount])*100 as decimal(10,2) )

reference the variable as @calcval.

Lee
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34915411
make it a subquery... part of the from clause...


select a.calcval,....
  from (select CAST(((x.[Amount]-x.[Unit Cost (LCY)])/x.[Amount])*100 as decimal(10,2) ),x.*
            from dbo.[UK$Sales Line] as X
         ) as A
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34915519
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

Author Comment

by:kcoxon
ID: 34915899
I have tried the declare @x but have some difficulty with errors.
I am trying to recreate as a variable the code below /**Calculate Line Val (LCY)**/
I get 'The multi-part identifier for dbo.[UK$Sales Header].[Currency Factor could not be bound' for each of the 3 fields specified.
Must be missing something obvious.

Thanks.

The whole query is this

declare @linvalLCY decimal(10,2)
SELECT @linvalLCY =  CASE
      WHEN dbo.[UK$Sales Header].[Currency Factor]= 0 THEN dbo.[UK$Sales Line].[Line Amount])
      ELSE dbo.[UK$Sales Line].[Line Amount]/SH.[Currency Factor]
      END


SELECT    
/****WHEN****/

YEAR (SH.[Order Date]) AS Year,
/****** Reformat the date to allow analysis by year and period ******/
CASE
WHEN MONTH (SH.[Order Date])<10 THEN
'0'+ CAST(MONTH (SH.[Order Date]) as varchar)
ELSE
CAST(MONTH (SH.[Order Date]) as varchar)
END
AS [Period],
CONVERT(VARCHAR(10), SH.[Order Date], 103) AS [Order Date],
/****WHO****/
dbo.UK$Salesperson_Purchaser.Name AS Salesperson,
dbo.[$ UK Country Dim Vals].[Name] AS Country,
SH.[Sell-to Customer No_]AS [Cust Code],
SH.[Sell-to Customer Name]AS [Customer],
dbo.[UK$Customer].[Business Line]AS [Business Line],
dbo.[$ UK Items].[Product Group],
dbo.[$ UK Product Dim Vals].Name AS [Product Series],
dbo.[UK$Sales Line].[No_]AS [Item],
dbo.[$ UK Items].[Description],
CAST([UK$Sales Line].[Quantity] as decimal(10,0)) AS [Qty],
dbo.[$ UK Items].[Base Unit of Measure]AS [UOM],
/****NUMBERS****/

[UK$Sales Line].[Outstanding Amount (LCY)],


--/**Calculate Margin**/
--CASE WHEN [UK$Sales Line].[Outstanding Amount (LCY)]=0 THEN 0
--ELSE CAST((([UK$Sales Line].[Outstanding Amount (LCY)]-dbo.[UK$Sales Line].[Unit Cost (LCY)]* [UK$Sales Line].[Quantity])/[UK$Sales Line].[Outstanding Amount (LCY)])*100 as decimal(10,2))
--END
--AS [Mat Marg %],


CAST(dbo.[UK$Sales Line].[Line Amount] as decimal(10,2)) AS [Line Val],
/**Calculate Line Val (LCY)**/
CASE
      WHEN SH.[Currency Factor]= 0 THEN CAST(dbo.[UK$Sales Line].[Line Amount]as decimal(10,2))
      ELSE CAST(dbo.[UK$Sales Line].[Line Amount]/SH.[Currency Factor] as decimal(10,2))
      END
      AS [Line Val (LCY)],


CAST(dbo.[UK$Sales Line].[Unit Cost (LCY)]* [UK$Sales Line].[Quantity] as decimal(10,5)) AS [Line Cost (LCY)],
CAST(dbo.[UK$Sales Line].[Unit Cost (LCY)] as decimal(10,5)) AS [Unit Cost (LCY)],

/*** Fill the blank currency code for LCY***/
CASE
      WHEN SH.[Currency Code]= '' THEN 'GBP'
      ELSE SH.[Currency Code]
      END
      AS [Curr],
CASE
      WHEN SH.[Currency Factor]= 0 THEN 1
      ELSE CAST(SH.[Currency Factor]as decimal(10,5))
      END
      AS [Curr Factor],



SH.[No_]AS [Order Number],
dbo.[UK$Sales Line].[Line No_]/10000 AS [Line No]


FROM        
dbo.[UK$Sales Header] AS SH
INNER JOIN
dbo.[UK$Sales Line] ON SH.[No_] = dbo.[UK$Sales Line].[Document No_]
INNER JOIN
dbo.[$ UK Country Dim Vals] ON dbo.[UK$Sales Line].[Shortcut Dimension 1 Code] = dbo.[$ UK Country Dim Vals].Code
INNER JOIN
dbo.[$ UK Product Dim Vals] ON dbo.[UK$Sales Line].[Shortcut Dimension 2 Code] = dbo.[$ UK Product Dim Vals].Code
INNER JOIN
dbo.[UK$Salesperson_Purchaser] ON SH.[Salesperson Code]= dbo.UK$Salesperson_Purchaser.Code
INNER JOIN
dbo.[UK$Customer] ON SH.[Sell-to Customer No_]= dbo.[UK$Customer].No_
INNER JOIN
dbo.[$ UK Items] ON dbo.[UK$Sales Line].[No_] = dbo.[$ UK Items].[No_]

WHERE    
SH.[No_ Series]<>'SQ'
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 34916486
You need to include your FROM <tables> and JOIN clauses in your variable assignment.

SELECT @linvalLCY =  CASE
      WHEN dbo.[UK$Sales Header].[Currency Factor]= 0 THEN dbo.[UK$Sales Line].[Line Amount])
      ELSE dbo.[UK$Sales Line].[Line Amount]/SH.[Currency Factor]
      END
FROM        
dbo.[UK$Sales Header] AS SH
INNER JOIN
dbo.[UK$Sales Line] ON SH.[No_] = dbo.[UK$Sales Line].[Document No_]
...
0
 

Author Comment

by:kcoxon
ID: 34918585
Still having problems with this so reduced to the very basics as below with 2 columns, one with Line Amount via variable gives all values as 0.00 and one direct that gives the correct value. No errors displayed.

/**DECLARE VARIABLES**/

DECLARE @linvalLCY decimal(10,2)
SELECT @linvalLCY =  dbo.[UK$Sales Line].[Line Amount]
FROM        
dbo.[UK$Sales Header]
INNER JOIN
dbo.[UK$Sales Line] ON dbo.[UK$Sales Header].[No_] = dbo.[UK$Sales Line].[Document No_]
/********************/

SELECT    
@linvalLCY AS Variable,
CAST(dbo.[UK$Sales Line].[Line Amount] as decimal(10,2)) AS [Line Amount]


FROM        
dbo.[UK$Sales Header]
INNER JOIN
dbo.[UK$Sales Line] ON dbo.[UK$Sales Header].[No_] = dbo.[UK$Sales Line].[Document No_]
INNER JOIN
dbo.[$ UK Country Dim Vals] ON dbo.[UK$Sales Line].[Shortcut Dimension 1 Code] = dbo.[$ UK Country Dim Vals].Code
INNER JOIN
dbo.[$ UK Product Dim Vals] ON dbo.[UK$Sales Line].[Shortcut Dimension 2 Code] = dbo.[$ UK Product Dim Vals].Code
INNER JOIN
dbo.[UK$Salesperson_Purchaser] ON dbo.[UK$Sales Header].[Salesperson Code]= dbo.UK$Salesperson_Purchaser.Code
INNER JOIN
dbo.[UK$Customer] ON dbo.[UK$Sales Header].[Sell-to Customer No_]= dbo.[UK$Customer].No_
INNER JOIN
dbo.[$ UK Items] ON dbo.[UK$Sales Line].[No_] = dbo.[$ UK Items].[No_]

WHERE    
dbo.[UK$Sales Header].[No_ Series]<>'SQ'
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 250 total points
ID: 34918657
A variable can only hold a single value, so if there are multiple rows then a variable is not going to work.
0
 

Author Comment

by:kcoxon
ID: 34919254
So there is no way of using this or any other means to calculate the column values with a variable.

Thank you for your help anyway.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34920335
i don't feel you have actually explained how and where you are trying to reuse the "variable"

my first two responses illustrate how you "reuse" calculations in sql..  you have not made any direct response to them...

can you please state what you are trying to achieve with some input/output data examples...





0
 

Author Comment

by:kcoxon
ID: 34923974
If I have a calculated value in a column, I want to reuse the formula for this again and again by expressing this formula as a variable.
e.g. where Margin = ((Revenue - Cost)/Revenue) instead of using  ((Revenue - Cost)/Revenue) each time use a variable 'margin'.
This is not a fixed variable, it changes based on the values in the record being selected.

I hope this clarifies my need.

Thanks.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 34924796
not really...

so why does this not allow you do now do any further calculations you wish to with margin?

select a.margin   ,....
  from (select CAST(((x.[Amount]-x.[Unit Cost (LCY)])/x.[Amount])*100 as decimal(10,2) ) as margin,x.*
            from dbo.[UK$Sales Line] as X
         ) as A

if you are saying that you wish to do independant calculations with the margin data....

then you have to store the margin in a temp table, and retrieve and manipulate it from there....

can you explain with a concrete example what you processing scenario is so that we can assist.

0
 
LVL 32

Expert Comment

by:awking00
ID: 34926253
Why not create a user-defined function passing in the amount and cost as parameters and returning the percentage result (be sure to account for a 0 amount)?
0
 

Author Closing Comment

by:kcoxon
ID: 35187350
Proposed solutions were far more complex than dealing with the original problem.

Thank you all
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
string fuctions 4 26
MS SQL / SQL Server Native Client -- how to prevent seeing other servers? 2 25
Problem when I run a simple storeproc - help 4 17
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question