Filter condition add - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Filter condition add

Author

Topic

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-09-12 : 12:36:23

Hi - I have a select sql query and I am stuck adding the filter condition.

SELECT DISTINCT
LTRIM(RTRIM(ISNULL(shipaddr2,'')))+LTRIM(RTRIM(ISNULL(shipcity,'')))+LTRIM(RTRIM(ISNULL(shipstate,'')))+LTRIM(RTRIM(ISNULL(shipzip,''))) as AddrConcat
from [dbo].[shphst ]

And I am stuggling to add the where condition below

where AddrConcat ='something'

since the Addrconcat is not a table column I am having trouble. I am sure there is another way to add the where condition in sql query and I am trying to move the concatnated values from the table to cursor with NOT in filter. Please help me.

memorykills
Starting Member

18 Posts

Posted - 2011-09-12 : 12:37:40

SELECT *
FROM
(SELECT DISTINCT
LTRIM(RTRIM(ISNULL(shipaddr2,'')))+LTRIM(RTRIM(ISNULL(shipcity,'')))+LTRIM(RTRIM(ISNULL(shipstate,'')))+LTRIM(RTRIM(ISNULL(shipzip,''))) as AddrConcat
from [dbo].[shphst ]
) a
WHERE
AddrConcat ='something'

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-09-12 : 12:39:25

Thanks for the reply, I added like you mentioned already. that gave error
Invalid colmn name Addrconcat. please help me it is very urgent.

Let me know if it is possible to store the ltrim(table.filedname) in a variable and access the variable in select ...where. Thanks

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-09-12 : 13:12:25

I tried

select 'author'=LTRIM(bbshphst.shipaddr1) from dbo.bbshphst where autor='50'

that one also gave me the same error

Invalid column name 'author'.

I understand that author is just column heading. is there a way to change the colum name.

Please help me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 13:19:14

do it like


select * from
(
select 'author'=LTRIM(bbshphst.shipaddr1) from dbo.bbshphst 
)t
where author='50'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhan
Yak Posting Veteran

59 Posts

Posted - 2011-09-12 : 13:40:48

Thank you, visakh16. That worked great.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 13:42:52

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources