July 29, 2009 at 6:44 am
Hi there,
I have three columns that I am pulling back in my SELECT statement.
Lets call them Col A, Col B and Col C.
I have created a column adding all the above together, something like below -
Col A + Col B + Col C AS Col D
How do I now filter on Col D via the WHERE syntax?
Thanks
July 29, 2009 at 6:49 am
select colA, colB, colC, colD=colA+colB+ colC from mytable where colD=123
July 29, 2009 at 7:37 am
Now have another issue -
When I run the below query -
SELECT TEN.[tenancy-ref] AS 'Tenancy Reference',
TEN.[corr-name1] AS Name,
TEN.[curr-balance] AS 'Current Balance',
TEN.[hb-arrears] AS 'HB Arrears',
TEN.[sp-arrears] AS 'SP Arrears',
TEN.[curr-balance] + [hb-arrears]+ [sp-arrears]AS 'True Arrears Balance',
'True Arrears Balance' = TEN.[curr-balance] + [hb-arrears]+ [sp-arrears]
FROM [IH_RE-TENANCY]AS TEN
INNER JOIN
[IH_RE-TNCY-PLACE] AS TPLA
ON TEN.[tncy-sys-ref] = TPLA.[tncy-sys-ref]
INNER JOIN
[IH_IH-LOCATION]AS LOC
ON TPLA.[place-ref] = LOC.[place-ref]
WHERE (TEN.[tncy-status] 'FOR')
AND (LOC.[location-type] 'Garage')
AND 'True Arrears Balance' > 0
I get the following error -
Syntax error converting the varchar value 'True Arrears Balance' to a column of data type int.
So I changed the where cause from "AND 'True Arrears Balance' > 0" to "AND CONVERT (INT(9),'True Arrears Balance') > 0"
Now I get another message as below -
CAST or CONVERT: invalid attributes specified for type 'int'
No idea what I am doing wrong. Thanks
July 29, 2009 at 8:14 am
Okay... I'm guessing you are new to SQL or used to a product other than Microsoft SQL, yes?
INT doesn't have a length parameter. It's not INT(9), it's just INT. You can look up datatypes and many other useful things in Books Online (BOL). Books Online is the help function from SQL Server Management Studio.
You have column names enclosed in apostrophes 'True Arrears Balance' instead of [True Arrears Balance]. Strings which are enclosed in apostrophes are constants (or literals) in SQL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 29, 2009 at 8:14 am
You're trying to add a string to an INT field in the first query, which isn't possible. Your attempted fix is trying to convert the string to a number, again, not possible. Instead try casting the INT column to a VARCHAR or NVARCHAR (whichever is appropriate for your system) and then adding it to the string.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2009 at 8:20 am
That's Correct. I am not totally new to SQL but have huge difficulty grasping the language.
Thanks for your replies so far. My Code now looks like below -
SELECT TEN.[tenancy-ref] AS 'Tenancy Reference',
TEN.[corr-name1] AS Name,
TEN.[curr-balance] AS 'Current Balance',
TEN.[hb-arrears] AS 'HB Arrears',
TEN.[sp-arrears] AS 'SP Arrears',
TEN.[curr-balance] + [hb-arrears]+ [sp-arrears]AS 'True Arrears Balance',
[True Arrears Balance] = TEN.[curr-balance] + [hb-arrears]+ [sp-arrears]
FROM [IH_RE-TENANCY]AS TEN
INNER JOIN
[IH_RE-TNCY-PLACE] AS TPLA
ON TEN.[tncy-sys-ref] = TPLA.[tncy-sys-ref]
INNER JOIN
[IH_IH-LOCATION]AS LOC
ON TPLA.[place-ref] = LOC.[place-ref]
WHERE (TEN.[tncy-status] 'FOR')
AND (LOC.[location-type] 'Garage')
AND CONVERT (INT ,[True Arrears Balance]) > 0
--AND 'True Arrears Balance' > 0
Now I get error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'True Arrears Balance'.
TEN.[curr-balance] + [hb-arrears]+ [sp-arrears] are all held as VARCHARs in the database
July 29, 2009 at 8:32 am
You can't refer to an alias as a column. Instead of this:
AND CONVERT (INT ,[True Arrears Balance]) > 0
You would need to do this:
AND ((CAST(TEN.[curr-balance] AS INT) + CAST(TEN.[hb-arrears] AS INT) + CAST(TEN.[sp-arrears] as INT)) > 0)
Or, alternatively, you could place the select statement within a derived table and then you can refer to the column aliases as real columns, but it's not going to make the code any more clear for all that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2009 at 8:58 am
Thanks managed to get the data.
July 29, 2009 at 9:01 am
Since someone else may have the same problem and find this thread, can you post your solution?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 29, 2009 at 2:38 pm
ps (7/29/2009)
select colA, colB, colC, colD=colA+colB+ colC from mytable where colD=123
I recommend that you TEST your solutions before posting, even for something apparently this simple. It helps no one to post information that is just plain wrong and it certainly doesn't help your SQL credibility, either.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 29, 2009 at 7:57 pm
drew.allen (7/29/2009)
ps (7/29/2009)
select colA, colB, colC, colD=colA+colB+ colC from mytable where colD=123I recommend that you TEST your solutions before posting, even for something apparently this simple. It helps no one to post information that is just plain wrong and it certainly doesn't help your SQL credibility, either.
Drew
Thanks for the comments. 🙂 I didnt have sql server on that system then so couldnt check.
And yes, that's incorrect. The correct syntax is
select colA, colB, colC, colD=colA+colB+ colC from mytable where colA+colB+ colC =123
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply