Solved

Variables in SQL

Posted on 2011-02-17
13
393 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 31

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video discusses moving either the default database or any database to a new volume.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now