ifnull

[rrmcguire]

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

 

[gmmastros]

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

 

[softhemc]

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

 

[rrmcguire]

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

 

[bborissov]

There is no IFNULL function in MS SQL Server

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

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.

 

[djj55]

There is also the NULLIF function
NULLIF


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

 

[rrmcguire]

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

 

[gmmastros]

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

 

[Andrzejek]

Info from here:
[tt]
ISNULL ( a1, a2 )
[/tt]
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.

 

[johnherman]

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)

 

[rrmcguire]

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

 

[philhege]

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.