Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Declaring a variable in SQL and making it a field

Posted on 2013-12-12
15
Medium Priority
?
348 Views
Last Modified: 2013-12-12
I have a variable that I want to declare in SQL.  The whole idea is that it will compare two fields that are already being pulled from the database.  So the following is pseudo code to solve my sql dilemma:

newfield as float

if field1 > 0
newfield = field1
else
newfield = field2

How would I do the above in SQL and make newfield a field to be listed in a Query or stored procedure?  Thanks!
0
Comment
Question by:VBBRett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +2
15 Comments
 
LVL 20

Expert Comment

by:TheAvenger
ID: 39714941
That should give you a start:

declare @newfield float

select @newfield = case when field1 > 0 then field 1 else field2 end
from some_table
where ...

If you post more of your code I can help you integrate this in it.
0
 
LVL 12

Assisted Solution

by:Tony303
Tony303 earned 400 total points
ID: 39714944
Here is a workup example.
DECLARE @Variable float

SET @Variable = 0

--TEST Variable works
SELECT @variable

--Put var in and test
IF (SELECT 12 AS field1) > 0
SET @variable = (SELECT 12 AS field1)
ELSE
SET @Variable = (SELECT -12 AS field2)


SELECT @Variable

Open in new window

0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39714945
Lets say you have this table:

CREATE TABLE example (
   field1 int not null,
   field2 int not null
)

You could make a view against this table that would be:

SELECT field1, field2,
  case field1 > 0 then field1 else field2 end as newfield
FROM example


If the two fields are not the same data type, you convert to whichever type of the two can hold both values from the other, or to varchar if that isn't possible.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39714947
no need for a variable, you can use a case statement

   select case when field1 > 0 then field1 else field2 end as MyField
0
 
LVL 12

Expert Comment

by:Tony303
ID: 39714950
Here is how your example should work..

Cheers

DECLARE @Variable float


IF field1  > 0
SET @variable =field1
ELSE
SET @Variable = field2

SELECT @Variable

Open in new window

0
 

Author Comment

by:VBBRett
ID: 39715052
How am I to add this field into a stored procedure that already is built?  I keep getting incorrect syntax here and there.
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715067
You are going to have to provide some specific example code for help with adding this into a stored procedure and if this stored procedure is being used by other things adding columns to it or changing what data shows up in some of the columns can and probably will break those things.
0
 

Author Comment

by:VBBRett
ID: 39715080
Should I post the query?
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715085
One way you could do this without changing the stored procedure is this.

Create Table #proc_result_set (
--define all the columns names and data types of stored procedure result set
)

INSERT #proc_result_set
EXEC stored_procedure @parm1 = value, --etc.

SELECT
--columns list from the result set,
case field1 > 0 then field1 else field2 end as newfield
FROM #proc_result_set

This script could be converted to a wrapper stored proc if you must use stored procedures by adding a CREATE PROCEDURE new_proc_name (same parms as real proc) AS
at the start.
0
 

Author Comment

by:VBBRett
ID: 39715091
Well, here is the query

