How to refer to calculated field ?

When I do the following sql statement, I got the error "Invalid column name
'buy'."
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [buy]=0 then 1 else 2 end --> error here
FROM tbl1
How can I refer to Buy in the same sql statement ?
Thanks.That is not a calculated field, it is a column alias. You cannot refer it in
other clauses in the same select statement unless it is nested in a derived
table. Your options are:
SELECT col,
< expression using col >
FROM ( SELECT CASE WHEN col = 'buy'
THEN 1 ELSE 2
END
FROM tbl ) t ( col ) ;
You can also reuse the same expression to avoid the derived table construct
as well.
Anith|||You can't. You'll have to use a derived table. Something like:
select DerivedTable.Buy
,Ddif = case when DerivedTable.Buy=0 then 1 else 2 end
from (
SELECT Buy = case when [col1]='BUY' then 1 else 2 end
FROM tbl1
) DerivedTable
Of course this is untested.
ML
http://milambda.blogspot.com/|||Your first 'BUY' statement is pretty simple - just 1 or 2 based on col1 =
BUY, so how about :
SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
Ddif = case when [col1]<>'BUY' then 1 else 2 end
FROM tbl1
Or something like that. In other words, build the logic for Ddif back to
the source column unaltered by the first case statement.
However, there may be a problem in your logic in terms of what happens first
and second, etc. See what you think:
If you are saying:
For COL1 if 'BUY' then 1 else 2
then after that is done there will be 0 rows where BUY = 0, the only options
are 1 or 2.
So the dDif column will always be 2 no matter what.
"fniles" wrote:
> When I do the following sql statement, I got the error "Invalid column nam
e
> 'buy'."
> SELECT Buy = case when [col1]='BUY' then 1 else 2 end,
> Ddif = case when [buy]=0 then 1 else 2 end --> error here
> FROM tbl1
> How can I refer to Buy in the same sql statement ?
> Thanks.
>
>|||Thank you everybody for your help.

> You can also reuse the same expression to avoid the derived table
> construct
How can I reuse the same expression ? In my case, I will need to reuse the
column alias in more than 1 places in the same sql statement.
For ex:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
> That is not a calculated field, it is a column alias. You cannot refer it
> in other clauses in the same select statement unless it is nested in a
> derived table. Your options are:
> SELECT col,
> < expression using col >
> FROM ( SELECT CASE WHEN col = 'buy'
> THEN 1 ELSE 2
> END
> FROM tbl ) t ( col ) ;
> You can also reuse the same expression to avoid the derived table
> construct as well.
> --
> Anith
>|||I am wondering since I will do this in a stored procedure and I need to use
the derived column more than once in the same sql statement, would it be
possible to use a temporary table for the derived column, and if it is how
can I do that ? Thanks.
The sql statement:
SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
ABC = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
"fniles" <fniles@.pfmail.com> wrote in message
news:%23SD3Bsn%23FHA.360@.TK2MSFTNGP09.phx.gbl...
> Thank you everybody for your help.
>
> How can I reuse the same expression ? In my case, I will need to reuse the
> column alias in more than 1 places in the same sql statement.
> For ex:
> SELECT right('0000'+ convert(varchar(4),COL1),4) AS BUY,
> ABC = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:OamRSFf%23FHA.328@.TK2MSFTNGP14.phx.gbl...
>|||Please post a simplified version of your table schema with a few sample data
& expected results. Include a brief explanation of what you are trying to
accomplish here.
Based on the code snippet you posted, it looks mostly like some formatting
issue that, in general, can be handled differently, perhaps even outside the
database server.
Anith|||I am actually trying to convert Access db to SQL Server, and the query I
posted was a part of a longer query that I need to convert.
CREATE TABLE tblA (
Price float NULL,
CC varchar(50) NULL,
Account varchar(50) NULL,
Quantity smallint NULL,
:
)
CREATE TABLE tblB (
CC varchar(50) NULL,
:
)
CREATE TABLE tblC (
Account varchar(50) NULL,
:
)
SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
HClose = case when BUY>0 then
(
Left(BUY,charindex('.',BUY)-1)*32
+
substring(BUY,charindex('.',BUY)+1,2)*2
+
case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
)
else Null
end
from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>'0'))
Example:
tblA
Price CC Account
6 1212.25 ES 12345
6 0 ES 12345
1 1585.5 NQ 12345
1 51 NQ 12345
tblB
Symbol
ES
NQ
tblC
Account
12345
Result:
Buy HClose Account
ES 1212.2500 77618 12345
ES 0.0000 12345
NQ 1585.5000 101540 12345
NQ 51.0000 3352 12345
I am also having a problem converting a floating number to a 4 decimal
floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
In my Stored Procedure, can I just derived the column Buy in a temp table,
and refer it in the next sql statement ?
Thanks.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>|||Just to add, the derived column HClose is also going to be used again in the
same query.
This is from the Access query:
IIf([buy]=0,[Hprice]-[HCLOSE],[HCLOSE]-[Hprice]) AS Hdif
"fniles" <fniles@.pfmail.com> wrote in message
news:etFhZ7o%23FHA.1676@.TK2MSFTNGP09.phx.gbl...
>I am actually trying to convert Access db to SQL Server, and the query I
>posted was a part of a longer query that I need to convert.
> CREATE TABLE tblA (
> Price float NULL,
> CC varchar(50) NULL,
> Account varchar(50) NULL,
> Quantity smallint NULL,
> :
> )
> CREATE TABLE tblB (
> CC varchar(50) NULL,
> :
> )
> CREATE TABLE tblC (
> Account varchar(50) NULL,
> :
> )
> SELECT LEFT(convert(varchar(8),Price),8)+'0000'
,8) AS BUY,
> HClose = case when BUY>0 then
> (
> Left(BUY,charindex('.',BUY)-1)*32
> +
> substring(BUY,charindex('.',BUY)+1,2)*2
> +
> case when substring(BUY,charindex('.',BUY)+3,1) = 5 then 1 else 0 end
> )
> else Null
> end
> from (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
> tblA.Account = tblC.Account
> WHERE (((tblA.Quantity)<>'0'))
> Example:
> tblA
> Price CC Account
> 6 1212.25 ES 12345
> 6 0 ES 12345
> 1 1585.5 NQ 12345
> 1 51 NQ 12345
> tblB
> Symbol
> ES
> NQ
>
> tblC
> Account
> 12345
>
> Result:
> Buy HClose Account
> ES 1212.2500 77618 12345
> ES 0.0000 12345
> NQ 1585.5000 101540 12345
> NQ 51.0000 3352 12345
>
> I am also having a problem converting a floating number to a 4 decimal
> floating number: ex: from 1212.25 to 1212.2500, 0 to 0.0000, etc.
> In my Stored Procedure, can I just derived the column Buy in a temp table,
> and refer it in the next sql statement ?
> Thanks.
>
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
>|||This is a part of the original Access query that I need to convert to SQL
Server:
SELECT Format([tblA].[PRICE],"0.0000") AS BUY,
IIf(Val([BUY])>0,(Left$([BUY],InStr([BUY],".")-1)*32+Mid$([BUY],InStr([BUY],".")+1,2))*2+(IIf(Mid$([BUY],InStr(
[BUY],".")+3,1)=5,1,0)),Null)
AS HClose,
IIf([buy]=0,1-[HCLOSE],[HCLOSE]-1) AS Hdif,...
FROM (tblA LEFT JOIN tblB ON tblA.CC = tblB.CC) LEFT JOIN tblC ON
tblA.Account = tblC.Account
WHERE (((tblA.Quantity)<>"0"));
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:uSBRY5n%23FHA.3104@.TK2MSFTNGP15.phx.gbl...
> Please post a simplified version of your table schema with a few sample
> data & expected results. Include a brief explanation of what you are
> trying to accomplish here.
> Based on the code snippet you posted, it looks mostly like some formatting
> issue that, in general, can be handled differently, perhaps even outside
> the database server.
> --
> Anith
>sql