Tutorial :Using a function in a query that returns a string or a null



Question:

I want to join 2 tables 'addresses' and 'user_info' on user_id and app_id (which is a number, or it is null), like these 2 examples:

select * from user_info   left outer join addresses on addresses.user_id = user_info.user_id   and addresses.app_id is null    select * from user_info   left outer join addresses on addresses.user_id = user_info.user_id   and addresses.app_id = 1234  

What the app_id should be is complicated and I have written a function to return it. It returns a string, which would be for example "is null" or "= 1234". I'm trying to call it with this syntax:

select * from user_info   left outer join addresses on addresses.user_id = user_info.user_id   and addresses.app_id dbo.fnGetAppId(addresses.user_id)  

I get this error:

Msg 4145, Level 15, State 1, Line 3 An expression of non-boolean type specified in a context where a condition is expected, near 'dbo'.

I'd like to keep the query very simple as it is without having to determine if the function is returning a null or not.

Can you suggest the best way to keep the calling query very simple?

(I'm in sql server 2005.)


Solution:1

NULL != NULL. If either address.app_id = NULL or fnGetAppID = NULL, the comparison will fail. I would write the comparison as:

coalesce(address.app_id, 'NULLSTRING') = coalesce(dbo.fnGetAppID(addresses.user_id), 'NULLSTRING')


Solution:2

It looks like you're just missing an = sign

addresses.app_id dbo.fnGetAppId(addresses.user_id)

rather than

addresses.app_id = dbo.fnGetAppId(addresses.user_id)


Solution:3

So if fnGetAppId is null then this query looks like the following?

select * from user_info left outer join addresses on addresses.user_id = user_info.user_id and null  

I doubt that is what you want. :)

You may want to do a simple check in your logic, before calling the query, to properly handle a null for fnGetAppId and as Clyde mentioned you also need an = sign for a non-null

.


Solution:4

As James Black pointed out, you have AND where you probably want WHERE. Beyond this, I suggest you make the function a boolean one (passing address.app_id to it as one more argument), so it can perform an IS NULL or = 1234 as appropriate (Bill's COALESCE solution is clever indeed, but putting the appropriate comparison inside the function is more straightforward, IMO).


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »