We help IT Professionals succeed at work.

contrary of ISNULL

jsctechy
jsctechy asked
on
1,272 Views
Last Modified: 2008-02-01
hi there,
if i have this function on a query what would be the reverse for not null values?

ISNULL(FIELD, "put this" ) as FIELD_NAME

so i want somethinglike this:
ISNOTNULL(FIELD, "put this" ) as FIELD_NAME
but ISNOTNULL is not a valid function

THanks for your help.
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
jsctechyInfrastructure Team Lead

Author

Commented:
why i can not do this?

select SITUS_STREET_NUMBER + ' ' + SITUS_STREET_DIRECTION + ' ' + SITUS_STREET_NAME + ' ' + SITUS_STREET_TYPE as SITUS_ADDRESS,
CASE WHEN SITUS_ADDRESS IS NOT NULL THEN 'PUT THIS' ELSE NULL END as field_name
from gis.TAX_Roll
CERTIFIED EXPERT
Top Expert 2012

Commented:
Because you are confusing some other SQL dialect (MS Access) with T-SQL.  In T-SQL you cannot do that.  Period.
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
I am confuse about your remark acperkins?

>why i can not do this?
what error message do you get?
jsctechyInfrastructure Team Lead

Author

Commented:
if i run this query:

select  SITUS_STREET_NUMBER + ' ' + ISNULL(SITUS_STREET_DIRECTION, '') + ' ' + ISNULL(SITUS_STREET_NAME, '') + ' ' + ISNULL(SITUS_STREET_TYPE, '') as SITUS_ADDRESS,
CASE WHEN SITUS_ADDRESS IS NOT NULL
      THEN 'HOLLYWOD, FL'
      ELSE NULL
     END as SITUS_CITY

from gis.TAX_Roll

i got this errror: "Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'SITUS_ADDRESS'."


jsctechyInfrastructure Team Lead

Author

Commented:
perhaps i can not do that as acperkins have mention so i modify myquery like this and it seems to work:

select  SITUS_STREET_NUMBER + ' ' + ISNULL(SITUS_STREET_DIRECTION, '') + ' ' + ISNULL(SITUS_STREET_NAME, '') + ' ' + ISNULL(SITUS_STREET_TYPE, '') as SITUS_ADDRESS,
CASE WHEN SITUS_STREET_NUMBER IS NOT NULL
      THEN 'PUT THIS' + ' ' + SITUS_STREET_NUMBER
      ELSE NULL
     END as SITUS_CITY

from gis.TAX_Roll

--SITUS_STREET_NUMBER
 
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:

select  SITUS_STREET_NUMBER + ' ' + ISNULL(SITUS_STREET_DIRECTION, '') + ' ' + ISNULL(SITUS_STREET_NAME, '') + ' ' + ISNULL(SITUS_STREET_TYPE, '') as SITUS_ADDRESS,
CASE WHEN SITUS_STREET_TYPE = ''
     THEN 'HOLLYWOD, FL'
     ELSE NULL
     END as SITUS_CITY

from gis.TAX_Roll
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
explanation: you cannot use an aliased column name directly in the same query...
CERTIFIED EXPERT
Top Expert 2012

Commented:
angelIII,

>>I am confuse about your remark acperkins?<<
It was directed to the questioner.

>>you cannot use an aliased column name directly in the same query...<<
Yep.  That is what I said.

Anthony
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.