Storing values in a variable

Last Post 21 Aug 2009 05:44 AM by shijobaby. 9 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
Jack_Kaye
New Member
New Member

--
06 Sep 2006 06:17 AM
I'm trying to discover a way that I can store a value from a select statement in a variable that I can then perform arithmatic functions on. I'm receiving an error message, "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Here is a snapshot of what I'm trying to do:

declare
@StartDate varchar(10)
,@EndDate varchar(10)
,@JTDUSRevLCY smallmoney
,@JTDCANRevLCY smallmoney
,@JTDTotalRevUSD smallmoney
set @StartDate = '01/01/2006'
set @EndDate = '06/30/2006'

select
,@JTDUSRevLCY =
isnull((
select t.Amount*-1 -- Reverse the sign for Revenue
from
(
select [Job No_], sum(Amount) as Amount
from [Blanchard US$G_L Entry]
where
[Source Code] = 'POSTRECOG'
and left([G_L Account No_],1) = 5 and left([Document No_],5) <> 'CLOSE' and [Posting Date] <= @EndDate
group by [Job No_]
) t
where t.[Job No_] = tot.[Job No_]
),0)
,@JTDCANRevLCY =
isnull((
select t.Amount*-1
from
(
select [Job No_], sum(Amount) as Amount
from [Blanchard CANADA$G_L Entry]
where
[Source Code] = 'POSTRECOG'
and left([G_L Account No_],1) = 5
and left([Document No_],5) <> 'CLOSE'
and [Posting Date] <= @EndDate
group by [Job No_]
) t
where t.[Job No_] = tot.[Job No_]
),0)
,@JTDTotalRevUSD =
@JTDUSRevLCY + (@JTDCANREVLCY/dbo.NavCurrConv('CAN',convert(char(10),max([Posting Date]),110)))

from
(
/*
***********************************************************
US Revenue/Costs - Current Period
***********************************************************
*/
select
usgl.[Job No_]
,usgl.Amount
,usgl.[G_L Account No_]
,usgl.[Document No_]
,usgl.[Posting Date]
,OrgCo = 'US'
from [Blanchard US$G_L Entry] usgl
where
usgl.[Source Code] = 'POSTRECOG'
and left(usgl.[G_L Account No_],1) in(5,6)
and usgl.[Document No_] not like 'CLOSE%'
and usgl.[Posting Date] between @StartDate and @EndDate
/*
***********************************************************
Canada Revenue/Costs - Current Period
***********************************************************
*/
union all
select
usgl.[Job No_]
,usgl.Amount
,usgl.[G_L Account No_]
,usgl.[Document No_]
,usgl.[Posting Date]
,OrgCo = 'CAN'
from [Blanchard CANADA$G_L Entry] usgl
where
usgl.[Source Code] = 'POSTRECOG'
and left(usgl.[G_L Account No_],1) in(5,6)
and usgl.[Document No_] not like 'CLOSE%'
and usgl.[Posting Date] between @StartDate and @EndDate
) tot
group by tot.[Job No_]
having
sum(tot.[Amount]) <> 0
order by tot.[Job No_]
nosepicker
New Member
New Member

--
06 Sep 2006 08:48 AM
You can store values into variables, but you can't have any other columns in your SELECT statement. For example, you can do this:

DECLARE @col1 int, @col2 int, @col3 int

SELECT @col1 = col1, @col2 = col2, @col3 = col3
FROM YourTable
WHERE ...


But you can't do this:

DECLARE @col1 int, @col2 int, @col3 int

SELECT @col1 = col1, @col2 = col2, @col3 = col3, col4, col5
FROM YourTable
WHERE ...
Jack_Kaye
New Member
New Member

--
06 Sep 2006 08:56 AM
So if I want to store variables and pull data, do I have any other option? My only thought was to store the variable information in another table.
nosepicker
New Member
New Member

--
06 Sep 2006 11:00 AM
If you want to pull data, just run the query again, without using the variables.
Jack_Kaye
New Member
New Member

--
06 Sep 2006 12:33 PM
I need both the data stored in the variables as well as data being extracted. I'm thinking that storying the data from the variables into a table with a first pass and then running a second pass to pull the data along with the information in the new table might be the way I need to go.
SQLUSA
New Member
New Member

--
06 Sep 2006 01:59 PM
You can do a cursor on the SELECT.

I think that is what you need.

Kalman Toth, Database, Data Warehouse and BI Architect
http://www.sqlusa.com
The Best SQL Server 2005 Training in the World
whynot
New Member
New Member

--
07 Sep 2006 11:33 AM
This query included some unnecessary column. You may put the sum in your tot sub_query, take all useless column out. then select the sum in the top level query. The select in top level query is not a good way to do this.
This might work for you:

