[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Variables in SQL

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
Paul G
Asked:
Paul G
  • 5
  • 4
  • 2
  • +2
1 Solution
 
Lee SavidgeCommented:
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
 
LowfatspreadCommented:
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Paul GAuthor Commented:
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
 
derekkrommCommented:
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
 
Paul GAuthor Commented:
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
 
derekkrommCommented:
A variable can only hold a single value, so if there are multiple rows then a variable is not going to work.
0
 
Paul GAuthor Commented:
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
 
LowfatspreadCommented:
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
 
Paul GAuthor Commented:
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
 
LowfatspreadCommented:
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
 
awking00Commented:
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
 
Paul GAuthor Commented:
Proposed solutions were far more complex than dealing with the original problem.

Thank you all
0

Featured Post

Industry Leaders: 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!

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