December 14, 2011 at 6:10 am
Hi all,
Little problem I am facing.
How to return a 0 value if there is no result, or the result itself if there is any?
In other words, let's take an example:
I have a table: tbl_description that contains textual descriptions linked to owners and is used for FTS and another table: tbl_owners that contains data linked to the owners.
tbl_description:
description_id bigint IDENTITY(1,1)
owner_id bigint NOT NULL
description nvarchar(4000)
I would like to obtain the rank of the following FTS:
SELECT A.*, Score = ( SELECT ftt.[RANK] FROM tbl_description TD
INNER JOIN CONTAINSTABLE(dbo.tbl_description, [description], @freetext_keywords) AS ftt
ON TD.description_id = ftt.
WHERE
TD.owner_id = A.owner_id
)
FROM tbl_owners A
WHERE
{{my conditions}}
Results of the above SQL statement:
I obtain a RANK when something is found, otherwise, I obtain nothing (no records)
The problem is that I would like to force a value of 0 if there is no matching, otherwise the RANK value.
I tried COALESCE but the latter only works if something is returned.
I know that a solution would be to use: something like "SELECTA.*, Score = (CASE WHEN EXISTS(sqlStatement) THEN sqlStatement ELSE 0 END)" but this would mean relaunching the query (sqlStatement) a second time to obtain the RANK.
Any solution would be appreciated.
December 14, 2011 at 6:21 am
Have you tried APPLY?
SELECT A.*, x.Score
FROM tbl_owners A
OUTER APPLY (
SELECT Score = ftt.[RANK]
FROM tbl_description TD
INNER JOIN CONTAINSTABLE(dbo.tbl_description, [description], @freetext_keywords) AS ftt
ON TD.description_id = ftt.
WHERE TD.owner_id = A.owner_id
) x
WHERE
{{my conditions}}
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 14, 2011 at 6:21 am
Please will you post your table DDL in the form of a CREATE TABLE statement, some sample data in the form of INSERT statements, and, given that sample data, the results you expect to see.
Thanks
John
December 14, 2011 at 7:38 am
I finally found the solution...
Simply use the following:
SELECT COALESCE(( sqlstatement), 0)
For example
SELECT A.*, Score = 100 * ( SELECT COALESCE( ( SELECT ftt.RANK FROM tbl_description TD INNER JOIN CONTAINSTABLE(dbo.tbl_description, [description],@freetext_keywords) ftt ON TD.description_id = ftt.
WHERE TD.owner_id = A.owner_id ) , 0))
FROM tbl_owners A
WHERE ....
Hope this might help someone else.
Cheers
December 14, 2011 at 9:01 am
boeledi (12/14/2011)
I finally found the solution...Simply use the following:
SELECT COALESCE(( sqlstatement), 0)
For example
SELECT A.*, Score = 100 * ( SELECT COALESCE( ( SELECT ftt.RANK FROM tbl_description TD INNER JOIN CONTAINSTABLE(dbo.tbl_description, [description],@freetext_keywords) ftt ON TD.description_id = ftt.
WHERE TD.owner_id = A.owner_id ) , 0))
FROM tbl_owners A
WHERE ....
Hope this might help someone else.
Cheers
Did you try the APPLY solution? Not only will it work, it will allow the result (Score) to be utilised in the WHERE clause. Not all correlated subqueries optimise well too, so it's worth a try for performance.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply