INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ifnull

ifnull

(OP)
I have a field 'days to pay' and when it is null want to show the value = 30.

so I have

IFNULL(`daystopay`,30)


but if its not null to show whatever the value is in 'daystopay'


Can someone tell me how to do this?

thanks

RE: ifnull

I think you're looking for "IsNull", not IfNull.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: ifnull

Possibly COALESCE(DaysToPay, 30) - COALESCE takes the first non NULL value from the list

RE: ifnull

(OP)
I'm using ifnull as I need to know if 'days to pay' is null set it equal to 30 but then I also need to pull thr value of 'days to pay' if it's anything else also

Thanks

RE: ifnull

There is no IFNULL function in MS SQL Server

Borislav Borissov
VFP9 SP2, SQL Server

RE: ifnull

In T-SQL ISNULL or COALESCE are doing that. Also field names are not delimited by backticks, that's MySQL syntax. In T-SQL you use Schema.TableName.ColumnName, most often dbo for the schema. The column name alone may be sufficient, if it has no spaces. You may also delimit the name parts with [] or ", but not backticks.

Bye, Olaf.

RE: ifnull

There is also the NULLIF function
NULLIF

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

RE: ifnull

(OP)
ok, IFNULL is actually what I'm using,

currently have IFNULL('daystopay',30)

so if days to pay is null the value needs to be 30, but what do I do I the 'daystopay' is not null and I want to show the value of 'daystopay'

thanks everyone

RE: ifnull

Like others have said, IFNULL is not a built-in SQL Server function. ISNULL would work, and Coalesce would also work.

Since you are insistent on using IFNULL, that would indicate to me that you are not using SQL Server or you have a user defined function named IFNULL.

Either way.... I think your problem may be with the single quotes. In SQL Server, you use single quotes to delimit a hard coded string.

IFNULL('daystopay',30)

In this case, you are checking if the string "daystopay" is null. It's a string with a value, so it is not NULL. Try....

IFNULL(daystopay, 30)

Without the single quotes, SQL Server will look for a column named daystopay.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

RE: ifnull

Info from here:

ISNULL ( a1, a2 )

where a1 is the expression to be checked for NULL and a2 is the expression to be returned if a1 is NULL. If a1 is not null then a1 is returned.

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: ifnull

If you are using IFNULL, there is probably a global function or synonym in place.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: ifnull

(OP)
yeah, basically IFNULL is performing the same as ISNULL I beleive

RE: ifnull

This question should be posted to the MySQL forum. There may be behaviors of IFNULL() that T-SQL folks are not familiar with.

https://www.1keydata.com/sql/sql-ifnull.html

-----------
With business clients like mine, you'd be better off herding cats.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close