select a.[Job No_]
,@JTDUSRevLCY = isnull(US_Amount,0) * -1
,@JTDCANRevLCY = isnull(CA_Amount,0)*-1
,@JTDTotalRevUSD = isnull(US_Amount,0) + (isnull(CA_Amount,0)/dbo.NavCurrConv('CAN',convert(char(10),max([Posting Date]),110)))
from
/*
***********************************************************
Get all [Job No_] you have.
***********************************************************
*/
(SELECT [Job No_] from [Blanchard US$G_L Entry]
union
SELECT [Job No_] from [Blanchard CANADA$G_L Entry]) a
Left join
/***********************************************************
left join to get [Job No_]'s US Revenue/Costs total - Current Period
***********************************************************/
(select usgl.[Job No_]
,sum(isnull(usgl.Amount,0)) AS US_Amount
from [Blanchard US$G_L Entry] usgl
where
usgl.[Source Code] = 'POSTRECOG'
and left(usgl.[G_L Account No_],1) in(5,6)
and usgl.[Document No_] not like 'CLOSE%'
and usgl.[Posting Date] between @StartDate and @EndDate
group by usgl.[Job No_] ) u on u.[Job No_]=a.[Job No_]

/************************************************************
left join to get [Job No_]'s Canada Revenue/Costs total - Current Period
*************************************************************/
left join
(select usgl.[Job No_]
,sum(isnull(usgl.Amount,0)) as CA_Amount
from [Blanchard CANADA$G_L Entry] usgl
where
usgl.[Source Code] = 'POSTRECOG'
and left(usgl.[G_L Account No_],1) in(5,6)
and usgl.[Document No_] not like 'CLOSE%'
and usgl.[Posting Date] between @StartDate and @EndDate
group by usgl.[Job No_]) c on c.[Job No_]=a.[Job No_]
Jack_Kaye
New Member
New Member

--
12 Sep 2006 08:55 AM
Thank you for your reply Whynot...there is a couple pieces of information that are missing that might help:

(1) I need to make an initial query for any job records within the current period of time 01/01/06-06/30/06. These are the CYTotalRevUSD,CYTotalCostUSD calculations.

(2) If any job records are found in the initial period, I need to then query all job records from the beginning of time up to the end of the period 06/30/06 for those jobs found in the initial period. These are the JTDUSRevLCY, JTDCANRevLCY, JTDUKRevLCY, JTDTotalRevUSD (currency conversion), JTDUSCostLCY, JTDCANCostLCY, JTDUKCostLCY, JTDTotalCostUSD (currency Conversion) calculations.

(3) When getting the currency conversion, I would like to avoid having to requery the data again and just take the values for JTDTotalRevUSD = JTDUSRevLCY + JTDCANRevLCY/currency rate + JTDUKRevLCY/currency rate and add them up.

(4) When getting the PriorRevUSD, I would like to avoid having to requery the data and again and just take the values for PriorRevUSD = JTDTotalRevUSD - CYTotalRevUSD.

Can you think of a way to structure the query to accomplish this?

THX
Jack =+)
whynot
New Member
New Member

--
13 Sep 2006 10:11 AM
Answer to your last post:

(1) Add "where [Posting Date] between @StartDate and @EndDate" in the two union select statements, you will get the initial job list you want.

(2) With the first change, the query will give you JTDUSRevLCY, JTDCANRevLCY, JTDTotalRevUSD between the dates, but not JTDUKRevLCY. To get JTDUKRevLCY, copy one of the sub-query that calculate sub-total, change the base table to [Blanchard British$G_L Entry] to make a sub-query get UK_Amount (name the column). left join it as the other two did. Also, add ",@JTDCANRevLCY = isnull(UK_Amount,0)*-1 " in the top level select statement and add UK_Amount to JTDTotalRevUSD calculation. You may use the same way to aggregate Cost.

(3) The query I posted does not requery when convert CA$ to US$. It use the sub-total from the secand and third sub-query. If you mean to requery the exchange rate, the you may store the exchange rate for each currency in different variable before the query. In the query, use variables instead of "dbo.NavCurrConv('CAN',convert(char(10),max([Posting Date]),110)))"

(4) This request is not in your original post. Without data source and other information, I cannot give more help. However, If you can get CTYRev work, you will figure out how to get PrioRev. It should be same.

Let's know how far have you got.
shijobaby
New Member
New Member

--
21 Aug 2009 05:44 AM
Hi

Actually this in the group of small errors consuming time

Just have aook on my blog

{url removed my SQL Server Magazine Forum Pro}


Happy Programming


Acceptable Use Policy
---