ALTER PROCEDURE [dbo].[Rpt_Comp]  
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal USD',
 ((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Current Components (USD)',
 ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
 AS 'Current Components and Base (USD)',
 ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Planned Components (USD)',
 ((eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
 AS 'Proposed New Base (Merit) Salary USD',
 ((isnull(RecAmt,'')*ExchangeRate)/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Merit Increase Pct',
 (promoincreaseamt*ExchangeRate)
 AS 'Promo Increase Amt USD',
 (((isnull(RecAmt,'')*ExchangeRate)+(isnull(promoincreaseamt,'')*ExchangeRate))/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Total Increase Percent (Merit and Promo)',
 RptLoc.RptLocName
 AS 'Assigned Office',
 Eml.Div
 AS Region
FROM
 Emp
  LEFT JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL
  LEFT JOIN Job ON (Job.Job = Eml.Job AND Job.JobEfdDt IS NULL AND Job.JobEfdDt IS NULL) AND Job.JobEfdDt IS NULL
  LEFT JOIN Dpt ON (Dpt.Dpt = Eml.Dpt AND Dpt.Loc = Eml.Loc AND Dpt.DptEfdDt IS NULL AND Dpt.DptEfdDt IS NULL) AND Dpt.DptEfdDt IS NULL
  LEFT JOIN Org ON (Org.Org = Eml.Org AND Org.Dpt = Eml.Dpt AND Org.OrgEfdDt IS NULL AND Org.OrgEfdDt IS NULL) AND Org.OrgEfdDt IS NULL
  RIGHT JOIN Cty ON Cty.Cty = Emp.PrimCty AND Cty.CtyEfdDt IS NULL
  LEFT JOIN EmpCpsPlnDta AS EmpCpsPlnDta ON (EmpCpsPlnDta.Emp = Emp.Emp) AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL
  RIGHT JOIN RptLoc ON (RptLoc.RptLoc = Eml.RptLoc AND RptLoc.Cmp = Eml.Cmp AND RptLoc.RptLocEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL) AND RptLoc.RptLocEfdDt IS NULL
  RIGHT JOIN Cur ON Cur.Cur = Eml.Cur AND Cur.CurEfdDt IS NULL
  LEFT JOIN EmpSalCompTmp ON EmpSalCompTmp.Emp = Emp.Emp AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL
WHERE
 ((eml.empsts IN (N'Active',N'On Leave'))
AND
 EmpCpsPlnDta.CpsPlnYr = N'2014'
AND
 (EmpCpsPlnDta.Fld1 IN (N'IT ANZAME',N'IT Bus Servces',N'IT HelpDsk',N'IT Mgmt',N'IT SrvrHelpDsk',N'IT SteSuportUS',N'IT SysApps',N'IT UK')))
AND
 (Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND Dpt.DptEfdDt IS NULL AND Org.OrgEfdDt IS NULL AND Cty.CtyEfdDt IS NULL AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL AND Cur.CurEfdDt IS NULL AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname)

Open in new window

0
 

Author Comment

by:VBBRett
ID: 39715096
The values that I want to compare are 'Current Components and Base (USD) and Planned Components USD.  If Planned Components USD is not 0 then I want the new field value to be the value of Planned Components USD...if the Planned Components USD is empty, then I wan the new field value to be the value of Current Components USD.
0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715100
Nevermind saw it.
0
 
LVL 8

Accepted Solution

by:
virtuadept earned 1600 total points
ID: 39715120
Added "NewField" you probably want to pick a better name. Also keep in mind what I said about adding columns to procs breaking other things that use the proc.

ALTER PROCEDURE [dbo].[Rpt_Comp]  
SELECT DISTINCT 
 EmpCpsPlnDta.CpsGrp
 AS 'Comp Group',
 EmpCpsPlnDta.Fld1
 AS Unit,
 EmpCpsPlnDta.Fld1Name
 AS 'Unit Name',
 (Emp.Lastname + ', ' + Emp.Firstname)
 AS Name,
 Job.JobTitle,
 EmpCpsPlnDta.RecAmt
 AS 'Merit Increase Amount',
 (AnnSal*ExchangeRate)
 AS 'Current Annual Sal USD',
 ((select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Current Components (USD)',
 ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
 AS 'Current Components and Base (USD)',
 ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
 AS 'Planned Components (USD)',
 ((eml.AnnlSal+EmpCpsPlnDta.RecAmt)*ExchangeRate)
 AS 'Proposed New Base (Merit) Salary USD',
 ((isnull(RecAmt,'')*ExchangeRate)/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Merit Increase Pct',
 (promoincreaseamt*ExchangeRate)
 AS 'Promo Increase Amt USD',
 (((isnull(RecAmt,'')*ExchangeRate)+(isnull(promoincreaseamt,'')*ExchangeRate))/((AnnSal*ExchangeRate)+(select isnull(sum(round(sc.amt*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))*100)
 AS 'Total Increase Percent (Merit and Promo)',
 RptLoc.RptLocName
 AS 'Assigned Office',
 Eml.Div
 AS Region,
 (
	case when ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null)) > 0 
	then ((select isnull(sum(round(sc.NewmnthlyamtCZ*ExchangeRate,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null))
	else ((select isnull(sum(round(sc.Mnthlyamt*ExchangeRate*12,2)),'') from EmpSalCompTmp sc, Cur c where sc.Emp = Emp.Emp and sc.EmpSalCompTmpEfdDt is null and sc.amt > 0 and sc.AmtCur = c.Cur and c.CurEfdDt is null) + AnnlSal*ExchangeRate)
	end
) as NewField 
FROM
 Emp
  LEFT JOIN Eml ON (Emp.Emp = Eml.Emp AND Eml.EmlEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL) AND Eml.EmlEfdDt IS NULL AND Emp.EmpEfdDt IS NULL
  LEFT JOIN Job ON (Job.Job = Eml.Job AND Job.JobEfdDt IS NULL AND Job.JobEfdDt IS NULL) AND Job.JobEfdDt IS NULL
  LEFT JOIN Dpt ON (Dpt.Dpt = Eml.Dpt AND Dpt.Loc = Eml.Loc AND Dpt.DptEfdDt IS NULL AND Dpt.DptEfdDt IS NULL) AND Dpt.DptEfdDt IS NULL
  LEFT JOIN Org ON (Org.Org = Eml.Org AND Org.Dpt = Eml.Dpt AND Org.OrgEfdDt IS NULL AND Org.OrgEfdDt IS NULL) AND Org.OrgEfdDt IS NULL
  RIGHT JOIN Cty ON Cty.Cty = Emp.PrimCty AND Cty.CtyEfdDt IS NULL
  LEFT JOIN EmpCpsPlnDta AS EmpCpsPlnDta ON (EmpCpsPlnDta.Emp = Emp.Emp) AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL
  RIGHT JOIN RptLoc ON (RptLoc.RptLoc = Eml.RptLoc AND RptLoc.Cmp = Eml.Cmp AND RptLoc.RptLocEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL) AND RptLoc.RptLocEfdDt IS NULL
  RIGHT JOIN Cur ON Cur.Cur = Eml.Cur AND Cur.CurEfdDt IS NULL
  LEFT JOIN EmpSalCompTmp ON EmpSalCompTmp.Emp = Emp.Emp AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL
WHERE
 ((eml.empsts IN (N'Active',N'On Leave'))
AND
 EmpCpsPlnDta.CpsPlnYr = N'2014'
AND
 (EmpCpsPlnDta.Fld1 IN (N'IT ANZAME',N'IT Bus Servces',N'IT HelpDsk',N'IT Mgmt',N'IT SrvrHelpDsk',N'IT SteSuportUS',N'IT SysApps',N'IT UK')))
AND
 (Emp.EmpEfdDt IS NULL AND Eml.EmlEfdDt IS NULL AND Job.JobEfdDt IS NULL AND Dpt.DptEfdDt IS NULL AND Org.OrgEfdDt IS NULL AND Cty.CtyEfdDt IS NULL AND EmpCpsPlnDta.EmpCpsPlnDtaEfdDt IS NULL AND RptLoc.RptLocEfdDt IS NULL AND Cur.CurEfdDt IS NULL AND EmpSalCompTmp.EmpSalCompTmpEfdDt IS NULL)
ORDER BY
 EmpCpsPlnDta.CpsGrp,
 EmpCpsPlnDta.Fld1,
 (Emp.Lastname + ', ' + Emp.Firstname)

Open in new window

0
 
LVL 8

Expert Comment

by:virtuadept
ID: 39715125
I just did a minor edit to the prior post, I forgot "when" after the case keyword.
0
 

Author Comment

by:VBBRett
ID: 39715385
Hi virtuadept...I caught the When error prior to your posting but I appreciate you helping the cause.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

670 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